THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

SQL Server Performance Baselines – What's Missing?

A frequently asked question is what counters should be included in a SQL Server baseline. The discussion then quickly proceeds to define a set of perfmon counters to be logged as the performance baseline. And often, people seem to have an urge to try to reduce the baseline to a small number of perfmon counters that may cover processors, memory, I/O, and some aspects of SQL Server such as page life expectancy and cache hit ratio.

Unfortunately, only in very rare cases will such a small set of perfmon counters give you a baseline that would be practically useful for troubleshooting or performance analysis. If your baseline consists of a small set of perfmon counters, sooner or later you'll be in a situation where you wish you had included this other counter. And every time, it may be a different counter whose values you want to use as part of the baseline.

So instead of playing this hit-and-miss game, I'd suggest you include a comprehensive set of perfmon counters in your baseline. In addition, you may also want to include some SQL traces, and snapshots of some DMVs including wait stats.

Is that enough? In other words, is the following a good foundation for capturing a useful SQL Server performance baseline?

A comprehensive set of perfmon counters
Some SQL traces
Snapshots of DMVs including wait stats

Well, not really! There is still a gaping hole. The main character is still missing from the show, which is the performance information about the application itself. It is fine to collect information on how much CPU is being used, whether there is any memory pressure, whether the I/O latency appears to be within the generally recommended range, whether SQL Server spends a lot of time waiting on log writes, and so on. But without being able to correlate these SQL Server or hardware resource related data points with the application performance behavior, you have not established a baseline that is relevant to the application.

It is critical to realize that when it comes to performance problems, it is not CPU, memory, disks, or SQL Server query processor that will complain. Rather, it is the application folks that can add great misery to your life. That is, your job is not there to solve SQL Server or hardware resource problems. You are there to help resolve application performance issues that these SQL Server or hardware resource problems may be causing or contributing to.

To that end, your performance baseline must include the application performance data points. Exactly what these application performance data points should include and how they should be collected and reported is a decision that must be made jointly between you and your application folks.

A straightforward example is that the application folks select from many OLTP queries some of the key queries as the benchmark queries, record the server processing times of these queries as part of the application,and expose the recorded times to you for inclusion into the baseline.

As another example, assuming that the applicaiton is a long-running database batch job, you can work with the application folks to identify the key steps or key processing units in the batch, and log their processing times--among other useful stats such as resultset size--for inclusion as part of the SQL Server baseline.

Or if the number of application transactions processed per second is a critical performance measure, that number must be exposed and included in the performance baseline. Blindly picking the SQL Server counter transaction/sec or batch requests/sec as a performance indicator of the application transaction processing may help derail your troubleshooting efforts with red herrings, to speak politely, and often a total waste, to put it more harshly.

The key point is that an application should be instrumented to expose performance metrics that can be used to characterize database performance as seen by the application. SQL Server performance baseline must then include these application metrics so that the SQL Server and hardware performance metrics can be precisely correlated with the application metrics.

Unfortunately, I have not seen this being practiced as often as I think I should. And I see valuable troubleshooting time, especially at the initial stages, repetitively wasted on the database folks trying to ascertain exactly what's the problem from the application perspective.

Published Wednesday, January 30, 2008 11:26 AM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Linchi

First of all I'd like to thank you for another great article. Could you elaborate a little bit "performance information about the application itself"? You mentioned that 'Some SQL traces and Snapshots of DMVs including wait stats'  are still not enough. From your experience , what "application metrics" is? Moreover, it would realy great if you describe a way strating  troubleshooting  from your experience. Lets say a guy said that SQL Server is running slowly (there is an app that uses SQL Server), so I'd strart running PROFILER...... etc.

I understand there is no rule of thumb for Performances Base line,but certainly you you can say more...:-))

January 31, 2008 12:41 AM
 

C Robinson said:

I have a really simple baseline, every proc should complete its job in less than 4 seconds at the interface, This came from the app users suggesting that would be acceptable, Ideally 2 seconds. an easy baseline to set, monitor, improve on and report.  in real life its obviously load dependant so set a load to go with it!

All the rest comes in when this "baseline" fails to be acheived

January 31, 2008 1:43 PM
 

Linchi Shea said:

Uri;

It wasn't my intention to get into the whole troubleshooting discussion. But to pick up on your example where an app guy says that SQL Server is running slow. Well, that's precisely where you may have a problem and end up wasting a lot of time if you don't have immediate access to an well-defined and previously-agreed app metric to verify that the app is indeed running slow. I'm not saying that an app metric or baseline is all you need to determine whether the app is indeed slow. By its very nature, it won't be complete and most likely won't cover all the aspects of the app. But it sure beats being totally ignorant about the app and completely dependent on the app folks to tell you the app performance behavior, which is at best not very reliable or precise. And if a problem does come up that is not covered by the app performance baseline metric, at least you have just eliminated an area that otherwise may serve no better use but to divert your attention from finding the real cause.

What app metric should you use as part of your SQL Server performance baseline? That of course depends on the nature of the application, and what's important to the app folks. In a previous comment, C Robinson gives a good example. In any event, you need to work with the app folks to define the metric. But note that we are talking about SQL Server performance baseline, not the application performance baseline. So the app metric has to be directly related to SQL Server it's using, not some matrix computation on an app server, for instance. But if the matrix computation needs to retrieve its data frequently from the database, and the performance of the matrix computation happens to be sensitive to the performance of that data retrieval, some measure of the data retrieval may very well be a good candidate for inclusion into the SQL Server performance baseline.

February 1, 2008 12:43 AM
 

Uri Dimant said:

Linchi

Thank you very much for  the explanation. From my experience, I have clients  who use third party applications  and usually do not have an access to the source. So , you just start from the "scratch" and eliminate one by one areas. I very agree with you that it depends on the app folks to tell you the app performance behavior.

February 3, 2008 12:44 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement