Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

DTA and Hypothetical Indexes

For those of you that don’t know DTA stands for Database Engine Tuning Adviser and is available from the Tools menu in Management Studio.  This tool was first introduced in SQL Server 2005 and has been a much used tool by DBA’s and Developers alike in most of the companies I have worked for.

There are two main problems I have with DTA firstly the default object name prefixes are terrible, no really I mean absolutely awful.  See the table below for examples.

Object type
Default object name prefixes
Partition functions
Partition schemes

Now there is no right or wrong way to standardise the names of your database objects but indexes for example I go with the below;

Single Index Key Column - IDX_TableName:ColumnName
Multi Index Key Column – IDX_TableName:CompositeX

Some people will agree some won’t, but in my experience this makes life easier for me and the team when maintaining our SQL estate.

The other problem is that while DTA is analysing a workload, it automatically creates the recommended indexes with the meaningless names as mentioned above.  DTA will always clean up the indexes it creates; well actually that is a lie.  If the DTA process exits then the indexes it has created so far will persist!

We can identify these indexes by the value of the is_hypothetical column of the sys.indexes catalog view, this will be = 1.

I have created the below script which will email a list of hypothetical indexes and the script to drop them, simply schedule this as a SQL Server Agent job as you see fit changing the @EmailProfile and @EmailRecipient variables accordingly;

      Hypothetical Indexes
      For more SQL resources, check out


      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

-- Set database context
USE master;

-- Declare variables
DECLARE@EmailProfile VARCHAR(255)
DECLARE@EmailRecipient VARCHAR(255)
DECLARE@EmailSubject VARCHAR(255)

-- Set variables
SET@EmailProfile = 'DBA'
SET@EmailRecipient = ''
SET@EmailSubject = 'ALERT - Hypothetical Indexes found on ' + @@SERVERNAME

-- Drop temporary table if exists
IF OBJECT_ID('tempDB.dbo.#HypotheticalIndexDropScript') IS NOT NULL
    DROP TABLE #HypotheticalIndexDropScript;
-- Create Temporary Table
CREATE TABLE #HypotheticalIndexDropScript
      DatabaseName VARCHAR(255) ,
      HypotheticalIndexDropScript VARCHAR(4000)

INSERT  INTO#HypotheticalIndexDropScript
        EXEC sp_msforeachdb 'USE [?]; SELECT  DB_NAME(DB_ID()), ''USE '' + ''['' + DB_NAME(DB_ID()) + ''];'' + '' IF  EXISTS (SELECT 1 FROM sys.indexes  AS i WHERE i.[object_id] = '' + ''object_id('' + + '''''''' + ''['' + SCHEMA_NAME(o.[schema_id]) + ''].'' + ''['' +  OBJECT_NAME(i.[object_id]) + '']'' + '''''''' + '')'' + '' AND name = '' + '''''''' + i.NAME + '''''''' + '') ''    
       + '' DROP INDEX '' + ''['' + + '']'' + '' ON '' + ''['' + SCHEMA_NAME(o.[schema_id]) + ''].'' + ''['' + OBJECT_NAME(o.[object_id]) + ''];'' AS HypotheticalIndexDropScript
FROM    sys.indexes i
        INNER JOIN sys.objects o ON o.[object_id] = i.[object_id]
WHERE is_hypothetical = 1'

-- Check for hypothetical indexes
            FROM    #HypotheticalIndexDropScript )
        SET@tableHTML = N'<style type="text/css">'
            + N'.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} '
            + N'.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} '
            + N'body {font-family: Arial, verdana;} '
            + N'table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} '
            + N'td{background-color:#F1F1F1; border:1px solid black; padding:3px;} '
            + N'th{background-color:#99CCFF; border:1px solid black; padding:3px;}'
            + N'</style>' + N'<table border="1">' + N'<tr>'
            + N'<th>DatabaseName</th>'
            + N'<th>HypotheticalIndexDropScript</th>' + N'</tr>'
            + CAST(( SELECT td =DatabaseName ,
                            '' ,
                            td = HypotheticalIndexDropScript ,
                     FROM   #HypotheticalIndexDropScript
                     XMLPATH('tr') ,
                   ) AS NVARCHAR(MAX)) + N'</table>';
            -- Email results
        EXECmsdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,
            @recipients = @EmailRecipient, @subject = @EmailSubject,
            @body =@tableHTML, @body_format = 'HTML';

DTA is not coming out to play as he has been (and will continue to be) a very naughty boy.




Leave a comment on the original post [, opens in a new window]

Loading comments...