I need to identify stored procedures that are no longer used by our application (~2,000 stored procedures, duplicated in ~1,000 databases).
To do this, I'm querying
dm_exec_procedure_stats, which initially seemed to give good results, until I saw some stored procedures that I know are being used a lot.
Subsequent investigations show that if I run the query a few times in a row, I get records dropping out.
Is this a simple indication of memory pressure causing the cache to be cleared?
Nobody is running
dbcc freeproccache or
SELECT database_id, object_id, OBJECT_NAME(object_id, database_id) 'Name', d.total_elapsed_time, d.execution_count, d.total_worker_time, d.total_logical_reads, d.total_logical_writes FROM sys.dm_exec_procedure_stats AS d WHERE p.name LIKE '%myinterestingsproc%'
If, for example, that query returns two rows for 'one' stored procedure (because it's the same name in two db's, I consider it one stored procedure for the purposes of utilisation), a subsequent run of the query might lose one of the rows. Numbers of the remaining rows do not reset, so it's not a system-wide stats reset.