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	Example
Indexes	_dta_index_	_dta_index_dta_mv_1_7_1150627142_K2
Statistics	_dta_stat_	_dta_stat_2041058307_2_5
Views	_dta_mv_	_dta_mv_3
Partition functions	_dta_pf_	_dta_pf_1043
Partition schemes	_dta_ps_	_dta_ps_1040

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 SQLServer365.blogspot.com

      -----------------------------------------------------------------

      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.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database context

USE master;

GO

-- Declare variables

DECLARE @EmailProfile VARCHAR(255)

DECLARE @EmailRecipient VARCHAR(255)

DECLARE @EmailSubject VARCHAR(255)

-- Set variables

SET @EmailProfile = 'DBA'

SET @EmailRecipient = 'Chris@SQLServer365.com'

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 '' + ''['' + i.name + '']'' + '' 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

IF EXISTS ( SELECT  1

            FROM    #HypotheticalIndexDropScript )

    BEGIN

        DECLARE @tableHTML NVARCHAR(MAX);

        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

                   FOR

                     XML PATH('tr') ,

                         TYPE

                   ) AS NVARCHAR(MAX)) + N'</table>';

          

            -- Email results

        EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,

            @recipients = @EmailRecipient, @subject = @EmailSubject,

            @body = @tableHTML, @body_format = 'HTML';

    END

    GO

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

Enjoy!

Chris