Posted in
Windows Server |
No Comment | 1,925 views | 10/11/2015 15:07
Following query will give you details about SP usage:
SELECT DB_NAME(database_id) DBName,
OBJECT_NAME(object_id) SPName,
last_execution_time LastExec,
last_elapsed_time/1000 LastTimeMS,
last_worker_time/1000 LastWorkerCPU,
last_physical_reads LastPReads,
last_logical_writes LastLWrites,
last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID() |
SELECT DB_NAME(database_id) DBName,
OBJECT_NAME(object_id) SPName,
last_execution_time LastExec,
last_elapsed_time/1000 LastTimeMS,
last_worker_time/1000 LastWorkerCPU,
last_physical_reads LastPReads,
last_logical_writes LastLWrites,
last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID()
If you need to get all custom SPs in one query:
SELECT DB_NAME(database_id) DBName,
OBJECT_NAME(object_id) SPName,
last_execution_time LastExec,
last_elapsed_time/1000 LastTimeMS,
last_worker_time/1000 LastWorkerCPU,
last_physical_reads LastPReads,
last_logical_writes LastLWrites,
last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats
WHERE database_id > 4 AND database_id < 32767 |
SELECT DB_NAME(database_id) DBName,
OBJECT_NAME(object_id) SPName,
last_execution_time LastExec,
last_elapsed_time/1000 LastTimeMS,
last_worker_time/1000 LastWorkerCPU,
last_physical_reads LastPReads,
last_logical_writes LastLWrites,
last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats
WHERE database_id > 4 and database_id < 32767
Just filtered last stats, but you can use SELECT * to get all stats.