10

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?

2
  • 1
    There are suggested indexes but these are very limited and should still be evaluated by someone who knows what they are doing. Do you have a specific question about indexing?
    – JNK
    Jun 7 '12 at 13:02
  • 1
    Is there a query/table you're having poor performance on? As JNK implied you should really only add indexes when you know what you're doing (and test it regardless). Unless you have reason to believe a table needs an index (huge table, slow queries, an obvious clustered index) you shouldn't just try and add them
    – Ben Brocka
    Jun 7 '12 at 13:07
13

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:

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

6

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.

Link for DTA Tutorial

It is also worth noting that there are built-in DMVs for gathering that type of information:

0

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.