What is before Analysing execution plan and Indexes?
Now I know which query is most expensive in terms of CPU and execution time. Now what’s next??Many of us will jump into the execution plan and start looking at the key operator’s immediately.
Before we go there, we should consider outer factors which can also affect the query performance. By outer factors I do not mean "hardware resources", but components within the SQL Server RDBMS itself.
These are:
Database Object StatisticsAny object referred inside the query has statistics which optimizer uses to decide the execution plan. All execution plans are based upon these statistics.
This means if the database object statistics are not up-to-date, then the execution plan will be inefficient also.
You can check the statistics of the table and its indexes by running classic DBCC SHOW_STATISTICS command.
Index defragmentation
If the database objects referred inside the query are fragmented heavily then this will cause query to read more pages then required.
Make sure your indexes are not heavily fragmented. In sql server 2005 and onwards you can check this by using the DMV sys.dm_db_Index_physical_stats.
I normally look for any fragmentation level of more than 30% and also look at number of pages of the indexes. If index with high number of pages is fragmented heavily then that is definitely going to affect performance.
Connection level options
Setting connection level options different then default value inside the query can cause operations like recompilation. Make sure your Connection level options are set properly inside the body
of TSQL.
Before we go there, we should consider outer factors which can also affect the query performance. By outer factors I do not mean "hardware resources", but components within the SQL Server RDBMS itself.
These are:
Database Object StatisticsAny object referred inside the query has statistics which optimizer uses to decide the execution plan. All execution plans are based upon these statistics.
This means if the database object statistics are not up-to-date, then the execution plan will be inefficient also.
You can check the statistics of the table and its indexes by running classic DBCC SHOW_STATISTICS command.
Index defragmentation
If the database objects referred inside the query are fragmented heavily then this will cause query to read more pages then required.
Make sure your indexes are not heavily fragmented. In sql server 2005 and onwards you can check this by using the DMV sys.dm_db_Index_physical_stats.
I normally look for any fragmentation level of more than 30% and also look at number of pages of the indexes. If index with high number of pages is fragmented heavily then that is definitely going to affect performance.
Connection level options
Setting connection level options different then default value inside the query can cause operations like recompilation. Make sure your Connection level options are set properly inside the body
of TSQL.
No comments:
Post a Comment