如何在 SQL Server 中查找存储过程执行时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7766880/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find Stored Procedures execution time in SQL Server?
提问by Azeem
I have 10 stored procedures as follows: SP1,SP2,.....,SP10 These stored procedures do some stuff. I need to run these procedures as follows: EXECUTE SP1; EXECUTE SP2; ... EXECUTE SP10;
我有 10 个存储过程，如下所示： SP1,SP2,.....,SP10 这些存储过程做了一些事情。我需要按如下方式运行这些程序： EXECUTE SP1; 执行 SP2；... 执行 SP10;
When SQL server finshes to complete execution of these procedures, it gives ten lines showing any row changes caused by all these stored procedures. What i want to do is that after Execution of all stored procedures SQL Server also gives in output window the execution time for each Stored Procedures.Can i do this? I am sure that for achieving this task i need to modify stored procedures but i don't have any idea how to do it... Please help me. Thanks.
当 SQL Server 完成这些过程的执行时，它给出十行显示所有这些存储过程引起的任何行更改。 我想要做的是在执行所有存储过程之后，SQL Server 还在输出窗口中给出每个存储过程的执行时间。我可以这样做吗？我确信为了完成这项任务，我需要修改存储过程，但我不知道该怎么做...请帮助我。谢谢。
采纳答案by sll
You can use Sql Server Profilerfor this purposes it provides a lot of useful info along the each executed query and Stored procedure as well.
为此，您可以使用Sql Server Profiler，它也为每个执行的查询和存储过程提供了很多有用的信息。
MSDN: SQL Profiler Data Columns
SQL Profiler displays the execution time for each event
SQL Profiler 显示每个事件的执行时间
An other straightforward way:
另一种直接的方法：
- DECLARE 2 datetime variables: start/end
- SET start = GETDATE()
- EXEC SP_NAME
- SET end = GETDATE()
- Execution time - difference between end and start
- 声明 2 个日期时间变量：开始/结束
- SET 开始 = GETDATE()
- 执行 SP_NAME
- SET 结束 = GETDATE()
- 执行时间 - 结束和开始之间的差异
回答by Alex K.
Assuming management studio or some other environment with an output pane you can;
假设您可以使用管理工作室或其他带有输出窗格的环境；
SET STATISTICS TIME ON
EXEC SP1
EXEC SP2
...
SET STATISTICS TIME OFF
回答by Illia Ratkevych
declare @start datetime = getdate()
-- your SQL statements
exec dbo.MyStoredProcedure
declare @executionTimeInMilliseconds int = datediff(ms, @start, getdate())
回答by Widor
There are ways to do this using tools such as Sql Server profiler, but a simple way is to run each proc surrounded with a line to print the time:
有一些方法可以使用 Sql Server profiler 等工具来做到这一点，但一个简单的方法是运行每个 proc 用一行包围来打印时间：
print convert(varchar, getdate(), 21)
EXEC PROC SP1
print convert(varchar, getdate(), 21)
回答by Igor Micev
You don't need to modify the SPs code at all. The following query gives you some stats about SPs execution times.
您根本不需要修改 SP 代码。以下查询为您提供了有关 SP 执行时间的一些统计信息。
SELECT
DB_NAME(database_id) [database],
OBJECT_NAME(object_id) [stored_procedure],
cached_time,
last_execution_time,
execution_count,
total_elapsed_time/execution_count [avg_elapsed_time],
[type_desc]
FROM sys.dm_exec_procedure_stats
--WHERE OBJECT_NAME(object_id) IN ('SP1','SP2','SP3','SP4','SP5')
ORDER BY avg_elapsed_time desc;
Just try it.
就试一试吧。
回答by rahularyansharma
capture and display execution time
use two parameter of datetimetype
and set one using getdate()before start of tsqlin stored procedure
使用两个datetime类型的参数并在存储过程getdate()开始之前设置一个 usingtsql
and set second using getdate()after tsqland finally use datedifffunction to get the difference
并使用getdate()after设置第二个tsql，最后使用datediff函数来获取差异
