Five Very Useful Index Selection Queries for SQL Server 2005

When it comes to maintaining and improving SQL Server 2005 performance, it is pretty hard to think of anything that is more important than proper index selection and maintenance. In earlier versions of SQL Server, it was pretty difficult to determine how your indexes were being used, but the story is much better in SQL Server 2005.

One of the most useful new features in SQL Server 2005 is Dynamic Management Views (DMV), which collect a plethora of useful information about what is going on with your SQL Server 2005 instance (since it was last started). You need to have VIEW SERVER STATE permission to access most of these views.

 

1. Find "Missing" Indexes for the entire instance of SQL Server

This query will give you a list of indexes that the query optimizer would have liked to have had, based on the workload. I like to run this first, and see if there are any tables that jump out with multiple missing indexes. You also want to look at the last_user_seek column to see when was the last time the optimizer wanted an index. If it is several hours or days ago, it may have been from an ad-hoc query of maintenance job rather than your normal workload.

-- Missing Indexes for the entire instance
    SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, mid.statement as 'Database.Schema.Table',
    mid.equality_columns, mid.inequality_columns, mid.included_columns,
    migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
    FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
    ORDER BY index_advantage DESC;

2. Find "Missing" Indexes for a single table

This query will give you a list of indexes that the query optimizer would have liked to have had, based on the workload, for one table. Generally speaking, you want to have the column(s) mentioned in the mid.equality_columns column as the first column in your new index, based on their selectivity.

-- Missing Indexes for a single table
    SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage, migs.last_user_seek, 
           mid.statement as 'Database.Schema.Table',
           mid.equality_columns, mid.inequality_columns, mid.included_columns,
           migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
    FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
    ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    ON mig.index_handle = mid.index_handle
    WHERE statement = '[yourdatabasename].[dbo].[yourtablename]' -- Specify one table
    ORDER BY index_advantage DESC;

 

3. Examine the current index structure for a single table

I like to run #2 and this query together, when I am focusing on a specific table. That way you can easily compare what SQL Server is asking for, to what you already have in place for that table.

-- Examine current indexes for your table
    EXEC sp_HelpIndex 'dbo.yourtablename'

 

4. Look at index usage for a single table

This tells you how your current indexes are being used. If you see an index with many more writes than reads, that means you may want to drop that index (since you are paying the cost of index maintenance for no good reason).

--- Index Read/Write stats for a single table (shows which indexes are being used)
    DECLARE @dbid int
    SELECT @dbid = db_id()

    SELECT objectname = object_name(s.object_id), indexname = i.name, i.index_id,
           reads = user_seeks + user_scans + user_lookups, writes =  user_updates
    FROM sys.dm_db_index_usage_stats AS s, sys.indexes AS i
    WHERE objectproperty(s.object_id,'IsUserTable') = 1
    AND s.object_id = i.object_id
    AND i.index_id = s.index_id
    AND s.database_id = @dbid
    AND object_name(s.object_id) = 'yourtablename'
    ORDER BY object_name(s.object_id), writes DESC, reads DESC;

 

5. Look for possible bad indexes inside the entire current database

This tells you how your current indexes are being used across your entire database. If you see an index with many more writes than reads, that means you may want to drop that index (since you are paying the cost of index maintenance for no good reason).

-- Possible Bad Indexes (writes > reads)
    DECLARE @dbid int
    SELECT @dbid = db_id()

    SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,
           'Total Writes' =  user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,
            'Difference' = user_updates - (user_seeks + user_scans + user_lookups)
    FROM sys.dm_db_index_usage_stats AS s 
    INNER JOIN sys.indexes AS i
    ON s.object_id = i.object_id
    AND i.index_id = s.index_id
    WHERE objectproperty(s.object_id,'IsUserTable') = 1
    AND s.database_id = @dbid
    AND user_updates > (user_seeks + user_scans + user_lookups)
    ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

 

6. Drill into your workload (Bonus)

This gives you a better idea which stored procedures are called the most frequently, along with their average and total cost in terms of CPU (worker time), logical reads (memory pressure), and physical reads (I/O pressure). Depending on your workload and situation, you will be concerned with different issues at different times (CPU, memory or I/O).

-- Get Top 100 executed SP's ordered by calls/sec
    SELECT TOP 100 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',  
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, qs.creation_time,
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
    qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'
    , qt.dbid 
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    --WHERE qt.dbid = 5 -- Filter by database
    ORDER BY qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) DESC
 

About these ads
This entry was posted in SQL Server 2005. Bookmark the permalink.

6 Responses to Five Very Useful Index Selection Queries for SQL Server 2005

  1. Brian says:

    The headline claims 5 Very Useful Queries, but there are 6 in the post. So which one isn\’t very useful? :)

  2. David says:

    Awesome article – it\’s been super useful. I had some light issues with #6. I was getting divide by zero errors in my test environment. Here are the tweaks that I made:– Get Top 100 executed SP\’s ordered by calls/sec SELECT TOP 100 qt.text AS \’SP Name\’, qs.execution_count AS \’Execution Count\’, ISNULL((qs.total_worker_time/ NULLIF(qs.execution_count, 0)), -1) AS \’AvgWorkerTime\’, qs.total_worker_time AS \’TotalWorkerTime\’, ISNULL((qs.total_elapsed_time/ NULLIF(qs.execution_count, 0)), -1) AS \’AvgElapsedTime\’, qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, qs.creation_time, DATEDIFF(Minute, qs.creation_time, GetDate()) AS \’Age in Cache\’, ISNULL((qs.execution_count/NULLIF(DATEDIFF(Second, qs.creation_time, GetDate()), 0)), -1) AS \’Calls/Second\’ , qt.dbid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt –WHERE qt.dbid = 5 — Filter by database ORDER BY ISNULL((qs.execution_count/NULLIF(DATEDIFF(Second, qs.creation_time, GetDate()), 0)), -1) DESC

  3. Terry says:

    When running #6, I get ‘The user does not have permission to perform this action.’ Any idea what ‘role’ I should grant to make it possible to run query?

  4. Terry says:

    For the ‘Missing Indexes’ and ‘Bad Indexes’ queries above, would queries executed inside a Stored Procedure be taken into account? I’m getting a ‘Bad Index’ saying 11 Reads and 6M writes…but I KNOW I use that field inside a SP very frequently in a WHERE clause when updating data. The field is a ‘Job Status’ field and I only do certain things when a job is ‘complete’ so in lots of update statements I have: WHERE jStatus = 2.

    Thanks for the info.

  5. Glenn Berry says:

    This query takes all index usage (since the last time SQL Server was restarted) into account. I would look at the execution plan of the stored procedure that you are sure is using the index to make sure. There are lots of reasons (such as implicit conversions) that will cause SQL Server to not use an index that you think it should.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s