识别 SQL Server 性能问题 [英] Identifying SQL Server Performance Problems

问题描述

我们的 SQL Server 2005 群集出现零星的随机查询超时.我拥有一些使用它的应用程序，所以我正在帮助调查.在常规的 ol' Perfmon 中查看 % CPU 时间时，您当然可以看到它正在消失.但是，SQL 活动监视器只提供进程使用的累积 CPU 和 IO 时间，而不是它当时或特定时间范围内正在使用的时间.也许我可以使用分析器并运行跟踪，但是这个集群被大量使用，恐怕我会在大海捞针中寻找一根针.我是不是找错了树?

We're having sporadic, random query timeouts on our SQL Server 2005 cluster. I own a few apps that use it, so I'm helping out in the investigation. When watching the % CPU time in regular ol' Perfmon, you can certainly see it pegging out. However, SQL activity monitor only gives cumulative CPU and IO time used by a process, not what it's using right then, or over a specific timeframe. Perhaps I could use the profiler and run a trace, but this cluster is very heavily used and I'm afraid I'd be looking for a needle in a haystack. Am I barking up the wrong tree?

有没有人有一些好的方法可以在这种环境中跟踪昂贵的查询/流程?

Does anyone have some good methods for tracking down expensive queries/processes in this environment?

推荐答案

这将给出平均 CPU 时间排名前 50 的语句，请在此处查看其他脚本:http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true

This will give you the top 50 statements by average CPU time, check here for other scripts: http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx?mfr=true

SELECT TOP 50
        qs.total_worker_time/qs.execution_count as [Avg CPU Time],
        SUBSTRING(qt.text,qs.statement_start_offset/2, 
            (case when qs.statement_end_offset = -1 
            then len(convert(nvarchar(max), qt.text)) * 2 
            else qs.statement_end_offset end -qs.statement_start_offset)/2) 
        as query_text,
        qt.dbid, dbname=db_name(qt.dbid),
        qt.objectid 
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY 
        [Avg CPU Time] DESC

这篇关于识别 SQL Server 性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！