如何记录和查找最昂贵的查询? [英] How Can I Log and Find the Most Expensive Queries?

问题描述

sql2k8 中的活动监视器允许我们查看最昂贵的查询.好的，这很酷，但是有没有办法可以记录此信息或通过查询分析器获取此信息?我真的不想打开 Sql 管理控制台并查看活动监视器仪表板.

The activity monitor in sql2k8 allows us to see the most expensive queries. Ok, that's cool, but is there a way I can log this info or get this info via query analyser? I don't really want to have the Sql Management console open and me looking at the activity monitor dashboard.

我想弄清楚哪些查询写得不好/模式设计得不好等

I want to figure out which queries are poorly written/schema is poorly designed, etc.

感谢您的帮助！

推荐答案

1. 使用 SQL Server Profiler(在 SSMS 的工具菜单上)创建记录这些事件的跟踪:

1. Use SQL Server Profiler (on the tools menu in SSMS) to create a trace that logs these events:

 RPC:Completed
 SP:Completed
 SP:StmtCompleted
 SQL:BatchCompleted
 SQL:StmtCompleted

您可以从标准跟踪模板开始并对其进行修剪.您没有指定这是针对特定数据库还是整个服务器，如果是针对特定数据库，请包含 DatabaseID 列并为您的数据库设置过滤器 (SELECT DB_ID('dbname')).确保每个事件都包含逻辑读取数据列.将跟踪设置为记录到文件.如果您要让此跟踪在后台无人值守运行，如果您有足够的空间，最好设置最大跟踪文件大小，例如 500MB 或 1GB(这完全取决于服务器上的活动量，因此你将不得不吮吸它看看).

You can start with the standard trace template and prune it. You didn't specify whether this was for a specific database or the whole server, if it is for specific Db's, include the DatabaseID column and set a filter to your DB (SELECT DB_ID('dbname')). Make sure the logical Reads data column is included for each event. Set the trace to log to a file. If you are leaving this trace to run unattended in the background, it is a good idea to set a maximum trace file size say 500MB or 1GB if you have plenty of room (it all depends on how much activity there is on the server, so you will have to suck it and see).

简单地开始跟踪，然后暂停它.转到 File->Export->Script Trace Definition 并选择您的数据库版本，然后保存到文件中.您现在有一个创建跟踪的 sql 脚本，该跟踪的开销比通过探查器 GUI 运行要少得多.当你运行这个脚本时，它会输出 Trace ID(通常是 @ID=2)；记下这一点.

Briefly start the trace and then pause it. Goto File->Export->Script Trace Definition and pick your DB version, and save to a file. You now have a sql script that creates a trace that has much less overhead than running through the profiler GUI. When you run this script it will output the Trace ID (usually @ID=2); note this down.

一旦您有了跟踪文件 (.trc)(由于达​​到最大文件大小而完成了跟踪，或者您使用了

Once you have a trace file (.trc) (either the trace completed due to reaching the max file size or you stopped the running trace using

EXEC sp_trace_setstatus @ID, 0
EXEC sp_trace_setstatus @ID, 2

EXEC sp_trace_setstatus @ID, 0
EXEC sp_trace_setstatus @ID, 2

您可以将跟踪加载到分析器中，或使用 ClearTrace(非常方便)或将其加载到像这样的表:

You can load the trace into profiler, or use ClearTrace (very handy) or load it into a table like so:

SELECT * INTO TraceTable
FROM ::fn_trace_gettable('C:\location of your trace output.trc', default)

然后你可以运行一个查询来聚合这样的数据:

Then you can run a query to aggregate the data such as this one:

SELECT COUNT(*) AS TotalExecutions, 
    EventClass, CAST(TextData as nvarchar(2000))
 ,SUM(Duration) AS DurationTotal
 ,SUM(CPU) AS CPUTotal
 ,SUM(Reads) AS ReadsTotal
 ,SUM(Writes) AS WritesTotal
FROM TraceTable
GROUP BY EventClass, CAST(TextData as nvarchar(2000))
ORDER BY ReadsTotal DESC

一旦您确定了代价高昂的查询，您就可以生成并检查实际的执行计划.

Once you have identified the costly queries, you can generate and examine the actual execution plans.

这篇关于如何记录和查找最昂贵的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！