Update Statistics after INDEX Rebuild November 17, 2014Posted by furrukhbaig in Execution Plan.
Tags: execution plan, FULLSCAN, Performance Tuning, REBUILD INDEX, scan statistics, statistics, UPDATE STATISTICS
add a comment
Its a common mistake during overnight maintenance job that we rebuild indexes and then update statistics. Its something commonly overlooked. Update statistics uses sample rows by default while rebuild index will update statistics with full scan as part of index rebuild. If we don’t specify FULLSCAN option with update statistics it will overwrite full scan statistics with sampled statistics which may mean completely different/inefficient query execution plan. Even if you update statistics with full scan its still waste of resources as rebuild already did that.
I hope it will help someone safe couple of minutes during overnight maintenance window 😉
Is this Index useful ? August 17, 2007Posted by furrukhbaig in DMV's, dm_db_index_usage_stats, Index tuning, Index usefulness, Indexes, Optimize, Performance, Performance Tuning, SQL Server 2005, SQL Server 2005 features, TSQL.
Tags: dmv, dm_db_index_usage_stats, index usage, indexes, Performance Tuning, sql 2012 indexes, sql server 2005, SQL server 2008 R2, sql server 2012, sql tips
1 comment so far
Creating indexes on tables always been a tricky question. Creating a index does not mean that optimizer will use that index to solve queries. SQL Server 2005 introduced a dynamic management view sys.dm_db_index_usage_stats to capture the use of indexes by SELECT, INSERT, UPDATE and DELETE. Thus any index or table which does not exists in this view is overhead to system and not useful.
Following query will return all the indexes and tables which never get used by SQL Server since the service started. The best way to check is to create index and run the data work load or regression test that should make use of all indexes and run following query.see full article on SQL Server Customer Advisory Team (CAT) How can SQL Server 2005 help me evaluate and manage indexesSELECT ObjectName = OBJECT_NAME(ind.object_id), IndexName = ind.name FROM Sys.Indexes ind INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id WHERE OBJECTPROPERTY(obj.object_id,‘IsUserTable’) = 1 AND NOT EXISTS ( SELECT 1 FROM Sys.dm_db_index_usage_stats usg WHERE usg.object_id = ind.object_id AND usg.index_id = ind.index_id AND ind.index_id = usg.index_id ) ORDER BY ObjectName, IndexName
Worst Performing Queries August 17, 2007Posted by furrukhbaig in DMV's, Indexes, Performance, SQL Server 2005, TSQL.
Tags: dmv, dm_exec_query_stats, Performance Tuning, query performance, query tuning, TSQL, worst performing query
I have been looking for a easy way to find worst performing queries without runing Profiler and I come across very interesting DMV’s dm_exec_query_stats and dm_exec_sql_text.Microsoft has introduced Dynamic Management Views (sys.dm_exec_query_stats, sys.dm_exec_sql_text) in SQL Server 2005 which provide a way to fetch information related to resources used by SQL Server for executing SQL statements.
Following query will return TOP 100 worst performing SQL Statement with the name of object that contain those statements e.g. Stored Procedure, Trigger and Function. Current database context will be used so change the database before executing query. The result will be sorted by CPU time in descending order.SELECT TOP 100 [Object_Name] = object_name(st.objectid), creation_time, last_execution_time, total_cpu_time = total_worker_time / 1000, avg_cpu_time = (total_worker_time / execution_count) / 1000, min_cpu_time = min_worker_time / 1000, max_cpu_time = max_worker_time / 1000, last_cpu_time = last_worker_time / 1000, total_time_elapsed = total_elapsed_time / 1000 , avg_time_elapsed = (total_elapsed_time / execution_count) / 1000, min_time_elapsed = min_elapsed_time / 1000, max_time_elapsed = max_elapsed_time / 1000, avg_physical_reads = total_physical_reads / execution_count, avg_logical_reads = total_logical_reads / execution_count, execution_count, SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ( ( CASE statement_end_offset WHEN –1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END – qs.statement_start_offset ) /2 ) + 1 ) as statement_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE Object_Name(st.objectid) IS NOT NULL AND st.dbid = DB_ID() ORDER BY db_name(st.dbid), total_worker_time / execution_count DESC