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
Pingback: SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA
Pingback: A Look at Missing Indexes : SQL Fool
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
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.
Pingback: The Rambling DBA: Jonathan Kehayias
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.
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
Thanks again Michelle for your awesome posts. You just saved me a lot of time. Again!!
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.
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).
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.
I have the same issue that MyDogJessie does.
Pingback: Indexes: just because you can, doesn’t mean you should! | Kimberly L. Tripp
Pingback: Digging into the SQL Plan Cache: Finding Missing Indexes - Jonathan Kehayias
Why not just use:
object_name(objectid, dbid)
?
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’.
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()