I am wondering how to get the execution_time for all executions for a specific stored procedure. (using Microsoft SQL Server 2016)
I know that via dm_exec_procedure_stats I get information about the last_execution_time and execution_count but I am interested in the execution_time of every execution (of one stored procedure) which got counted for the execution_count .
What I get is something like this (as an example):
| name | database_id | execution_count | last_execution_time |
------------------------------------------------------------------
| sp_name1 | db_id1 | 23 | 11.09.2019 hh:mm:ss |
| sp_name2 | db_id1 | 12 | 09.09.2019 hh:mm:ss |
| sp_name3 | db_id2 | 3456 | 11.09.2017 hh:mm:ss |So basically I want a query to get a table which has in one column the name of the procedure and in the other the execution times of this procedure such that the number of rows should equal the execution_count from the procedure.
What I want is something like this:
| name | database_id | execution_time |
------------------------------------------------
| sp_name1 | db_id1 | 11.09.2019 hh:mm:ss |
| sp_name1 | db_id1 | dd.mm.yyyy hh:mm:ss |
| sp_name1 | db_id1 | dd.mm.yyyy hh:mm:ss |
| ... | ... | ... |which should have 23 rows.
1条答案
按热度按时间vq8itlhq1#
若要取得存储程序的执行记录，您可以用途：
性能分析器：
SQL Server〉工具〉SQL Server Profiler导航到文件〉新建跟踪。跟踪属性〉单击事件选择选项卡〉在计数器的存储过程分组中选择
SP:Completed counter〉单击常规选项卡以保存结果（表/文件）。〉列筛选器：您可以捕获正在使用的数据库〉Finally Run