SQL Server 2005/2008 database tuning - picking low hanging fruits - part I - Notes from SQL Server Premier Field Engineer - Site Home

Hello fellow readers!

It has been a while since I posted. Sorry about that - have been busy travelling around customers in Australia.

Let me write a few words how you can easily increase performance of your database application without doing much hard work. Sounds cheezy you might think. Not at all. I would like to cover two easy options you have to collect those low hanging fruits of optimizing SQL Server performance. First shows the power of dynamic management views (DMVs) and second uses tools which have been shipping with SQL Server for a long time - Database Tuning Advisor and SQL Profiler. Let's cover these approaches one at a time.

1. Dynamic management views

SQL Server 2005/2008 has a set of dynamic management views maintained by query optimizer which tell you if there are any indexes missing. These DMVs are populated based on queries optimizer has seen so far since last time SQL Server booted up. They are: sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats.

Simly open SQL Server Management Studio of your production server and type:

select * from sys.dm_db_missing_index_details

select * from sys.dm_db_missing_index_groups

select * from sys.dm_db_missing_index_group_stats

and look at the output. You maybe surprised how much entries are there - I suggested one customer to do a quick check and we saw ~400 entries in sys.dm_db_missing_index_details. This SQL Server environment had multiple databases and was shared between multiple applications, so don't be totally scared with a big number. Still, it shows that there is a big room for improvement to optimize performance of SQL Server installation. I am sure, you will see some in your environment as well. It is especially interesting to see what output is if you just recently migrated your databases from SQL Server 2000 to 2005 or 2008.

Lets have a quick look at the columns of the sys.dm_db_missing_index_details DMV. Our best friend SQL Server books online has this to say about them:

Column name	Data type	Description
index_handle	int	Identifies a particular missing index. The identifier is unique across the server. index_handle is the key of this table.
database_id	smallint	Identifies the database where the table with the missing index resides.
object_id	int	Identifies the table where the index is missing.
equality_columns	nvarchar(4000)	Comma-separated list of columns that contribute to equality predicates of the form: table.column = constant_value
inequality_columns	nvarchar(4000)	Comma-separated list of columns that contribute to inequality predicates, for example, predicates of the form: table.column > constant_value Any comparison operator other than "=" expresses inequality. For a complete list of comparison operators, see Comparison Operators (Database Engine).
included_columns	nvarchar(4000)	Comma-separated list of columns needed as covering columns for the query. For more information about covering or included columns, see Creating Indexes with Included Columns.
statement	nvarchar(4000)	Name of the table where the index is missing.

As one can see it is easy to build a CREATE INDEX statement based on the information in this DMV. Unfortunately, you have to do it by hand. I am not yet aware of simple tool which will autoscript all the entries into a SQL script. You may not necessarily want to create all indexes logged in the 'missing indexes DMV'. Try to correlate its entries with frequency of certain queries which can be collected through profiler or looked in the SQL Server plan cache DMVs like sys.dm_exec_query_stats or other sys.dm_exec_* DMVs. At the very least, discuss this output to your developers and implement some of them in your Dev environment and see if it helps your performance.

This is all for now. Please collect your juicy fruits!

Next time I will cover another method of using SQL Server profiler and Database Tuning Advisor.