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 Sunday, April 18, 2010 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 17, 2010 3:26 AM
Points: 1, Visits: 5
nice article but don't you think, creating a new temp table and simply inserting store proc name and time it has been used will do the job. we might have to run a trigger after every query in the database but running this for one day and putting db back to normal next day will do the job and I believe, getting data for 24 hrs testing of normal running of application will do much better than job than running it for 30 min although i don't mind 30 min too.
Post #905577
Posted Sunday, April 18, 2010 11:34 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
Thanks for feedback, Mister.Magoo! I didn't look at that particular article before posting but I'll put it on my todo list and review it soon! :)
Post #905698
Posted Sunday, April 18, 2010 11:41 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
Thank you all for great feedback! As this was my first article I haven't refined it nearly good enough. Nor have I done sufficient research into alternative methods of solving the case at hand.

I will absolutely rewrite the last part of the article to underline the danger of deleting stored procedures that seems to be unused. I do believe we can all agree on that this article provides some help for small-to-midsize applications but that it doesn't really work well on largescale applications.

Again, thanks for feedback! :)
Post #905702
Posted Monday, April 19, 2010 1:03 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 15, 2013 9:03 AM
Points: 55, Visits: 355
dma-669038 (4/15/2010)
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)


I tried this but received the following error when I ran it:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.


Which is referencing this line: CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

I'm still debugging it but thought I'd throw this out there.


======================================================
John
SQL Server 200x Newb and proud of it!
======================================================
Post #906289
Posted Tuesday, April 20, 2010 12:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 09, 2012 11:49 AM
Points: 31, Visits: 89
John Waclawski (4/19/2010)
dma-669038 (4/15/2010)
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)


I tried this but received the following error when I ran it:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.


Which is referencing this line: CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

I'm still debugging it but thought I'd throw this out there.


Try changing the compatibility Level on your database. I changed it on one of mine and it got rid of the syntax error.
Post #907158
Posted Thursday, January 19, 2012 9:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 03, 2014 4:06 PM
Points: 15, Visits: 282
Checked out the link that dma-669038 posted and there is some good stuff at the site.

Had to use this today as I am reverse engineering a DB and found this absolutely brilliant.
Also thanks to Mr G Larsen for the article and the code.
Post #1239197
Posted Friday, June 15, 2012 2:59 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 03, 2014 2:47 AM
Points: 984, Visits: 55
Is there a similar way to retreive the unused views / functions?
Post #1316398
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse