如何在 SQL Server 2008 中找到性能最差的查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2499910/
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 the worst performing queries in SQL Server 2008?
提问by Thomas Bratt
How to find the worst performing queries in SQL Server 2008?
如何在 SQL Server 2008 中找到性能最差的查询？
I found the following example but it does not seem to work:
我找到了以下示例，但它似乎不起作用：
SELECT TOP 5 obj.name, max_logical_reads, max_elapsed_time
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd
INNER JOIN sys.sysobjects obj on hnd.objectid = obj.id
ORDER BY max_logical_reads DESC
Taken from:
取自：
http://www.sqlservercurry.com/2010/03/top-5-costly-stored-procedures-in-sql.html
http://www.sqlservercurry.com/2010/03/top-5-costly-stored-procedures-in-sql.html
回答by KM.
top 10 worst queries based on...:
基于...的前 10 个最差查询：
SELECT TOP 10
total_worker_time/execution_count AS Avg_CPU_Time
,execution_count
,total_elapsed_time/execution_count as AVG_Run_Time
,(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE
WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE statement_end_offset
END -statement_start_offset)/2
) FROM sys.dm_exec_sql_text(sql_handle)
) AS query_text
FROM sys.dm_exec_query_stats
--pick your criteria
ORDER BY Avg_CPU_Time DESC
--ORDER BY AVG_Run_Time DESC
--ORDER BY execution_count DESC
回答by Ivan Zlatanov
回答by Matt Wrock
If you want to find the worst performing queries by time taken, I'd use this:
如果您想按时间查找性能最差的查询，我会使用这个：
SELECT *
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(sql_handle) hnd
ORDER BY total_elapsed_time/execution_count DESC
However, finding the "worst" queries often requires a bit more probing into the exec_query_statsDMV. There are lots of things to consider:
然而，找到“最差”的查询通常需要对exec_query_statsDMV 进行更多的探索。有很多事情需要考虑：
- Worst individual queries by time taken which the above query will yield.
- Worst CPU hogs (if you are running high on CPU) which would order by
total_worker_time/execution_count - Queries doing the most reads which are often queries that take the longest.
- 上述查询将产生的时间最差的单个查询。
- 最糟糕的 CPU 猪（如果您在 CPU 上运行很高），它将按以下顺序排序
total_worker_time/execution_count - 读取次数最多的查询通常是耗时最长的查询。
Now these queries will highlight queries that have poor performance but often you might have queries with "fair" performance but get called very frequently which drives down the overall performance of your app. To find these, order the above query by total_elapsedtime (or total_[whatever metric you are interested in]) and do not divide by execution_count.
现在，这些查询将突出显示性能不佳的查询，但通常您可能有性能“一般”的查询，但会被频繁调用，这会降低应用程序的整体性能。要找到这些，请按total_elapsed时间（或total_[whatever metric you are interested in]）对上述查询进行排序，不要除以execution_count。
回答by Ivo
Finding slow performing queries with SQL Profiler
使用 SQL Profiler 查找执行缓慢的查询
- Start SQL Profiler (preferrably on the live database).
- File -> New Trace
- Choose SQL server
- Tab filter
- Optionally set a filter on the database name
- Start the profiler (RUN)
- Save the result in a table, for example: _Mytrace, preferrably on a database server that hasn't got much to do already
- Filter the select queries
- order them by duration
- Check exectution plan for this queries
- 启动 SQL Profiler（最好在实时数据库上）。
- 文件 -> 新跟踪
- 选择 SQL 服务器
- 标签过滤器
- （可选）对数据库名称设置过滤器
- 启动分析器 (RUN)
- 将结果保存在一个表中，例如：_Mytrace，最好在已经没有太多事情要做的数据库服务器上
- 过滤选择查询
- 按持续时间排序
- 检查此查询的执行计划
