I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?
Yes, there are tools, like the database engine tuning advisor. It ships with SQL Server and does some pretty nasty things:
- It allows you to consider only a very small workload (even a single query). Which will then suggest indexes that may help that small workload only, with no regard whatsoever to the rest of the workload (indexes help speed up some queries, but they can slow down others, especially insert/update/delete), never mind the rest of the business cycle.
- It will often recommend redundant indexes that only differ by a trailing column or by an included column.
- It really, really, really likes included columns - I think it may be a fetish.
The proper approach to index tuning is to consider your entire workload over a complete business cycle. Sometimes it is better to turn to 3rd party tools than reinvent the wheel. DBSophic makes a free product called Qure Workload Analyzer that I think does a much better job than DTA - but their licensed tool (Qure Workload Optimizer) is absolutely phenomenal at what it does. Last year I blogged about how the tool works when integrated with SQL Sentry).
There are some other options, too, for example missing indexes are reported in the DMVs, and you can also discover which indexes are being written to (so they are taking up space and affecting insert/update/delete operations) but never (or rarely) used by user queries:
- Find missing indexes using the DMVs
- Find unused indexes using Policy-Based Management
- Discovering unused indexes
- Deeper insight into unused indexes
But these also need the caveat that the DMVs are only valid since the last SQL Server restart, and that they won't necessarily cover a proper business cycle (and hence may be missing vital information
The tool you are talking about is called the Database Engine Tuning Advisor (DTA for short). It can take a number of inputs and give recommendations on things like missing indexes. It is worth noting, though, that these are just suggestions and careful consideration should be taken instead of blindly following DTA's recommendations.
It is also worth noting that there are built-in DMVs for gathering that type of information:
It might sound too good to be true but I found one query that does exactly that:
USE YourDatabase GO SELECT db.[name] AS [DatabaseName] ,id.[object_id] AS [ObjectID] ,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName] ,id.[statement] AS [FullyQualifiedObjectName] ,id.[equality_columns] AS [EqualityColumns] ,id.[inequality_columns] AS [InEqualityColumns] ,id.[included_columns] AS [IncludedColumns] ,gs.[unique_compiles] AS [UniqueCompiles] ,gs.[user_seeks] AS [UserSeeks] ,gs.[user_scans] AS [UserScans] ,gs.[last_user_seek] AS [LastUserSeekTime] ,gs.[last_user_scan] AS [LastUserScanTime] ,gs.[avg_total_user_cost] AS [AvgTotalUserCost] -- Average cost of the user queries that could be reduced by the index in the group. ,gs.[avg_user_impact] AS [AvgUserImpact] -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented. ,gs.[system_seeks] AS [SystemSeeks] ,gs.[system_scans] AS [SystemScans] ,gs.[last_system_seek] AS [LastSystemSeekTime] ,gs.[last_system_scan] AS [LastSystemScanTime] ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost] ,gs.[avg_system_impact] AS [AvgSystemImpact] -- Average percentage benefit that system queries could experience if this missing index group was implemented. ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage] ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN '_' ELSE '' END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE WHEN id.[equality_columns] IS NOT NULL AND id.[inequality_columns] IS NOT NULL THEN ',' ELSE '' END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex] ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate] FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK) INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle] INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle] INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id] WHERE db.[database_id] = DB_ID() --AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName' ORDER BY ObjectName, [IndexAdvantage] DESC OPTION (RECOMPILE);