Overview
I've just found this by chance while in the process of reviewing around 100 stored procedures to check for performance efficiency. It doesn't help with badly written queries, but if the query is reasonable then it can help you find those elusive missing indices. Thought I'd have to find them myself, but thankfully help is at hand...
Details
Normally, you would use the SQL Server Profiler to help track performance issues, but what if you are not in a live environment? SQL Server 2005 helpfully collects some data that you can use.
Warning: the data is only kept for while the server is running. If it restarts, you'll have to wait for it to recalculate the information!
The database automatically collects information on indices that it would have used had they existed. You can use this to investigate whether creating the index is warranted. SQL Server also lets you know its potential value. This feature is on by default.
Have a look at the references section for more details on the feature, but here is an example script to show you some of the information that is available:
USE [master]
GO
SELECT statement, avg_total_user_cost, avg_user_impact, statement,
equality_columns, inequality_columns, included_columns
FROM sys.dm_db_missing_index_group_stats a INNER JOIN sys.dm_db_missing_index_groups b ON a.group_handle = b.index_group_handle INNER JOIN
sys.dm_db_missing_index_details c ON b.index_handle = c.index_handle
ORDER BY avg_user_impact DESC
GO
References
Versions
Metadata
- Categories: SQL Server, IT Management, Software Development, Performance Analysis
- Additional keywords: query optimisation, missing indexes, statistics, tuning
- Technorati Tags:
SQL,
SQL Server,
performance,
database indexing,
IT Management