BPSoftware.com

Lately, I have been doing a lot with SQL (2005) optimization.  Database design plays an important role in the efficiency of a database.  A key part of the design is the use of indexes.  Indexes significantly affect the performance of a database whether it is during a SELECT, INSERT, UPDATE and DELETE Transact-SQL (DML) statement.  The biggest gain from an index is when a user SELECTs data from a database.   The designer of the database and application should have done their best to come up with indexes that accommodate how the database will be used.  How can one be sure they’re not missing any indexes?

-- ##############################
-- Check for missing indexes
-- ##############################
USE [master];

SELECT 
	ig.index_group_handle
	,id.index_handle
	,id.database_id
	,db_name(id.database_id) AS database_name
	,id.[object_id]
	,object_name(id.[object_id],id.database_id) AS table_name
	,id.statement AS full_table_name
	,id.equality_columns
	,id.inequality_columns
	,id.included_columns
	,gs.user_seeks
	,gs.user_scans
	,gs.last_user_seek
	,gs.last_user_scan
	,gs.avg_total_user_cost
	,gs.avg_user_impact
	,gs.system_seeks
	,gs.system_scans
	,gs.last_system_seek
	,gs.last_system_scan
	,gs.avg_total_system_cost
FROM 
	sys.dm_db_missing_index_details id INNER JOIN sys.dm_db_missing_index_groups ig
		ON (id.index_handle = ig.index_handle)
	inner join sys.dm_db_missing_index_group_stats gs
		ON gs.group_handle = ig.index_group_handle
ORDER BY 
	((gs.avg_total_user_cost * gs.avg_user_impact) * (gs.user_seeks + gs.user_scans)) DESC

-- ##############################

Using the above query result you could create the necessary index (the field names are listed where their values should be used):

CREATE NONCLUSTERED INDEX <unique index name>
ON <FULL_TABLE_NAME> (<EQUALITY_COLUMNS>,<INEQUALITY_COLUMNS>) -- exclude INEQUALITY_COLUMNS if NULL
INCLUDE (<INCLUDED_COLUMNS>); -- exclude INCLUDED_COLUMNS if NULL