Unused Procedure lists - Most easiest way

Question

Post reply

Unused Procedure lists - Most easiest way

ece.viky

Right there with Babe

Points: 727
More actions
December 9, 2014 at 9:15 am

#293380

Comments posted to this topic are about the item Unused Procedure lists - Most easiest way

Viewing 13 posts - 1 through 12 (of 12 total)

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

John Hanrahan Hall of Fame Points: 3825 More actions · Answer 1

Never needed this but did not 'really' know this existed. Thanks for the reminder.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 2

Keep in mind that procs are listed only as long as they are in the cache. On a memory-constrained or busy server, stats could be lost/removed. Also, whenever SQL Server is stopped and restarted obviously the cache gets emptied and has to start over.

That is, be careful when using this view and understand its limitations.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

R. M. Joseph Old Hand Points: 368 More actions · Answer 3

Good utility - required for clean-up activity of database. Thanks to Author

Pei Zhu-415513 SSCarpal Tunnel Points: 4053 More actions · Answer 4

technically you can not reply on it since

1. sproc's with recompile clause would not stay in cache.

2.if there is memory pressure or something(server restart, manual reset) dm_exec_procedure_stats might be cleared.

it is good to use for estimating though.

mariol 76344 Newbie Points: 7 More actions · Answer 5

Use with caution. We have plenty of procedures that are in use and they do not show up in the dm_view...

We store stored procedure usage over time and anything not in that list we would only remove if they do not reference temp tables.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 6

akljfhnlaflkj

SSC Guru

Points: 76202

May 23, 2016 at 6:52 am

#1880726

Thanks for the script.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

mariol 76344 (5/23/2016)
Use with caution. We have plenty of procedures that are in use and they do not show up in the dm_view...

Thanks for the warning.

Lee Linares SSCrazy Points: 2727 More actions · Answer 8

You can also get erroneous output if the object_id of an object exists in more than 1 database. In my testing a stored procedure was reported as having last executed 20 days ago but I had only created it last week. It turned out that a stored procedure in another database had the same object_id.

Lee

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

Lee Linares (5/23/2016)
You can also get erroneous output if the object_id of an object exists in more than 1 database. In my testing a stored procedure was reported as having last executed 20 days ago but I had only created it last week. It turned out that a stored procedure in another database had the same object_id.Lee

That's going to be a problem for us.

jonesg 74626 SSC-Addicted Points: 405 More actions · Answer 10

To Lee's point, use the following CTE that filters on the current database_id so that you aren't counting hits from object_ids from other databases:

select ISNULL(deps.database_id,-1) database_id,p.object_id,p.name

,ISNULL(deps.type,'P') type,ISNULL(deps.type_desc,'SQL_STORED_PROCEDURE') type_desc

,ISNULL(deps.last_execution_time,'1900-01-01') last_execution_time,ISNULL(deps.execution_count,-1) execution_count

,p.create_date,p.modify_date

from sys.dm_exec_procedure_stats deps

right outer join sys.procedures p on deps.object_id = p.object_id

where database_id= db_id()

Lee Linares SSCrazy Points: 2727 More actions · Answer 11

I like your solution, however, that method would NOT show the stored procedures that do NOT have a cached stored procedure plan. The Right JOIN Vignesh used was to include those procedures that had not been executed. Personally, I liked your modification since it is cleaner. Thanks.

Lee

bhatnagar_shilpi Newbie Points: 3 More actions · Answer 12

But will this work if we are running rebuild and reorganize indexes every week and also i have noticed that few stored procedures cache gets cleared every day , may be because of lot of insert/update/deletes or some other reason.

Thanks

Shilpi