June 16, 2015 at 1:59 pm
I need to find any stored procedures that have not been used over a certain time period.
I have set up an extended events session to gather sp_statement_starting and _completed.
The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.
Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?
Or if anybody has done this process of finding unused sp's and can offer an alternative method then please let me know !
June 17, 2015 at 4:57 am
PearlJammer1 (6/16/2015)
I need to find any stored procedures that have not been used over a certain time period.I have set up an extended events session to gather sp_statement_starting and _completed.
The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.
Do I have to repeat this process for every different object_id or is there a way I can query the trace results, using the object_id as my search criteria as one query ?
Or if anybody has done this process of finding unused sp's and can offer an alternative method then please let me know !
Hi
I usually use this query on the individual DBs:
SELECTsc.name as 'schema',
p.name as 'sp_name',
p.type_desc,
st.last_execution_time,
st.execution_count
FROM sys.procedures AS p INNER JOIN sys.schemas AS sc
ON p.[schema_id] = sc.[schema_id]
LEFT OUTER JOIN sys.dm_exec_procedure_stats AS st
ON p.[object_id] = st.[object_id]
ORDER BY st.last_execution_time DESC, p.name;
This will tell you if a stored procedure in your DB had been run since the last server restart (or freeproccache)
Here is the original article:
http://stackoverflow.com/questions/10421439/tsql-query-to-find-un-used-stored-procedures
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
June 17, 2015 at 5:07 am
JimmyJones (6/17/2015)
This will tell you if a stored procedure in your DB had been run since the last server restart (or freeproccache)
Or other event which removes plans from cache, or plans got aged out of cache, or....
The query stats/procedure stats are in no way reliable ways to tell if something has been run.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2015 at 5:08 am
PearlJammer1 (6/16/2015)
The trace returns the object_id's of many stored procedures. I then query the sys.procedures and plug in the object_id to return the stored proc name.
Why don't you join the extended events result to sys.objects?
You'll have to do it per database, but it'll be faster than per object.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 17, 2015 at 5:22 am
GilaMonster (6/17/2015)
JimmyJones (6/17/2015)
This will tell you if a stored procedure in your DB had been run since the last server restart (or freeproccache)Or other event which removes plans from cache, or plans got aged out of cache, or....
The query stats/procedure stats are in no way reliable ways to tell if something has been run.
Thanks Gail, you are absolutely right, I worded my sentence poorly about the plan cache.
Do you have any 'foolproof' method to check the stored proc usage?
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
June 17, 2015 at 5:34 am
good script..thanks very much 🙂
June 17, 2015 at 5:43 am
PearlJammer1 (6/17/2015)
good script..thanks very much 🙂
Please note Gail's comment.
It is absolutely correct, that script may not be the most accurate as your cache can change rapidly.
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
June 17, 2015 at 9:31 am
yeah I have made a note of that comment from Gail and tried something different.
I found this approach to be quite helpful
http://www.sqlservercentral.com/articles/Administration/2582/
June 17, 2015 at 9:54 am
PearlJammer1 (6/17/2015)
I found this approach to be quite helpful
http://www.sqlservercentral.com/articles/Administration/2582/%5B/quote%5D
Make it easier for others to follow: http://www.sqlservercentral.com/articles/Administration/2582/
June 17, 2015 at 2:28 pm
JimmyJones (6/17/2015)
Do you have any 'foolproof' method to check the stored proc usage?
Yup. Extended events run for a full business cycle (usually a year). Use the bucketiser and group on the object_id and database_id, that should reduce the data volumes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2015 at 12:30 am
GilaMonster (6/17/2015)
Yup. Extended events run for a full business cycle (usually a year). Use the bucketiser and group on the object_id and database_id, that should reduce the data volumes.
Thank you!
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply