How to find unused Stored Procedures in SQL Server 2005/2008

Question

Post reply

How to find unused Stored Procedures in SQL Server 2005/2008

ronnydahl

SSC Enthusiast

Points: 130
More actions
April 14, 2010 at 10:30 pm

#223311

Comments posted to this topic are about the item How to find unused Stored Procedures in SQL Server 2005/2008
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]

raghu.gr SSC Enthusiast Points: 141 More actions · Answer 1

raghu.gr

SSC Enthusiast

Points: 141

April 15, 2010 at 1:45 am

#1151930

Nice Article, easy to understand.

Raghunath

Carlos Sacristan SSC Enthusiast Points: 113 More actions · Answer 2

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

kll SSC Veteran Points: 250 More actions · Answer 3

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 deleted

Sp_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. 😉

Carlos Sacristan SSC Enthusiast Points: 113 More actions · Answer 4

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 deleted
Sp_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

ianp SSC Veteran Points: 284 More actions · Answer 5

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

Satnam Singh Hall of Fame Points: 3542 More actions · Answer 6

Satnam Singh

Hall of Fame

Points: 3542

April 15, 2010 at 2:59 am

#1151961

Fantastic....

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 7

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

JohnG69 Hall of Fame Points: 3114 More actions · Answer 8

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

dma-669038 SSCrazy Points: 2773 More actions · Answer 9

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

http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm

--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)

JANNIEE SSC Rookie Points: 25 More actions · Answer 10

Great article. I found it help full.

Wish something similar was there for views.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

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);

brett.hawton Valued Member Points: 74 More actions · Answer 12

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

marykdba Mr or Mrs. 500 Points: 574 More actions · Answer 13

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

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