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

  • 1
    What do you mean "latency"? Are you talking about how long a stored procedure takes to execute? – Max Vernon Sep 7 '18 at 17:51
  • yes we are doing lot of conversions so want to monitor performance of the some stored procs and queries – soha7 Sep 7 '18 at 18:15
2

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.

  • Thanks Max. Is it possible to get this info per day? – soha7 Sep 7 '18 at 18:36
  • 2
    You'd need to run it nightly via SQL Server Agent, saving the results each time into a table. – Max Vernon Sep 7 '18 at 19:17

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.