Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to find unused Stored Procedures in SQL Server 2005/2008 Expand / Collapse
Author
Message
Posted Wednesday, April 14, 2010 10:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 07, 2011 5:02 AM
Points: 2, Visits: 10
Comments posted to this topic are about the item How to find unused Stored Procedures in SQL Server 2005/2008
Post #903742
Posted Thursday, April 15, 2010 1:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 12:53 AM
Points: 51, Visits: 11
Nice Article, easy to understand.
Raghunath
Post #903818
Posted Thursday, April 15, 2010 2:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:32 AM
Points: 5, Visits: 292
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
Post #903829
Posted Thursday, April 15, 2010 2:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 02, 2012 7:53 AM
Points: 10, 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.
Post #903835
Posted Thursday, April 15, 2010 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:32 AM
Points: 5, Visits: 292
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
Post #903837
Posted Thursday, April 15, 2010 2:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 24, 2013 9:40 AM
Points: 9, 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


Post #903842
Posted Thursday, April 15, 2010 2:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 06, 2014 1:47 AM
Points: 292, Visits: 1,084
Fantastic....
Post #903852
Posted Thursday, April 15, 2010 7:09 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 794, Visits: 2,091
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
PS C:\>(Find-Me).TwitterUri
@wshawnmelton
PS C:\>(Find-Me).BlogUri
meltondba.wordpress.com
Post #903985
Posted Thursday, April 15, 2010 8:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:16 AM
Points: 434, Visits: 202
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
Post #904046
Posted Thursday, April 15, 2010 8:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:06 PM
Points: 312, Visits: 1,026
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)
Post #904053
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse