Find Missing Indexes in Stored Procs with T-SQL

/* Create a stored procedure skeleton */
If ObjectProperty(Object_ID('dbo.dba_missingIndexStoredProc_sp'), N'IsProcedure') Is Null
Begin
    Execute ('Create Procedure dbo.dba_missingIndexStoredProc_sp As Print ''Hello World!''')
    RaisError('Procedure dba_missingIndexStoredProc_sp created.', 10, 1);
End;
Go
 
/* Drop our table if it already exists */
If Exists(Select Object_ID From sys.tables Where [name] = N'dba_missingIndexStoredProc')
Begin
    Drop Table dbo.dba_missingIndexStoredProc
    Print 'dba_missingIndexStoredProc table dropped!';
End
 
/* Create our table */
Create Table dbo.dba_missingIndexStoredProc
(
      missingIndexSP_id int Identity(1,1)   Not Null
    , databaseName      varchar(128)        Not Null
    , databaseID        int                 Not Null
    , objectName        varchar(128)        Not Null
    , objectID          int                 Not Null
    , query_plan        xml                 Not Null
    , executionDate     smalldatetime       Not Null
 
    Constraint PK_missingIndexStoredProc
        Primary Key Clustered(missingIndexSP_id)
);
 
Print 'dba_missingIndexStoredProc Table Created';
 
/* Configure our settings */
Set ANSI_Nulls On;
Set Quoted_Identifier On;
Go
 
Alter Procedure dbo.dba_missingIndexStoredProc_sp
 
        /* Declare Parameters */
            @lastExecuted_inDays    int = 7
          , @minExecutionCount      int = 7
          , @logResults             bit = 1
          , @displayResults         bit = 0
 
As
/*********************************************************************************
    Name:       dba_missingIndexStoredProc_sp
 
    Author:     Michelle Ufford, http://sqlfool.com
 
    Purpose:    Retrieves stored procedures with missing indexes in their
                cached query plans.
 
                @lastExecuted_inDays = number of days old the cached query plan
                                       can be to still appear in the results;
                                       the HIGHER the number, the longer the
                                       execution time.
 
                @minExecutionCount = minimum number of executions the cached
                                     query plan can have to still appear 
                                     in the results; the LOWER the number,
                                     the longer the execution time.
 
                @logResults = store results in dba_missingIndexStoredProc
 
                @displayResults = return results to the caller
 
    Notes:      This is not 100% guaranteed to catch all missing indexes in
                a stored procedure.  It will only catch it if the stored proc's
                query plan is still in cache.  Run regularly to help minimize
                the chance of missing a proc.
 
    Called by:  DBA and/or SQL Agent Job
 
    Date        User    Description
    ----------------------------------------------------------------------------
    2009-03-02  MFU     Initial Release for public consumption
*********************************************************************************
    Exec dbo.dba_missingIndexStoredProc_sp
          @lastExecuted_inDays  = 30
        , @minExecutionCount    = 5
        , @logResults           = 1
        , @displayResults       = 1;
*********************************************************************************/
 
Set NoCount On;
Set XACT_Abort On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set Numeric_RoundAbort Off;
 
Begin
 
    /* Declare Variables */
    Declare @currentDateTime smalldatetime;
 
    Set @currentDateTime = GetDate();
 
    Declare @plan_handles Table
    (
        plan_handle     varbinary(64)   Not Null
    );
 
    Create Table #missingIndexes
    (
          databaseID    int             Not Null
        , objectID      int             Not Null
        , query_plan    xml             Not Null
 
        Constraint PK_temp_missingIndexes Primary Key Clustered
        (
            databaseID, objectID
        )
    );
 
    Begin Try
 
        /* Perform some data validation */
        If @logResults = 0 And @displayResults = 0
        Begin
 
            /* Log the fact that there were open transactions */
            Execute dbo.dba_logError_sp
                  @errorType            = 'app'
                , @app_errorProcedure   = 'dba_missingIndexStoredProc_sp'
                , @app_errorMessage     = '@logResults = 0 and @displayResults = 0; no action taken, exiting stored proc.'
                , @forceExit            = 1
                , @returnError          = 1;  
 
        End;
 
        Begin Transaction;
 
        /* Retrieve distinct plan handles to minimize dm_exec_query_plan lookups */
        Insert Into @plan_handles
        Select Distinct plan_handle
        From sys.dm_exec_query_stats
        Where last_execution_time > DateAdd(day, -@lastExecuted_inDays, @currentDateTime)
            And execution_count > @minExecutionCount;
 
        With xmlNameSpaces (
            Default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
        )
 
        /* Retrieve our query plan's XML if there's a missing index */
        Insert Into #missingIndexes
        Select deqp.[dbid]
            , deqp.objectid
            , deqp.query_plan 
        From @plan_handles As ph
        Cross Apply sys.dm_exec_query_plan(ph.plan_handle) As deqp 
        Where deqp.query_plan.exist('//MissingIndex') = 1
            And deqp.objectid Is Not Null;
 
        /* Do we want to store the results of our process? */
        If @logResults = 1
        Begin
            Insert Into dbo.dba_missingIndexStoredProc
            Execute sp_msForEachDB 'Use ?; 
                                    Select ''?''
                                        , mi.databaseID
                                        , Object_Name(o.object_id)
                                        , o.object_id
                                        , mi.query_plan
                                        , GetDate()
                                    From sys.objects As o 
                                    Join #missingIndexes As mi 
                                        On o.object_id = mi.objectID 
                                    Where databaseID = DB_ID();';
 
        End
        /* We're not logging it, so let's display it */
        Else
        Begin
            Execute sp_msForEachDB 'Use ?; 
                                    Select ''?''
                                        , mi.databaseID
                                        , Object_Name(o.object_id)
                                        , o.object_id
                                        , mi.query_plan
                                        , GetDate()
                                    From sys.objects As o 
                                    Join #missingIndexes As mi 
                                        On o.object_id = mi.objectID 
                                    Where databaseID = DB_ID();';
        End;
 
        /* See above; this part will only work if we've 
           logged our data. */
        If @displayResults = 1 And @logResults = 1
        Begin
            Select *
            From dbo.dba_missingIndexStoredProc
            Where executionDate >= @currentDateTime;
        End;
 
        /* If you have an open transaction, commit it */
        If @@TranCount > 0
            Commit Transaction;
 
    End Try
    Begin Catch
 
        /* Whoops, there was an error... rollback! */
        If @@TranCount > 0
            Rollback Transaction;
 
        /* Return an error message and log it */
        Execute dbo.dba_logError_sp;
 
    End Catch;
 
    /* Clean-Up! */
    Drop Table #missingIndexes;
 
    Set NoCount Off;
    Return 0;
End
Go

