Menu
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?
562
Performance Monitor (PerfMon) is an inbuilt Windows Tool for monitoring all aspects of Windows and hosted applications such as SQL Server. Performance Metrics in PerfMon are referred to as counters. PerfMon counters can be visualized in real time.
Using Client StatisticsGo to Menu >> Query >> Select Include client Statistics. Execute your query. In the results panel, you can see a new tab Client Statistics. Go to the Client Statistics tab to see the execution time.
Use the Database Engine Tuning Advisor Another important technique for SQL performance tuning is to analyze the performance of Transact-SQL statements that are run against the database you intend to tune. You can use the Database Engine Tuning Advisor to analyze the performance implications.
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.
83
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
