How to find unused Stored Procedures in SQL Server 2005/2008
-
Comments posted to this topic are about the item How to find unused Stored Procedures in SQL Server 2005/2008
-
Nice Article, easy to understand.
Raghunath
-
But what happens with the internal calls that a store procedure could make? For example, if sp_a calls sp_b, and sp_b is never called directly, you probably think that sp_b could be deleted
-
Carlos Sacristan (4/15/2010)
But what happens with the internal calls that a store procedure could make? For example, if sp_a calls sp_b, and sp_b is never called directly, you probably think that sp_b could be deletedSp_b should have its own line, as it of course will have an SP_starting event to log.
Personally I will be more afraid of missing some of the rarely used SPs. If they are named in a sane way, the name might tell you something. When I am in doubt, I rename the SP to something like SP_name_old and wait for something to break. If nothing breaks within a year or so, it should be safe to delete.
Of course we all have backups and version control, so you could just delete the buggers and restore from CVS when something breaks.
-
kll (4/15/2010)
Carlos Sacristan (4/15/2010)
But what happens with the internal calls that a store procedure could make? For example, if sp_a calls sp_b, and sp_b is never called directly, you probably think that sp_b could be deletedSp_b should have its own line, as it of course will have an SP_starting event to log.
Personally I will be more afraid of missing some of the rarely used SPs. If they are named in a sane way, the name might tell you something. When I am in doubt, I rename the SP to something like SP_name_old and wait for something to break. If nothing breaks within a year or so, it should be safe to delete.
Of course we all have backups and version control, so you could just delete the buggers and restore from CVS when something breaks.
That's true, I'm confused with rpc:completed and sql:batchcompleted, wich I usually use. Thanks
-
Hi Interesting aticle.I think the real problem is rarely used or just referenced procs. Eg a report run once a year - by the company MD summing up the previous years' perfomance - or possibly the monthly equivalent once again run by the CEO with a notoriously short fuse. I suspect that running profiler for a month would produce a file large enough to break Excel. Just a thought from an agency and temp staffing environment http://www.ava.co.uk
-
Fantastic....
-
Good article and instruction...however
I would move that warning you put on the "bottom" of the article to the "top" of the article. Or at best make it read lettering or something, and reference it throughout the article.
I would also add a warning note or something around the area where you mentioned you did a test run for 30 minutes (which I really don't see how you could pick such a short time on a database you just acquired, but "to each his own") stating that circumstances should dictate how long you let this process run.
Like a few others that have mentioned in the this forum, I may take a day or two of server-side traces (better than running profiler) to capture unused SPs but will not immediately delete them. I would take note of the ones captured and possibly create a trigger or a trace to specifically look for this list, something to let it capture how often they are run for a year or so. Then evaluate if they are needed. Unless you are 100% familiar with what that database is doing, you don't necessarily know if that one procedure is used strictly by the manufacture or vendor for patching purposes.
Still a good article though.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
I read this article quickly and as soon as I saw you using the Profiler I stopped reading thinking this isn't the easiest way for sure... there must be a DMV that exists...
So after some research I came up with the following to find the unused stored procedures... take it for what it is worth:
Select *
from sysobjects S
left join sys.dm_exec_procedure_stats X on X.object_id = S.id
where S.xtype in ('P' ,'X')
and X.object_id is null
Enjoy.
JG
-
Great article but in large environments you dont get to run/profile all stored procs at one shot(many times the application will not even call some procs depending on how people are using it, or testing it). We typically go by below DMV based query - it has some limitations, only gives what is in cache since last reboot, but depending on your environment it might work well. We got it from Greg Larsen's article
--Stored procedure usage since last reboot
SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,max(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
order by max(cp.usecounts)
-
Great article. I found it help full.
Wish something similar was there for views.
-
Thanks for the interesting Article!
I wonder did you ever take a look at this blog post about using DMVs to analyse store proc usage ?
Five DMV Queries That Will Make You A Superhero![/url]
The first example in that post shows how to query the dmvs for a list of the most frequently used stored procs.... it seems quite interesting.
Edit: corrected url
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0); - Forum Etiquette: How to post Reporting Services problems [/url]
- Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden [/url]
- How to Post Performance Problems - by Gail Shaw [/url]
I, as some of the other posts here have suggested, would go for repeated calls to the DMV over a long period of time (a few weeks at the least to find SP's called once weekly or once monthly).
If you are going to use trace then at many sites the RPC:Starting event should also be included as well as the SP:starting event you chose.
Nice article!
Brett Hawton
Idera Product Architect
I was excited when I saw the title of the article, I thought perhaps SQL Server was keeping it's own data on when a stored procedure was last invoked, buried deep in the system area. In my case this wouldn't be easy to implement as we have several databases at my company which may use each other's stored procedures, so running one application won't do it.
What I have done in the past is a bit tedious, involving the source code for all the applications we have and all the reports. My stored procedure naming conventions make it easy to identify these objects, so I just have a "spider" program scanning everything and spitting out the name of the stored procedures. Then, everything that is not in the result list gets renamed temporarily with a chosen prefix until it is determined that indeed, it is no longer being used. Then it is deleted.
Your solution would work well for small applications but in a highly complex database environment it could be tricky.
I would rather rely on the DMVs to track proc usage. A trace for 30 minutes would be inadequate in every environment I have been in. We also need to throw out some caution concerning objects that only get used once a month or once a year.
Granted the DMVs only contain information since last reboot - that would cover a lot more ground and have less holes in regard to finding unused procs. I could see using a query against the DMVs in a view that you could query from excel - that would be highly helpful.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply