jump to navigation

Update Statistics after INDEX Rebuild November 17, 2014

Posted by furrukhbaig in Execution Plan.
Tags: , , , , , ,
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 ūüėČ

Enjoy.

 

Is this Index useful ? August 17, 2007

Posted 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: , , , , , , , , ,
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 indexes

SELECT  
            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, 2007

Posted by furrukhbaig in DMV's, Indexes, Performance, SQL Server 2005, TSQL.
Tags: , , , , , ,
8 comments

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