search
Categories
Sponsors
VirtualMetric Hyper-V Monitoring, Hyper-V Reporting
Archive
Blogroll

Badges
MCSE
Community

Cozumpark Bilisim Portali
Getting Stored Procedure Stats from MSSQL
Posted in Windows Server | No Comment | 1,699 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()

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

Just filtered last stats, but you can use SELECT * to get all stats.



Leave a Reply