如何在SQL Server 2008中检查存储过程的性能 [英] How to Check Stored Procedure Performance in Sql server 2008

问题描述

如何在Sql Server 2008中测试查询的性能

解决方案

如果要查看查询所消耗的时间，可以在之后使用属性窗口查看它每个查询都会执行.

您也可以使用SQL事件探查器.

启用包括实际执行计划"将为您提供执行查询所花时间的原因，并且如果SSMS知道了解决方案，那么它还将告诉您可以采取哪些措施来提高查询的性能

您可以使用Profiler来确定读取的次数，CPU的数量等.
MSDN:如何使用SQL事件探查器 [ DECLARE @ start DateTime DECLARE @ end DateTime SET @ start = getDate() - 做你的事情 SET @ end = getDate() 选择 DATEDIFF(ms， @ start ， @ end )- 将显示查询使用了多少毫秒.

另一个好的技巧是打开执行计划:

  SET   SHOWPLAN_ALL   ON 
 GO  

这将为您提供许多有关性能的有趣数据

完成后，请记住将其关闭:

  SET   SHOWPLAN_ALL  关闭
 GO  

您可以在MSDN上详细了解所有这些列和数字的含义: http: //msdn.microsoft.com/zh-CN/library/aa259203(v=sql.80).aspx [ 解决方案

If you want to view the time consumed by the query you can view it using the properties window after each query is executed.

You can also use SQL Profiler for the same.

Enabling the "Include Actual Execution Plan" will give you the reason for the time taken to execute the query and if the solution is known to the SSMS then it will also tell you what can be done to improve the performance of the query

You can use Profiler to se how many reads, how much cpu etc.
MSDN: How To use SQL Profiler[^]

If you just want to measure speed you can use this technique:

DECLARE @start DateTime
DECLARE @end DateTime
SET @start = getDate()

-- Do your stuff

SET @end = getDate()
SELECT DATEDIFF(ms, @start, @end) -- Will show how many milliseconds your query used.

Another good tip is to turn on the execution plan:

SET SHOWPLAN_ALL ON
GO

That will give you a lot of interesting data about performance

Remember to turn it off after you are done:

SET SHOWPLAN_ALL OFF
GO

You can read more about what all those columns and numbers mean at MSDN: http://msdn.microsoft.com/en-us/library/aa259203(v=sql.80).aspx[^]

这篇关于如何在SQL Server 2008中检查存储过程的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！