Introduction
From time to time I get asked to check if a stored procedure is still used in preparation for dropping it. Last week I tweeted my wish for a DMV that would give me the usage statistics for stored procedures to which Aaron Bertrand (Blog|Twitter) quickly replied that SQL 2008 has sys.dm_exec_procedure_stats that does exactly what I need. Unfortunately the server I needed to do the analysis on was running SQL 2005.
Where It Went So Wrong
Rather than digging into my script library to see if I had anything that might help do the analysis I fell into classic tunnel vision, cursing myself for not pushing harder to get this application on SQL 2008. I decided that the only way to figure out if these stored procedures were still used was to fire up Profiler and trace for the stored procedure names. I was not looking forward to doing this because it is easy to get a trace wrong, not capturing any data or bogging down the server, but I pushed on. I built my server-side trace script, trying it out on a less used server then fired it up on my production server. The production server immediately went to 100% CPU, completely killing all applications that depended on it. The only way to get the get the applications back online was to restart the SQL Service. This was absolutely the worst I have ever been burned by Profiler.
A Better Way
Last night I decided I was done replaying the events of the day over and over in my head and threw out what had happened to #sqlhelp on Twitter to get advice on what I could have done better. The basic feedback was try not to run traces on multiple strings and if you do have to then try multiple traces. There really was not magic tipping point that anybody could point to as to why doing something in one place would have such disastrous results while doing it somewhere else would not. This really points to what I have known for a long time: Profiler is not your friend. Profiler is more like that former coworker that you could not trust but had to work with on some of your most important projects.
Talking to Andrew Kelly (Blog|Twitter) led to one of my more pronounced face-palm moments, ever. He pointed out that “2005 has similar dmv’s but if its a seldom run query the plan may not be in cache most of the time.” In iteration 1 of the same project I ran into less pronounced issues tracing stored procedures to make sure they were no longer used. At that time I wrote a script to check whether or not an object had a plan in the cache or not. That very script was sitting out in my common scripts directory the whole time. Ultimate Face-Palm.
The Script
Here is the script I ultimately used to figure out if the stored procedures were used. Pulling back the plan is optional but I find it helpful because it will tell you what parameters an object was compiled with. This can often point to where it was called from or even who the user is that called it. Careful with this query if you choose to run it on a production server you may see a performance impact.
Note that I explicitly specify the database name rather than rely on the USE statement. When the absence of results is what you are looking for it is too easy to confuse a mistake for success.
Updated to correct bug caused by improper usage of OBJECT_ID, pointed out by Simon Sabin (blog). I opted to keep the CROSS APPLY rather than going with an OUTER APPLY because I only want to return results for plans that are cached. The OBJECT_ID syntax is taken directly from here: http://msdn.microsoft.com/en-us/library/ms190328.aspx. I could not make it work by passing DB_ID as a parameter.
1 2 3 4 5 6 7 | SELECT q.text, p.query_plan, * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) q CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) p WHERE q.dbid = DB_ID('[ database_name ]') AND q.objectid = OBJECT_ID('[ database_name . [ schema_name ] . | schema_name . ] object_name ]') |
Conclusion
I learned a lot of tough lessons on this one. I suppose even tougher because they were lessons I already should have learned. Hopefully this blog and the included script will prevent someone else from falling into the same trap. Please let me know if you run into any issues with the script or if it bails you out of any jams. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.