Which indexes are missing?
Yes, yes, you can actually answer that question now... with the DMV's. Short story, one of the wonderful new features of SQL, introduced in v2005, are the Dynamic Management Views. There are two types, Server & Database-scope, and they provide extensive information about the current state of the SQL Server, and database(s). This data can be very helpful to monitor, diagnose and administer your servers, reactively AND proactively.
The DMVs are replacements for the system tables used within SQL Server 2000.
This statement joins the missing index DMVs, in an effort to identify the most beneficial missing indexes, AND provide the statements necessary to create them.
SET NOCOUNT ON;
SELECT
avg_user_impact AS average_improvement_percentage,
avg_total_user_cost AS average_cost_of_query_without_missing_index,
'CREATE INDEX idx_' + [statement] +
ISNULL(equality_columns, '_') +
ISNULL(inequality_columns, '_') + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, ' ') +
ISNULL(inequality_columns, ' ') + ')' + ISNULL(' INCLUDE (' + included_columns + ')', '') AS create_missing_index_command
FROM
sys.dm_db_missing_index_details a INNER JOIN sys.dm_db_missing_index_groups b
ON a.index_handle = b.index_handle INNER JOIN sys.dm_db_missing_index_group_stats c
ON b.index_group_handle = c.group_handle
ON a.index_handle = b.index_handle INNER JOIN sys.dm_db_missing_index_group_stats c
ON b.index_group_handle = c.group_handle
WHERE
avg_user_impact > = 40
SET NOCOUNT OFF;
NOTE: In the CREATE INDEX statement, 'idx' is my standard index naming convention for non-clustered indexes... change as you see fit.