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
