There is an interesting and useful new feature in SQL 2005 where it takes all queries that execute and don't find an optimal index...and records the information. You can use this information to generate indexes and speed up your performance. These are only held as long as your SQL Server is running...rebooting it will flush out any information stored.
You get the information from a built in view documented in MSDN: sys.dm_db_missing_index_details
http://msdn2.microsoft.com/en-us/library/ms345405.aspx
However, sys.dm_db_missing_index_details only take you half of the way home. It lists the pieces of the index to be created, but still requires you to construct the CREATE NONCLUSTERED INDEX SQL yourself. This requires you to learn how to do it (from the above link)...which means understanding the internals of how it works. (According to this 2nd link, the SQL team initially implemented the "whole enchalida" and then later removed it: http://solidqualitylearning.com/blogs/mike/archive/2005/09/26/1455.aspx )
The link above argues that this is a good thing, becuase it says that these are just suggestions rather than guarantees (and you still need to verify that you want to truly run each one). If this is true...I would argue back that forcing someone to piece together everything to create the query doesn't reinforce the fact that it's a suggestion. It just aggravates the DBA. The proper way to do this would be to just document that these are just suggestions in the reference on this stored procedure...not make it harder on the DBA who might have to implement it.
Anyway...fortunately for you (or unfortunately...depending on your point of view), I've created the following that will create the SQL for you. :) What it does is takes the top 10 queries that the view says would improve most from new indices and creates the SQL statement for them. You can adjust the # returned by changing: SELECT TOP (10) group_handle.
SELECT 'CREATE NONCLUSTERED INDEX <NewNameHere> ON ' + sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN mid.inequality_columns IS NULL
THEN '' ELSE CASE WHEN mid.equality_columns IS NULL
THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL
THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN
sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle INNER JOIN
sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle INNER JOIN
sys.objects WITH (nolock) ON mid.object_id = sys.objects.object_id
WHERE (migs.group_handle IN
(SELECT TOP (10) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))
Sample Output:
CREATE NONCLUSTERED INDEX <NewNameHere> ON MYtable ( [PId], [PersonId], [Hidden] ) INCLUDE ([Id], [BRId], [Amount], [Comment], [OtherDate], [ParentBId]);
Caveats:
1) Before adding each one...review it and make sure you really want to add it to your database. Per
http://solidqualitylearning.com/blogs/mike/archive/2005/09/26/1455.aspx
"These are suggestions, not absolutes. It still requires a DBA to take a look at the index and decide whether it should be created."
2) Use this at your own risk. I experienced a few times where I followed the MSDN article's steps exactly (which is what this view is based on) and it wouldn't create the index properly. Example: sometimes the INCLUDE field returned by the system view has text fields in it. But actually using it in the INCLUDE results in this error:
Column 'TextField' in table 'myTable' is of a type that is invalid for use as included column in an index.
So test the SQL first on your dev server and make sure it's right.
3) The generated SQL doesn't do one thing recommended by MSDN. It says "To determine an effective order for the equality columns, order them based on their selectivity; that is, list the most selective columns first." Doing this automatically would take more time than I had to devote to this. So check the order of the SQL produced and if the index fields aren't in the order you'd like, you can change them around. And if anyone wishes to create something that handles this too...then please post it here.
Ian Ippolito