0

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 dbcc freesystemcache.

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.

marc_s
  • 8,932
  • 6
  • 45
  • 51
Cylindric
  • 336
  • 1
  • 2
  • 12
  • I'd almost guarantee you are seeing memory pressure on your cache. 2000 procs on 1000 databases? That's a lot of caching going on. – Kenneth Fisher May 21 '14 at 16:29
  • That's what I was afraid of. It is spread over half a dozen servers, but it's still 2k sprocs in each of the ~200 db's per server. I just wasn't sure if the stats were memory-only, or were stored somewhere out of RAM. – Cylindric May 21 '14 at 16:31

0 Answers0