In this article we are going to see some performance tips in Sql Server.Thus makes the performance improvement in executing the query.
Tip 1:
Always try to use Select "Column names" instead of "Select * from Table_name", because this will increase network traffic , high disk i/o and memory usage in Sql server.
Tip 2:
Always use Transaction in necessary purpose, because to many transactions some times make deadlock or block other user from access.
Tip 3:
Always try to avoid using of Cursor in DB, if need minimally use While loop.
Tip 4:
Always use same data type variables to save the value in the columns to avoid the implicit and explicit conversion this leads to table scan and low performance.
Tip 5:
Function on the columns in the where clause or Join can't use indexes appropriately lead to slow performance.
Tip 6:
Minimize the usage of Distinct, Order by, Union
Tip 7:
Table variable doesn't have statistics , so Table variable reduce the performance in Joins and Searches.
Tip 8:
Avoid creating stored procedures that have wide range of data supplied to them as parameters because these are compiled to use just as one query plan.
Tip 9:
Temporary table have statistics, which get updated when data is inserted so recompile is possible ,to avoid this use Table variable.
Tip 10:
Always use tables in the view instead of referring another view because this will gives performance impact.
Tip 11:
Always use one clustered index for a table, which is the column in regular access.
Tip 12:
Always try to use columns in where clause as well as in order by that have indexes.
Tip 13:
Don't use the columns in indexes that is frequently updated the value.
Tip 14:
Creating a Too many of non-clustered indexes will slow down the performance.
Tip 15:
Always use the foreign column as indexed because this will avoid the table scan , while performing the deletion operation in referenced table due to primary key deletion.
Tip 16:
A Clustered index on column which have Guid values leads to Fragmentation of index, due to random number generation. so always use NEWSEQUENTIALID() to generated the Guid for indexed column for Guid.
Tip 17:
Always use SET NOCOUNT ON at the start of the stored procedure and SET NOCOUNT OFF at the end of the stored procedure this makes disables the counting of number of records affected.
Tip 18:
Always use the Normalization in your table structure, this will improve the performance ,avoid the repetition of data.
I Hope this article will help you in your work in Sql Server, to improve the performance.
Tip 1:
Always try to use Select "Column names" instead of "Select * from Table_name", because this will increase network traffic , high disk i/o and memory usage in Sql server.
Tip 2:
Always use Transaction in necessary purpose, because to many transactions some times make deadlock or block other user from access.
Tip 3:
Always try to avoid using of Cursor in DB, if need minimally use While loop.
Tip 4:
Always use same data type variables to save the value in the columns to avoid the implicit and explicit conversion this leads to table scan and low performance.
Tip 5:
Function on the columns in the where clause or Join can't use indexes appropriately lead to slow performance.
Tip 6:
Minimize the usage of Distinct, Order by, Union
Tip 7:
Table variable doesn't have statistics , so Table variable reduce the performance in Joins and Searches.
Tip 8:
Avoid creating stored procedures that have wide range of data supplied to them as parameters because these are compiled to use just as one query plan.
Tip 9:
Temporary table have statistics, which get updated when data is inserted so recompile is possible ,to avoid this use Table variable.
Tip 10:
Always use tables in the view instead of referring another view because this will gives performance impact.
Tip 11:
Always use one clustered index for a table, which is the column in regular access.
Tip 12:
Always try to use columns in where clause as well as in order by that have indexes.
Tip 13:
Don't use the columns in indexes that is frequently updated the value.
Tip 14:
Creating a Too many of non-clustered indexes will slow down the performance.
Tip 15:
Always use the foreign column as indexed because this will avoid the table scan , while performing the deletion operation in referenced table due to primary key deletion.
Tip 16:
A Clustered index on column which have Guid values leads to Fragmentation of index, due to random number generation. so always use NEWSEQUENTIALID() to generated the Guid for indexed column for Guid.
Tip 17:
Always use SET NOCOUNT ON at the start of the stored procedure and SET NOCOUNT OFF at the end of the stored procedure this makes disables the counting of number of records affected.
Tip 18:
Always use the Normalization in your table structure, this will improve the performance ,avoid the repetition of data.
I Hope this article will help you in your work in Sql Server, to improve the performance.
No comments:
Post a Comment