SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
ronnydahl
ronnydahl
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 12
Comments posted to this topic are about the item How to find unused Stored Procedures in SQL Server 2005/2008
raghu.gr
raghu.gr
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 11
Nice Article, easy to understand.
Raghunath
Carlos Sacristan
Carlos Sacristan
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 359
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
kll
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 53
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
Carlos Sacristan
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 359
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
ianp
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 42
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 www.ava.co.uk



Satnam Singh
Satnam Singh
Say Hey Kid
Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)Say Hey Kid (678 reputation)

Group: General Forum Members
Points: 678 Visits: 1084
Fantastic....
Shawn Melton
Shawn Melton
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4087 Visits: 3532
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: blog.wsmelton.info
JohnG69
JohnG69
SSC Eights!
SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)SSC Eights! (917 reputation)

Group: General Forum Members
Points: 917 Visits: 452
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
dma-669038
Mr or Mrs. 500
Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)Mr or Mrs. 500 (569 reputation)

Group: General Forum Members
Points: 569 Visits: 1035
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search