blocks|key|3247193|text|我不相信有PerfMon，但在SQL+Server+Management+Studio中有一份报告：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3247194|右键单击数据库，选择Reports+>+Standard+Reports+>+Object+Execution+Statistics。这将为您提供一些非常好的统计信息，包括数据库中正在运行的内容、运行所需的时间、需要多少内存/io处理等。|offset|length|style|BOLD|3247195|您还可以在所有数据库的服务器级别上运行它。|3247196|entityMap^0|0|A|1K|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

I don't believe there is a PerfMon but there is a report within SQL Server Management Studio:

Right click on the database, select Reports > Standard Reports > Object Execution Statistics. This will give you several very good statistics about what's running within the database, how long it's taking, how much memory/io processing it takes, etc.

You can also run this on the server level across all databases.

blocks|key|2243577|text|你可以使用查询分析器(+SQL+Server的工具之一)，看看它们是如何在内部执行的，这样你就可以优化索引等。这不会告诉你平均值，或者返回到客户端。要做到这一点，您必须将其记录到客户端并自己分析数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2243578|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You can use Query Analyzer (which is one of the tools with SQL Server) and see how they are executed internally so you can optimize indexing etc. That wouldn't tell you about the average, or round-trip back to the client. To do that you'd have to log it on the client and analyze the data yourself.

blocks|key|3247308|text|我设法通过将Trace保存到SQL来实现。当跟踪打开时|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3247309|File+>+Save+As+>+Trace+Table|offset|length|style|CODE|3247310|选择SQL，并在导入SQL后运行|3247311|select+avg(duration)+from+dbo.[YourTableImportName]+|code-block|syntax|javascript|3247312|你可以很容易地执行其他统计，最大，最小，计数等…查询跟踪结果的更好方法|3247313|entityMap^0|0|0|4|7|7|H|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]|$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|10|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|11|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|R|$]]

I managed to do it by saving the Trace to SQL. When the trace is open

<code>File</code> > <code>Save As</code> > <code>Trace Table</code> 

Select the SQL, and once its imported run

<pre><code>select avg(duration) from dbo.[YourTableImportName] 
</code></pre>

You can very easily perform other stats, max, min, counts etc... Much better way of interrogating the trace result

blocks|key|2237914|text|另一种解决方案是多次运行查询，并获得平均查询时间：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2237915|DO+$proc$
DECLARE
++++StartTime+timestamptz;
++++EndTime+timestamptz;
++++Delta+double+precision;
BEGIN
++++StartTime+:=+clock_timestamp();
++++FOR+i+IN+1..100+LOOP
++++++++PERFORM+*+FROM+table_name;
++++END+LOOP;
++++EndTime+:=+clock_timestamp();
++++Delta+:=+1000+*+(extract(epoch+FROM+EndTime)+-+extract(epoch+FROM+StartTime))+/+100;
++++RAISE+NOTICE+'Average+duration+in+ms+=+%25',+Delta;
END;
$proc$;|code-block|syntax|javascript|2237916|在这里，它将查询运行100次：|2237917|PERFORM+*+FROM+table_name;|2237918|只需用PERFORM替换SELECT即可|offset|length|style|CODE|2237919|entityMap^0|0|0|0|0|3|7|C|6|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]|$M|Z|N|10|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

An other solution is to run multiple time the query and get the average query time: 

<pre><code>DO $proc$
DECLARE
 StartTime timestamptz;
 EndTime timestamptz;
 Delta double precision;
BEGIN
 StartTime := clock_timestamp();
 FOR i IN 1..100 LOOP
 PERFORM * FROM table_name;
 END LOOP;
 EndTime := clock_timestamp();
 Delta := 1000 * (extract(epoch FROM EndTime) - extract(epoch FROM StartTime)) / 100;
 RAISE NOTICE 'Average duration in ms = %', Delta;
END;
$proc$;
</code></pre>

Here it run 100 time the query:

<pre><code>PERFORM * FROM table_name;
</code></pre>

Just replace <code>SELECT</code> by <code>PERFORM</code>

blocks|key|2243573|text|对于哪些查询，平均时间是多少？您需要进一步定义您所说的“平均”是什么意思，否则它就没有意义，这可能就是它不是一个简单的性能计数器的原因。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2243574|您可以通过运行跟踪来捕获此信息，并将其捕获到一个表中，然后您可以使用多种方法之一来分割和分割执行时间。|2243575|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Average over what time and for which queries? You need to further define what you mean by "average" or it has no meaning, which is probably why it's not a simple performance counter.

You could capture this information by running a trace, capturing that to a table, and then you could slice and dice the execution times in one of many ways.

blocks|key|329795|text|它并不能完全满足您的需求，但我强烈建议您尝试一下SQL+Server2005性能仪表板报告，该报告可以从here下载。它包括前20个查询及其平均执行时间的报告，以及许多其他有用的查询(按IO排名靠前的查询，等待统计等)。如果您确实安装了它，请务必记下它的安装位置，并遵循附加信息部分中的说明。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|329796|entityMap|0|LINK|mutability|MUTABLE|url|http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en^0|1G|4|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

