Sunday, September 26, 2010

Finding missing Indexes in SQL Server

As you might already know SQL Server DMV's (Dynamic Management Views) started from SQL Server 2005 are very helpful in analysing the SQL Server performance (finding locks, missing indexes, indexes which are not in use and causing overhead, long running t-sql queries, sp's etc...). I enjoy reading the Brent Ozar's blog in SQL Server performance and they have been very helpful personally to me in understanding the SQL Server performance tuning.
The SQL Server performance tuning used to be a task of DBA's to work on in the time of SQL Server 2000, but going forward starting from SQL Server 2005 with the help of DMV's a normal SQL developer also can contribute a lot in this area.
In this article I am going to talk about finding the missing indexes in SQL Server 2005/2008. Indexes are very critical if we talk about SQL Server performance and can impact a lot. If the transactional system is used for reporting purpose also the index can play a major role in improving performance of the reports. One thing we need to be very carefuly before adding new indexes is to make sure we analyse them thoroughly before adding. If any erp system/app is ruuning on top of SQL Server we need to make sure and understand adding a new index is not going to effect the performance of erp system/app since adding new index means more time in insertion and update activities.
I have copied the script below which is very helpful and gives top missing indexes with their impact. Adding these top index can definitely improve the SQL Server performance but once again we should very careful before adding these. We should be analysing these first in the staging/dev. environment before adding in production if required.



SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(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 AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE (migs.group_handle IN
(
SELECT TOP (500) 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))
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1
ORDER BY 2 DESC , 3 DESC