Blog Post

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
                     XMLPATH('tr') ,
                         TYPE
                   ) AS NVARCHAR(MAX)) + N'</table>';
          
            -- Email results
        EXECmsdb.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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating