/* Create a stored procedure skeleton */IF OBJECTPROPERTY(OBJECT_ID('dbo.dba_missingIndexStoredProc_sp'), N'IsProcedure') IS NullBEGIN    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;ENDGo