SQL Server tools for performance

Since the introduction of SQL Server 2005 Microsoft implemented Dynamic Management Views (DMV’s) in the database engine that enable us to peek inside of it, and retrieve the internal –performance- counters.

In this post I present a couple of queries gathered and used over time that assisted me to pin down the problem when having performance related issues.

If you’ve inherited a system from an older / upgraded SQL Server version, I recommend running the ‘unused indexes‘ query mentioned later on. It saved my life in more than one occasion.
In my experience as tables have grown bigger, indexes were created for specific needs and over time became obsolete, but no one bothered to remove them, leaving the system with a lot of unnecessary I/O overhead.

Explore sys.dm_exec_query_stats it contains more fields than used here (last_physical_reads for instance) and also check out other DMVs for additional info you might be interested in.

Before starting any investigation make sure that all indexes are defragmented and all the statistics are up to date up front (see my post on index reorganization)

The queries described here, combined with my previous posts on fn_virtualfilestats, auto index management, STATISTICS IO, multiple files per database and index reorganization should supply you with enough info to tackle most of I/O related performance issues.

Beware: DMV’s are reset when the SQL Server instance is restarted! So the numbers are only meaningful when the db engine is running for a considerable amount of time

First determine how long the instance has been running:

SELECT crdate AS Instance_Started_On
FROM sysdatabases
WHERE name = 'tempdb'
 

Queries with highest IO load:

SELECT TOP 100
        total_logical_reads, total_logical_writes, execution_count,
        total_logical_reads+total_logical_writes AS total_IO,
        st.TEXT AS query_text,
    st.dbid AS database_id,
        DB_NAME(st.dbid) AS database_name
    FROM sys.dm_exec_query_stats  qs
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
   WHERE total_logical_reads+total_logical_writes > 0
   ORDER BY (total_logical_reads+total_logical_writes) DESC -– most i/o intensive
 

Queries using the most CPU:

SELECT TOP 100
    highest_cpu_queries.total_worker_time,
    sql_text.dbid,
    DB_NAME(sql_text.dbid) AS database_name,
    sql_text.[TEXT]
FROM  
    (SELECT TOP 100 PERCENT
        qs.plan_handle,  
        qs.total_worker_time
    FROM  
        sys.dm_exec_query_stats qs
    ORDER BY qs.total_worker_time DESC) AS highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) AS sql_text
ORDER BY highest_cpu_queries.total_worker_time DESC
 

Get the unused indexes:

USE your_db_name –- switch TO your own db here BEFORE executing the dmv query below
go

SELECT TOP 100
OBJECT_NAME(i.OBJECT_ID) AS tablename,
i.name AS indexname,
s.user_updates,
s.user_seeks + s.user_scans + s.user_lookups AS totalreads,
s.user_updates AS totalwrites
FROM sys.indexes i
            LEFT join sys.dm_db_index_usage_stats s
ON s.OBJECT_ID = i.OBJECT_ID and  
                  i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsIndexable') = 1 and
-- index_usage_stats has no reference to this index (not being used)
s.index_id IS null or
-- index is being updated, but not used by seeks/scans/lookups
(s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0)
ORDER BY s.user_updates DESC
go