<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Ronny Dahl
»
How to find unused Stored Procedures in SQL...
27 posts, Page 1 of 3
1
2
3
»
»»
How to find unused Stored Procedures in SQL Server 2005/2008
Rate Topic
Display Mode
Topic Options
Author
Message
ronnydahl
ronnydahl
Posted Wednesday, April 14, 2010 10:30 PM
Forum 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
raghu.gr
raghu.gr
Posted Thursday, April 15, 2010 1:45 AM
Valued 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
Carlos Sacristan
Carlos Sacristan
Posted Thursday, April 15, 2010 2:02 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, April 26, 2013 2:57 AM
Points: 5,
Visits: 249
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
kll
kll
Posted Thursday, April 15, 2010 2:11 AM
Grasshopper
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
Carlos Sacristan
Carlos Sacristan
Posted Thursday, April 15, 2010 2:22 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, April 26, 2013 2:57 AM
Points: 5,
Visits: 249
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
ianp
ianp
Posted Thursday, April 15, 2010 2:34 AM
Forum Newbie
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 10:51 AM
Points: 9,
Visits: 40
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
Satnam Singh
Satnam Singh
Posted Thursday, April 15, 2010 2:59 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, April 05, 2013 3:42 AM
Points: 291,
Visits: 1,064
Fantastic....
Post #903852
Shawn Melton
Shawn Melton
Posted Thursday, April 15, 2010 7:09 AM
Right there with Babe
Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:56 PM
Points: 754,
Visits: 1,891
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
@wshawnmelton
Post #903985
jghali
jghali
Posted Thursday, April 15, 2010 8:02 AM
SSC-Addicted
Group: General Forum Members
Last Login: Friday, November 09, 2012 12:51 AM
Points: 429,
Visits: 187
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
dma-669038
dma-669038
Posted Thursday, April 15, 2010 8:08 AM
Old Hand
Group: General Forum Members
Last Login: Monday, November 12, 2012 6:46 AM
Points: 312,
Visits: 1,024
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 »
27 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
