Most DBAs know that adhering to SQL Server best practices can help them avoid frantic calls to Microsoft Product Support Services (PSS). But how many of us really implement these best practices? In last week's SQL Server Magazine UPDATE, I introduced three best practices that Microsoft Escalation Engineer Bob Ward deems important: testing a recovery plan, establishing a valid performance baseline for the applications that run on your database, and tracking configuration changes to SQL Server and the databases you support. Ward says that most people who seek PSS help don't follow these basic best practices.
Last week, I covered the best practice of creating and testing a recovery plan. This week, I continue that discussion by addressing the second best practice Ward recommends: establishing a performance baseline. To determine whether you're following this best practice, answer this question: Have you or a colleague ever thought, "Hmmm, a user complained that the server is slow today, and the CPU does seem high. I wonder what CPU and I/O utilization looks like when the server is running fast?" If so, you probably don't have a performance baseline. A baseline lets you put your database's daily performance in context: Is 500 transactions per second high or low for your application? What's an average and peak range of transactions for Monday? What about Friday? A baseline gives you the answers.
As a SQL Server consultant, I spend most of my billable time doing performance-tuning work. After my conversation with Ward about performance-related calls to PSS, I can imagine many support calls going something like this:
Customer: SQL Server is slow and my end users are complaining. What can I do to speed up application performance?
PSS: What specifically is slow?
Customer: I'm not sure. It's just slow.
PSS: Can you describe how the system behaves when performance is acceptable to end users?
Customer: No. I just know it must be OK because the end users aren't complaining.
I've exaggerated this dialogue to make a point: Most customers treat performance as an "if it ain't broke, don't fix it" issue. That approach is reasonable and practical if users are ordinarily happy with performance. But what happens when an out-of-the-ordinary situation pops up?
Think about what would happen if you went to the doctor and said, "I have a problem, but I'm not going to tell you what it is. I want you to run every test that exists, and you can guess what's wrong with me." Of course, you'd never do that. To the best of your ability, you'd tell the doctor what doesn't feel right—and you'd be able to do so because you know how you feel when you're well. In other words, you have a baseline for your well-being, and you can quickly identify deviations in that baseline that can help the doctor pinpoint the cause of your illness.
The personal-health analogy might be closer to reality than you realize. By the time you call PSS with a performance problem, you're probably already in the hot seat. You want an immediate solution, not a diagnostic exercise that will take hours or days to find the root of your performance problem. PSS is staffed with talented SQL Server engineers who can help you diagnose the cause of a performance problem regardless of whether you have a solid performance baseline. But the process is much quicker and simpler if you can say, "It hurts when I touch here." Generally, PSS won't say, "Well then don't touch there."
My astute readers will remember that in last week's commentary, I promised to cover some things that Microsoft can do to help DBAs create baselines and track configuration changes. However, as I started exploring that topic, I realized I had too much information to include in this week's commentary. So I'll save those recommendations and cover them in a separate commentary after I've finished discussing Ward's best practices. Next week, I'll look at the third best practice that Ward recommends: tracking configuration changes to your database and server.
