Is there a SQL Server performance counter for average runtime?

Question

I want to set up a production SQL server. After making adjustments (for example, changing the degree of parallelism), I want to know if this helped or damaged the query execution time.

This seems like an obvious performance counter, but in the last half hour I searched Google and the list of counters in perfmon, and I was not able to find the performance counter for the SQL server to give me an average execution time for all queries hitting the server. SQL Server equivalent for ASP.NET query execution time.

Is there one that I am missing? Is there another effective way to track the average request time for a server?

Answer 1

I do not believe that there is PerfMon, but there is a report in SQL Server Management Studio:

Right-click on the database, select Reports> Standard Reports> Object Performance Statistics . This will give you some very good statistics on what works in the database, how long it takes, how much memory / io takes, etc.

You can also run this at the server level in all databases.

Answer 2

You can use the Query Analyzer (which is one of the tools with SQL Server) and see how they are executed internally so that you can optimize indexing, etc. This will not tell you about the average or return trip to the client. To do this, you will need to register it on the client and analyze the data yourself.

Answer 3

I managed to do this by saving Trace to SQL. When the track is open

File > Save As > Trace Table

Select SQL and after its imported run

 select avg(duration) from dbo.[YourTableImportName] 

You can easily execute other statistics, max, min, count, etc. .. A better way to poll the trace result.

Answer 4

Average for what time and for what queries? You need to further determine what you mean by “average” or does not make any difference, which is probably the reason that this is not a simple performance counter.

You can capture this information by running a trace, capturing it in a table, and then you can slice and slice the runtime in one of many ways.

Answer 5

This does not give exactly what you need, but I highly recommend trying the Dashboard Performance SQL Server 2005 reports, which you can download here . It includes a report of the 20 most popular requests and their average execution time and many other useful ones (top requests from IO, expectation statistics, etc.). If you install it, be sure to pay attention to where it is installed, and follow the instructions in the "More Information" section.

Answer 6

The profiler will provide you with statistics on the execution time of requests and actions on the server. The total request time may or may not mean a lot, without linking them to specific tasks and query plans.

Other indicators of performance bottlenecks are resource competition counters (general statistics, latches, locks). You can see them through performance counters. In addition, searching for a large number of scan tables or other operations that do not use indexes may indicate the need for indexing.

On a busy server, an increase in parallelism is unlikely to significantly affect performance, since there are already many requests for a given time. Where parallelism gains on large rarely performed batch jobs, such as ETL processes. If you need to reduce the execution time of such a process, then parallelism can be a good place to watch. On a busy server performing a transactional load with many users, system resources will be occupied from the workload, so parallelism is unlikely to be a big win.

Answer 7

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

To open Activity Monitor :

• In Sql Server Management Studio (SSMS), right-click on the server and select Activity Monitor .
• Open Recent Costly Queries to see CPU usage, average query time, etc.

Hope this helps.