List of SP

Question

Post reply

List of SP

rocky_498

SSCertifiable

Points: 6532
More actions
November 21, 2012 at 3:50 pm

#266317

Hi guys,
Need help, how i can get the list of SP in my Database that were not accessed in the last 3 Month?
Please advise.
Thank You.

Viewing 3 posts - 1 through 2 (of 2 total)

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

rocky_498 SSCertifiable Points: 6532 More actions · Answer 1

I am trying to run below query

select

DB_NAME(database_id) as "database_name"

, OBJECT_NAME(object_id, database_id) as "procedure_name"

, last_execution_time

from

sys.dm_exec_procedure_stats

WHERE DB_NAME(database_id) = 'MYDB'

ORDER BY last_execution_time DESC

Question = Which time i am receving " last_execution_time" Most of the SP time saying todays date. Please guide me.

Thank You.

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

rocky_498 (11/21/2012)
I am trying to run below query
select
DB_NAME(database_id) as "database_name"
, OBJECT_NAME(object_id, database_id) as "procedure_name"
, last_execution_time
from
sys.dm_exec_procedure_stats
WHERE DB_NAME(database_id) = 'MYDB'
ORDER BY last_execution_time DESC
Question = Which time i am receving " last_execution_time" Most of the SP time saying todays date. Please guide me.
Thank You.

No. This isn't right and you shouldn't use queries you find on the internet unless you thoroughly understand them. If you lookup sys.dm_exec_procedure_stats in Books Online (press the F1 key to get there), you'll find the following.

Returns aggregate performance statistics for [font="Arial Black"]cached [/font]stored procedures. The view contains one row per stored procedure, and [font="Arial Black"]the lifetime of the row is as long as the stored procedure remains cached[/font]. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view

The time a stored procedure remains cached could be measured in minutes which means it would look like it hadn't been used in 3 months if we fixed your query to do that.

I don't know of a more reliable alternate method but you must not use the code you posted for this. It won't give you the answer you expect all by itself.

--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)