It doesn't give exactly what you need, but I'd highly recommend trying the SQL Server 2005 Performance Dashboard Reports, which can be downloaded <a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&amp;displaylang=en" rel="nofollow noreferrer">here</a>. It includes a report of the top 20 queries and their average execution time and a lot of other useful ones as well (top queries by IO, wait stats etc). If you do install it be sure to take note of where it installs and follow the instructions in the Additional Info section.

blocks|key|3247183|text|分析器将为您提供有关服务器上查询执行时间和活动的统计信息。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3247184|性能瓶颈的其他指标是资源争用计数器(一般统计信息、锁存器、锁)。您可以通过性能计数器查看这些内容。此外，查找大量的表扫描或其他不使用索引的操作可以向您提供索引可能是必要的指示。|3247185|在执行多个用户的事务性工作负载的繁忙服务器上，系统资源将因工作负载而繁忙，因此并行性不太可能是一个大赢家。|3247186|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

The profiler will give you statistics on query execution times and activities on the server. Overall query times may or may not mean very much without tying them to specific jobs and query plans. 

Other indicators of performance bottlenecks are resource contention counters (general statistics, latches, locks). You can see these through performance counters. Also looking for large number of table-scan or other operations that do not make use of indexes can give you an indication that indexing may be necessary.

On a loaded server increasing parallelism is unlikely to materially affect performance as there are already many queries active at any given time. Where parallelism gets you a win is on large infrequently run batch jobs such as ETL processes. If you need to reduce the run-time of such a process then parallelism might be a good place to look. On a busy server doing a transactional workload with many users the system resources will be busy from the workload so parallelism is unlikely to be a big win.

blocks|key|329886|text|您可以使用活动监视器。它内置于SSMS中。它可以让你实时跟踪服务器上所有当前昂贵的查询。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|329887|要打开活动监视器，请执行以下操作|329888|CPU+|329889|在Sql+Server+Management+Studio+(SSMS)中，右键单击服务器并选择Activity+Monitor.|unordered-list-item|329890|
|329891|Open+Recent+Expensive+以查看|329892|329893|使用率、平均查询时间等。|329894|329895|希望这能有所帮助。|329896|entityMap^0|5|5|0|3|5|0|0|1C|G|0|0|5|H|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|14|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|18|8|@]|D|@]|E|$]]|$1|O|3|P|5|L|7|19|8|@$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|Q|3|N|5|6|7|1C|8|@]|D|@]|E|$]]|$1|R|3|S|5|L|7|1D|8|@]|D|@]|E|$]]|$1|T|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]|$1|U|3|V|5|6|7|1F|8|@]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]]|X|$]]

You can use Activity Monitor. It's built into SSMS. It will give you real-time tracking of all current expensive queries on the server.

To open Activity Monitor:

<ol>
<li>In Sql Server Management Studio (SSMS), Right click on the server and select Activity Monitor.</li>
<li>Open Recent Expensive Queries to see CPU Usage, Average Query Time, etc.</li>
</ol>

Hope that helps.

blocks|key|332071|text|“SQL+Server:Batch+Resp+Statistics”组中有一些计数器，它们能够跟踪SQL批处理响应时间。计数器根据响应时间间隔进行划分，例如，从0ms到1ms，...，从10ms到20ms，...，从1000ms到2000ms等等，因此可以为所需的时间间隔选择合适的计数器。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|332072|希望能有所帮助。|332073|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

There are counters in 'SQL Server:Batch Resp Statistics' group, which are able to track SQL Batch Response times. Counters are divided based on response time intervals, for example, from 0 ms to 1 ms, ..., from 10 ms to 20 ms, ..., from 1000 ms to 2000 ms and so on, So proper counters can be selected for the desired time interval.

Hope it helps.

I want to tune a production SQL server. After making adjustments (such as changing the degree of parallelism) I want to know if it helped or hurt query execution times.

This seems like an obvious performance counter, but for the last half hour I've been searching Google and the counter list in perfmon, and I have not been able to find a performance counter for SQL server to give me the average execution time for all queries hitting a server. The SQL Server equivalent of the ASP.NET Request Execution Time.

Does one exist that I'm missing? Is there another effective way of monitoring the average query times for a server?

Is there a SQL server performance counter for average execution time?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我想调优一个生产SQL服务器。在进行调整(比如改变并行度)之后，我想知道这是有助于还是有损于查询执行时间。这似乎是一个显而易见的性能计数器，但在过去的半个小时里，我一直在搜索Google和perfmon中的计数器列表，但我无法找到SQL server的性能计数器来给出所有命中服务器的查询的平均执行时间。相当于ASP.NET请求执行时间的SQL Server。有没有我遗漏的？有没有其他有效的方法来监

问是否有用于计算平均执行时间的SQL server性能计数器？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有用于计算平均执行时间的SQL server性能计数器？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有用于计算平均执行时间的SQL server性能计数器？
EN