Find Unused Store Porcedures

Question

Find Unused Store Porcedures

Old Hand

Points: 358

March 5, 2020 at 3:46 pm

I am trying to use the script to get unused Stored Procedure reports but having confusion as which one is the best approach as to look into cache or recompilation.

I don't want to run the script in each database and do the analysis.

Is it any way i can include in a one script and it's also gives me Database name, last access, scan or look up or not in cache to make easy for analysis.

Any Idea?

I tried this one but i have to run for each database:

WITH UnUsed (id)
AS
(
SELECT s.object_id
FROM sys.procedures AS s
EXCEPT
SELECT dm.object_id
FROM sys.dm_exec_procedure_stats AS dm
)
SELECT s.name, s.type_desc
FROM UnUsed
JOIN sys.procedures s ON unused.id = s.object_id

I also tried this one with the counts but not sure to make decision:

SELECT
DatabaseName = DB_NAME(st.dbid)
,SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid)
,StoredProcedure = OBJECT_NAME(st.objectid,dbid)
,ExecutionCount = MAX(cp.usecounts)
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'
AND DB_NAME(st.dbid) NOT IN ('tempdb', 'msdb', 'master', 'model')
GROUP BY
cp.plan_handle
,DB_NAME(st.dbid)
,OBJECT_SCHEMA_NAME(objectid,st.dbid)
,OBJECT_NAME(objectid,st.dbid)

Anyone has better solution really appreciate it?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

Cache is by database, not by instance. You need to run this in each db. You can script that, but i'd include the name of your db in the result set and store the data in a single table somewhere. Then you can analyze it. If you include the date you run the script, you can easily then trim out old executions of this and limit searches to the current set of results

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

Cache is also temporary... very temporary. Dropping procs based on what is in cache is a sure fire way to need to resurrect your resume and sharpen it up. Seriously... I'm not screwing around here... this is a form of "Death by SQL".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pdsqsql Old Hand Points: 358 More actions · Answer 3

Thanks Steve.

I am using different script to find out which procedure have the latest cache date and converting into Stored proc and during that time i am loading into table with the load date.

pdsqsql Old Hand Points: 358 More actions · Answer 4

Thanks Jeff.

I am totally agreed with you as it's temporary but any suggestions which i can follow so we don't have to carry unused stored proc into migration process.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

Sure... just like I said over on SQLTeam.com...

To be honest, there is no completely reliable method to ensure that you don't delete stored procedures that are being used.. Consider the fact that some stored procedures may be used only monthly, quarterly, or even only yearly.
About the best way to ensure that you don't delete something that you need is to append the stored procedure names with DeleteOn_yyyymmdd where yyyymmdd is a date a year into the future and prepend them with "zzz_" to kinda of move them out of the way in the SQL Explorer window and to make it apparent that you don't think they're being used and will be deleted.
I also do similar with view, functions, tables. The programmatic stuff doesn't take much room but tables can be a pain. I make a scratch and arch schema on each database and tell folks that for tables they only need for a week, to create them in the scratch schema and for things they'll need for 13 months, put those in the arch schema. I wrote an automatic "rake" that will rename to identify they're going to be deleted after a week and after 13 months respectively and, once renamed, wait for a week and then drop them unceremoniously.

If you absolutely need to get them out of your database, start a small ToBeDeleted database and put them there along with the renaming as before.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 6

I agree with Jeff, and that's what I've done. Usually removing stuff is a year+ duration. I rename them, have a date so that I can programmatically remove them, and then see if someone raises a ticket.

pdsqsql Old Hand Points: 358 More actions · Answer 7

Thanks for your views and suggestions and completely agreed with both of you!