SQL Server Index Tuning for Mere Mortals

One of the most important things to measure, monitor, and “get right” for good overall SQL Server OLTP performance is the number and composition of your SQL Server relational indexes. It is extremely important to have neither too many or too few relational indexes on each table, and that the indexes you do have are being used effectively by your workload.

Every time you modify data in a table(with a insert, update, or delete), the affected indexes must also be updated. With an OLTP workload (which means that your data is volatile) you typically want to have fewer indexes than with a DSS/DW type of workload (where the data is relatively static). One guideline from Tom Davidson when he was on the SQLCAT team in 2006 was to try to have less than four indexes on tables that were frequently updated. Lindsey Allen (from SQLCAT) also discussed the cost of having too many relational indexes here.  I generally start to get concerned when I see more than about five or six relational indexes on a volatile table. Having too many relational indexes has a negative effect on insert, update, and delete performance. Ultimately, it is a judgment call, based on your workload, your hardware and I/O subsystem, and on your experience.

On the other hand, if you have too few effective indexes or you are “missing” important indexes that SQL Server could effectively use to satisfy its query workload, you will also see bad overall query performance. You will see table or index scans where a seek could have satisfied a query that only returned a few rows of data. This can cause increased memory, I/O, and CPU pressure, depending on the situation. It can also cause extra locking and blocking while these extra scans are occurring.

What you want to have is a happy balance between too many and two few relational indexes. You want to eliminate indexes that are not being used, and add indexes that SQL Server thinks it needs (using your own good judgment and knowledge of your workload). After all, my two miniature dachshunds always think they need more doggie treats, but I sometimes know better!

DSC00508

Here are a couple of queries that will help you reach this sweet spot for your relational indexes. I like to call these two the “dynamic duo”, since they are so useful for judging whether you need to adjust your overall index strategy at the database level.

-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], 
i.name AS [Index Name], i.index_id,
user_updates AS [Total Writes], 
user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
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 = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1
ORDER BY [Difference] DESC, [Total Writes] DESC, 
[Total Reads] ASC OPTION (RECOMPILE);

-- Look for indexes with high numbers of writes 
-- and zero or a very low numbers of reads
-- Consider your complete workload
-- Investigate further before dropping an index


-- Missing Indexes current database by Index Advantage
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 mid.database_id = DB_ID() -- Remove this to see for entire instance
ORDER BY index_advantage DESC OPTION (RECOMPILE);

-- Look at last user seek time, number of user seeks 
-- to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!

After I look at that, I tend to try to focus on a single table in more detail before I make any changes. You should not just start blindly dropping or adding indexes based on these two queries alone. You should evaluate your existing indexes on that table and consider your complete workload, and apply some good human judgment before you make index changes.

The queries below will help focus your attention on a single table. In this case, I am looking at the ActivityEvent table.

-- Index Read/Write stats for a single table
SELECT OBJECT_NAME(s.[object_id]) AS [TableName], 
i.name AS [IndexName], i.index_id,
SUM(user_seeks) AS [User Seeks], SUM(user_scans) AS [User Scans], 
SUM(user_lookups)AS [User Lookups],
SUM(user_seeks + user_scans + user_lookups)AS [Total Reads], 
SUM(user_updates) AS [Total Writes]     
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 = DB_ID()
AND OBJECT_NAME(s.[object_id]) = N'ActivityEvent'
GROUP BY OBJECT_NAME(s.[object_id]), i.name, i.index_id
ORDER BY [Total Writes] DESC, [Total Reads] DESC OPTION (RECOMPILE);


-- 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
-- Specify one database, schema,table
WHERE [statement] = N'[NewsGator_SocialServices].[dbo].[ActivityEvent]' 
ORDER BY index_advantage DESC OPTION (RECOMPILE);


-- Look at existing indexes 
--(does not show included columns or filtered indexes)
EXEC sp_helpindex N'dbo.ActivityEvent';

-- See how big the table is
EXEC sp_spaceused N'dbo.ActivityEvent';

Remember, sp_helpindex does not show information about included columns or filtered indexes. One work-around is to script out the index creation DDL statements for each existing index to see what is really there. You can also use Kimberly Tripp’s (blog|twitter) excellent sp_helpindex2 procedure to pickup that information.

About these ads
This entry was posted in SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and tagged , , , . Bookmark the permalink.

10 Responses to SQL Server Index Tuning for Mere Mortals

  1. Dave Schutz says:

    Wow, interesting stuff. I’ll try those queries tomorrow.

  2. Glenn Berry says:

    Glad you found it interesting. Let me know what you find out.

  3. Pingback: @GlennAlanBerry posts SQL Server Index Tuning for Mere Mortals | sqlmashup

  4. Pingback: Tweets that mention SQL Server Index Tuning for Mere Mortals | Glenn Berry's SQL Server Performance -- Topsy.com

  5. Pingback: SQL Server Blogs and news for the week 14/01/11 | John Sansom - SQL Server DBA in the UK

  6. Pingback: Five DMV Queries That Will Make You a Superhero in 2011! | Glenn Berry's SQL Server Performance

  7. Nauman Aziz says:

    I have found one serious index which was missing, what would you suggest should i include the columns in the index which are included or should i first create an index on the column only.

    Euality-Column Inequality-Coulmn Included-ColumnUser-Seek
    [TransactionItemId] NULL NULL
    [TransactionItemId] NULL [PaymentCardClassId], [IsCreditCard]

    User-Seek
    245197
    107944

  8. Glenn Berry says:

    Nauman,

    It depends on your workload and what existing indexes you have on that table. You don’t want to have too many indexes on a volatile table with an OLTP workload. I would try adding the suggested index (with the included columns) and seeing what effect it has.

  9. Nauman Aziz says:

    Thanks Glenn,
    I have just created a suggested index (without included column) on that table and the results are superb, a process was taking 30 minutes before but now its taking 5 to 8 minutes. Now I am thinking as this process is kind of selecting records and inserting into another table the table which is write intensive has a cluster index with default fill factor 0 I would like to change the fill factor to 85% so that insertions will be fast.
    Any comments on this will be highly appreciated

  10. Pingback: Indexing Strategies for OLTP Databases. « SQLGiant

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