Find Missing Indexes in Stored Procs with T-SQL

This post should probably be called “The Power of Twitter.” I’ve said it before, I’ll say it again: I love Twitter. I mostly follow SQL Server people, so it’s become a great source of new information and help when I feel like banging my head against the wall when I get stumped.

So last week, Jonathan Kehayias (@jmkehayias) posted a link to a missing index script on the MSDN forums. Jonathan’s script is modified from something he put together for a question posed by Jeremiah Peschka (@peschkaj).

Jonathan’s script intrigued me. I’d never tried to search a query plan’s XML before, and it certainly presents some interesting possibilities. After dealing with a missing index in production a few weeks ago (caused by an index change), I thought it’d be a great idea to put a regular monitor in place.

Everything was going well until I ran into a problem where I couldn’t get the proc name returned. The results spanned numerous databases; I had the object_id and database_id, but I wanted to store the proc name instead. I tried several different methods, including sp_msforeachdb and sp_executeSQL, and while I had a working solution, it was a little more clunky than I liked. So I asked my awesome followers on Twitter for any tips and within minutes I had half a dozen responses. In the end, @MladenPrajdic solved my problem with a pretty simple solution: put ‘Use ?;’ at the start of my sp_msforeachdb statement. Thanks again, Mladen!

I’ve now had this process running on my server for a few days now, with good success. The stored procedure below will return the database name, proc name, and query plan XML for any stored proc with a missing index. This is a centralized proc that will store the results in a table for later action. If you click on the XML, you should see the actual query plan with the missing index details. Because this looks at cached query plans, your best bet is to run it fairly regularly (maybe daily) to increase your chances of catching any problem procs.

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

Not know what “EXECUTE dbo.dba_logError_sp;” is doing? Check out my blog post on Error Handling in T-SQL.

HTH!

Michelle

Source: http://sqlfool.com/2009/03/find-missing-indexes/

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , , . Bookmark the permalink.

17 Responses to Find Missing Indexes in Stored Procs with T-SQL

  1. Pingback: SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA

  2. Pingback: A Look at Missing Indexes : SQL Fool

  3. Tom Groszko says:

    I had not seen, at least not noticed, the missing index comment before in query plans. What other kinds of things are there in these plans that could just as easily be searched for?

    Thanks

  4. joe tigeleiro says:

    Is their a way to generate the missing index detail information into a create index statements just like SSMS does? It would be? I could find how SSMS parses the information.

  5. Pingback: The Rambling DBA: Jonathan Kehayias

  6. Kobe Lenjou says:

    I made a small change, you should change the two occurances of

    EXECUTE sp_msForEachDB ‘Use ?;

    to

    EXECUTE sp_msForEachDB ‘Use [?];

    for those of uw who have funky database names.

  7. Ewan says:

    Hi Michelle

    Love your blog. I read a different blog recently, and remembered your USE ? statement here. In SQL2005 SP2, the object_id function was upgraded – you can now use object_id(objid, dbid) to return the object name!

    HTH

    Ewan

  8. Montrial says:

    Thanks again Michelle for your awesome posts. You just saved me a lot of time. Again!!

  9. Roman says:

    Hi Michelle

    Looks like stor proc does not work if database name has dot.
    If db names is TechOnsite.HelpDesk then you will have error:

    Msg 50000, Level 15, State 1, Procedure dba_logError_sp, Line 152
    Database ‘TechOnsite’ does not exist. Make sure that the name is entered correctly.

  10. MyDoggieJessie says:

    Running this out of the box (as posted, and including the Loggin procedure for your other blog) I got:

    Msg 50000, Level 15, State 1, Procedure dba_LogErrorForMissingIndexes, Line 152
    Violation of PRIMARY KEY constraint ‘PK_temp_missingIndexes’. Cannot insert duplicate key in object ‘dbo.#missingIndexes’. The duplicate key value is (32767, 1003234025).

  11. Patrick says:

    Hi Michelle,
    I found a small bug with your code, the databases with dash character in it will though error.
    simple change Use ? to Use [?] and it will resolve the issue.

    Roman, try it out and see if it will fix your problem too.

  12. Greg says:

    I have the same issue that MyDogJessie does.

  13. Pingback: Indexes: just because you can, doesn’t mean you should! | Kimberly L. Tripp

  14. Pingback: Digging into the SQL Plan Cache: Finding Missing Indexes - Jonathan Kehayias

  15. SgtPUSMC says:

    Why not just use:

    object_name(objectid, dbid)

    ?

  16. marat says:

    I suggest to remove constraint
    Constraint PK_temp_missingIndexes Primary Key Clustered
    (
    databaseID, objectID
    )
    Some SPs can have multiple plans in cache.
    This will eliminate error:
    Msg 50000, Level 15, State 1, Procedure dba_LogErrorForMissingIndexes, Line 152
    Violation of PRIMARY KEY constraint ‘PK_temp_missingIndexes’. Cannot insert duplicate key in object ‘dbo.#missingIndexes’.

  17. Donald says:

    As you said you like it “a bit less clunky” :-)
    If you want to get rid of the “USE ” you can fully qualify the sys.objects by adding the database name.
    Then you also need to add the database name as parameter to DB_ID() and to OBJECT_NAME() (see SgtPUSMC reply) because otherwise they run in the current database context – and without “USE ” you do not have a context switching anymore.
    sp_msForEachDB does no context switching, it only gives you the database names.
    This is my Approach without any USE :
    Execute sp_msForEachDB
    ‘Select ”?”
    , mi.databaseID
    , Object_Name(o.object_id, DB_ID(”?”))
    , o.object_id
    , mi.query_plan
    , GetDate()
    From [?].sys.objects As o –added database name to sys.objects
    Join #missingIndexes As mi
    On o.object_id = mi.objectID
    Where databaseID = DB_ID(”?”);’; –added database name to DB_ID()

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>