/* Create a stored procedure skeleton */ If ObjectProperty(Object_ID('dbo.dba_missingIndexStoredProc_sp'), N'IsProcedure') Is Null Begin Execute ('Create Procedure dbo.dba_missingIndexStoredProc_sp As Print ''Hello World!''') RaisError('Procedure dba_missingIndexStoredProc_sp created.', 10, 1); End; Go /* Drop our table if it already exists */ If Exists(Select Object_ID From sys.tables Where [name] = N'dba_missingIndexStoredProc') Begin Drop Table dbo.dba_missingIndexStoredProc Print 'dba_missingIndexStoredProc table dropped!'; End /* Create our table */ Create Table dbo.dba_missingIndexStoredProc ( missingIndexSP_id int Identity(1,1) Not Null , databaseName varchar(128) Not Null , databaseID int Not Null , objectName varchar(128) Not Null , objectID int Not Null , query_plan xml Not Null , executionDate smalldatetime Not Null Constraint PK_missingIndexStoredProc Primary Key Clustered(missingIndexSP_id) ); Print 'dba_missingIndexStoredProc Table Created'; /* Configure our settings */ Set ANSI_Nulls On; Set Quoted_Identifier On; Go Alter Procedure dbo.dba_missingIndexStoredProc_sp /* Declare Parameters */ @lastExecuted_inDays int = 7 , @minExecutionCount int = 7 , @logResults bit = 1 , @displayResults bit = 0 As /********************************************************************************* Name: dba_missingIndexStoredProc_sp Author: Michelle Ufford, http://sqlfool.com Purpose: Retrieves stored procedures with missing indexes in their cached query plans. @lastExecuted_inDays = number of days old the cached query plan can be to still appear in the results; the HIGHER the number, the longer the execution time. @minExecutionCount = minimum number of executions the cached query plan can have to still appear in the results; the LOWER the number, the longer the execution time. @logResults = store results in dba_missingIndexStoredProc @displayResults = return results to the caller Notes: This is not 100% guaranteed to catch all missing indexes in a stored procedure. It will only catch it if the stored proc's query plan is still in cache. Run regularly to help minimize the chance of missing a proc. Called by: DBA and/or SQL Agent Job Date User Description ---------------------------------------------------------------------------- 2009-03-02 MFU Initial Release for public consumption ********************************************************************************* Exec dbo.dba_missingIndexStoredProc_sp @lastExecuted_inDays = 30 , @minExecutionCount = 5 , @logResults = 1 , @displayResults = 1; *********************************************************************************/ Set NoCount On; Set XACT_Abort On; Set Ansi_Padding On; Set Ansi_Warnings On; Set ArithAbort On; Set Concat_Null_Yields_Null On; Set Numeric_RoundAbort Off; Begin /* Declare Variables */ Declare @currentDateTime smalldatetime; Set @currentDateTime = GetDate(); Declare @plan_handles Table ( plan_handle varbinary(64) Not Null ); Create Table #missingIndexes ( databaseID int Not Null , objectID int Not Null , query_plan xml Not Null Constraint PK_temp_missingIndexes Primary Key Clustered ( databaseID, objectID ) ); Begin Try /* Perform some data validation */ If @logResults = 0 And @displayResults = 0 Begin /* Log the fact that there were open transactions */ Execute dbo.dba_logError_sp @errorType = 'app' , @app_errorProcedure = 'dba_missingIndexStoredProc_sp' , @app_errorMessage = '@logResults = 0 and @displayResults = 0; no action taken, exiting stored proc.' , @forceExit = 1 , @returnError = 1; End; Begin Transaction; /* Retrieve distinct plan handles to minimize dm_exec_query_plan lookups */ Insert Into @plan_handles Select Distinct plan_handle From sys.dm_exec_query_stats Where last_execution_time > DateAdd(day, -@lastExecuted_inDays, @currentDateTime) And execution_count > @minExecutionCount; With xmlNameSpaces ( Default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' ) /* Retrieve our query plan's XML if there's a missing index */ Insert Into #missingIndexes Select deqp.[dbid] , deqp.objectid , deqp.query_plan From @plan_handles As ph Cross Apply sys.dm_exec_query_plan(ph.plan_handle) As deqp Where deqp.query_plan.exist('//MissingIndex') = 1 And deqp.objectid Is Not Null; /* Do we want to store the results of our process? */ If @logResults = 1 Begin Insert Into dbo.dba_missingIndexStoredProc Execute sp_msForEachDB 'Use ?; Select ''?'' , mi.databaseID , Object_Name(o.object_id) , o.object_id , mi.query_plan , GetDate() From sys.objects As o Join #missingIndexes As mi On o.object_id = mi.objectID Where databaseID = DB_ID();'; End /* We're not logging it, so let's display it */ Else Begin Execute sp_msForEachDB 'Use ?; Select ''?'' , mi.databaseID , Object_Name(o.object_id) , o.object_id , mi.query_plan , GetDate() From sys.objects As o Join #missingIndexes As mi On o.object_id = mi.objectID Where databaseID = DB_ID();'; End; /* See above; this part will only work if we've logged our data. */ If @displayResults = 1 And @logResults = 1 Begin Select * From dbo.dba_missingIndexStoredProc Where executionDate >= @currentDateTime; End; /* If you have an open transaction, commit it */ If @@TranCount > 0 Commit Transaction; End Try Begin Catch /* Whoops, there was an error... rollback! */ If @@TranCount > 0 Rollback Transaction; /* Return an error message and log it */ Execute dbo.dba_logError_sp; End Catch; /* Clean-Up! */ Drop Table #missingIndexes; Set NoCount Off; Return 0; End Go