Início / dba / Perguntas / 108332
Accepted
sebeid
Asked: 2015-07-29 06:19:15 +0800 CST

Índice perdido durante o Orientador de Otimização do Mecanismo de Banco de Dados

  • 772

Eu estava analisando uma consulta usando o "Database Engine Tuning Advisor". Durante esse tempo, o banco de dados começou a receber bloqueios, consultas de execução longa e acabou reiniciando a instância. Quando verifiquei depois disso, descobri que alguns drop indexcomandos foram executados.

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

Por quê isso aconteceu? A consulta que eu estava ajustando não estava na tabela de perfil! Como posso verificar se esse índice já estava no sistema antes ou foi criado durante a análise?

sql-server index

1 respostas

  1. Best Answer

    Os índices que foram criados e descartados pelo DTA eram provavelmente índices hipotéticos - aqueles que o DTA cria durante a execução para executar sua análise e deve (mas nem sempre) excluir uma vez feito.

    Esses índices hipotéticos podem ser criados mesmo se você não estiver procurando ajustar essa tabela específica! Eu recomendo que você consulte alguns dos DMVs do sistema diretamente para descobrir os índices ausentes. Isso evita o comportamento irritante do DTA e fornece alguns insights mais granulares sobre seus problemas de índice.

    Você pode usar a consulta abaixo do SQL Server Central para detectar quaisquer índices hipotéticos restantes do DTA que devem ser removidos.

    /*
      -----------------------------------------------------------------
      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 = '[email protected]'
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
    • 8

relate perguntas