1

What is the best way to capture SQL Server stored procedure latency in SQL Server 2016?

Plan is to create a SSRS report and send it to management. We also enabled query store.

Thanks much

0
3

Stored procedure execution statistics are surfaced via the sys.dm_exec_procedure_stats dynamic management view.

This query will show stats for stored procedures in the current database:

SELECT ObjectName = s.name + N'.' + o.name
    , sps.last_execution_time
    , sps.execution_count
    , sps.last_elapsed_time
    , sps.total_elapsed_time
    , avg_elapsed_time = sps.total_elapsed_time / sps.execution_count
FROM sys.dm_exec_procedure_stats sps
    INNER JOIN sys.objects o ON sps.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE sps.database_id = DB_ID()
ORDER BY s.name
    , o.name;

The output looks like:

╔══════════════════════════════════╦═════════════════════════╦═════════════════╦═══════════════════╦════════════════════╦══════════════════╗
║            ObjectName            ║   last_execution_time   ║ execution_count ║ last_elapsed_time ║ total_elapsed_time ║ avg_elapsed_time ║
╠══════════════════════════════════╬═════════════════════════╬═════════════════╬═══════════════════╬════════════════════╬══════════════════╣
║ dbo.procAccessUniqueID           ║ 2018-09-07 13:21:04.403 ║          173346 ║               614 ║          223886470 ║             1291 ║
║ dbo.procAddBatchClassFilter      ║ 2018-09-04 15:25:22.150 ║              43 ║              5124 ║             171329 ║             3984 ║
║ dbo.procAddBatchFilter           ║ 2018-09-06 15:04:34.817 ║              17 ║               251 ║              18767 ║             1103 ║
║ dbo.procAddLock2                 ║ 2018-09-07 13:21:05.047 ║          373086 ║               912 ║          542911271 ║             1455 ║
║ dbo.procAddTempUser_V10          ║ 2018-09-07 10:50:13.973 ║               4 ║               292 ║               1223 ║              305 ║
║ dbo.procAddUserGroupRelationship ║ 2018-09-07 13:14:49.533 ║            1124 ║                70 ║             151824 ║              135 ║
╚══════════════════════════════════╩═════════════════════════╩═════════════════╩═══════════════════╩════════════════════╩══════════════════╝

The avg_elapsed_time, measured in microseconds, shows the average elapsed time for completed executions of each stored procedure listed.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.