Dropped Index during Database Engine Tuning Advisor

I was analyzing a query using “Database Engine Tuning Advisor”. During this time the database started to get blocking, long running queries and ended by restarted the instance. When I checked after that I found a few drop index commands had been executed.

drop index [dbo].[Profile].[_dta_index_Profile_7_1563152614__K15_K1]

Why did this happen? The query I was tuning was not on profile table!
How can I check if this index was already in the system before or was created during the analysis?

The indexes that were created and dropped by DTA were most likely hypothetical indexes – ones that DTA creates while running to perform its analysis and should (but doesn’t always) delete once done.

These hypothetical indexes can be created even if you weren’t looking at tuning that specific table! I’d recommend you query some of the system DMVs directly to find out about missing indexes. This avoids the pesky behavior of DTA and provides some more granular insights into your index problems.

You can use the below query from SQL Server Central to detect any remaining hypothetical indexes from DTA that should be removed.

/*
      -----------------------------------------------------------------
      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