In my prior post, I covered some of the basics of defragmenting your indexes – an essential for database performance maintenance. But for some, the question may more likely be: how do I even get to indexes? I have seen so many situations where indexes are an afterthought, and lucky if they even get applied – how this came to be, I can go on a soap box rant on how so many developers now a says consider the database passé….. but I’ll stop here, blood pressure….

Moving forward — how would someone either new to the concept, or for those to whom indexing is old hat, look at applying an indexing strategy to an existing database? There are many ways, but I’ll cover one handy method here that makes use of SQL Server 2005’s dynamic management views as a possible starting point. In particular, these three views, will ease our entry into a database that is completely foreign to you(you got dropped into the project or inherited the maintenance):

  • sys.dm_db_missing_index_group_stats: contains performance metrics on a missing grouping of indexes
  • sys.dm_db_missing_index_groups: links group stats to specific index details held in index_details
  • sys.dm_db_missing_index_details: holds detailed information about the missing indexes, such as table and columns that can make up the missing index

The query below returns the detailed information for a specific database – if you do not filter, then you will get results for all databases on the server. The group_stats view contains many metrics(see references below), all of which are useful for various indexing strategies. Since I like to stick with examples that impact end user performance, I have found it most useful to rank in a descending order by the metric user_seeks; I have chosen to highlight this metric as important for the following reasons:

  1. You want to encourage seeks on an index, not scans – so if SQL Server is interpreting benefit of a missing index and finds more seeks, then this should be a priority
  2. It is a user based metric, and we are wanting to improve performance for users of the database
  3. We are looking for the most used missing index, or in other words, the highest number of queries into the system where an index is deemed missing

This by no means is a rule of thumb, but more food for thought – again, it all is situation dependent, but for anyone wondering where to get started and can’t see the forest for the trees, this is one place to pick up and go forward:

 

select        database_id, object_id, statement, equality_columns, inequality_columns, mid.included_columns

            , unique_compiles, user_seeks, user_scans, last_user_seek, last_user_scan, avg_total_user_cost

, avg_user_impact, system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact

from        sys.dm_db_missing_index_group_stats migs

inner join    sys.dm_db_missing_index_groups mig on migs.group_handle = mig.index_group_handle

inner join    sys.dm_db_missing_index_details mid on mig.index_handle = mid.index_handle

where        database_id = (select database_id from sys.databases where name = ‘MyDatabaseToTune’)

order by    user_seeks desc

 

It is important to note that all of these statistics are cleared once the SQL Server service is restarted, so for those of you like me that run development instances on your laptop – you will need to archive this data off to a permanent table in order to look at historical data. From the data returned here, you can take a look at building new indexes against your most used tables – there is a lot to be said on database tuning and performance metrics – too much to be covered in a blog, so be sure to take the bits here in context and realise that everything in indexing and performance is part of a larger picture that shifts depending upon your project.

To save time if you choose to directly implement SQL Server’s recommendations, you can easily take the results from the query above, or modify the query above, to build strings that are your DDL statements for creating indexes – if you have any questions, please feel free to post. Thanks!

 

References:

Group Stats table: http://msdn2.microsoft.com/en-us/library/ms345421.aspx

Index Details: http://msdn2.microsoft.com/en-us/library/ms345434.aspx