Why Your Servers Need a Physical

1 Comment

I’ve been really lucky so far: I hardly ever get sick.

Steak Tartare
Steak Tartare

Every now and then, I engage in risky eating behavior and I come down with food poisoning. I should know better, but I just can’t help myself when I get the chance to try steak tartare, sushi, deep fried goat cheese or a Popeye’s #2 Extra Spicy, all of which have given me food poisoning. Yes, I know what Food Best Practices is, and generally I do a decent job of eating right, but who doesn’t fall off the wagon now and then?

Years of experience (including days in hotel bathrooms) have taught me how to handle basic health problems like a cold, a flu, or the aftermath of eating raw steak. I don’t usually see a medical professional for those symptoms. I know how to address those myself, and I can nurse myself back to health.

Nonetheless, once a year I shuffle into a doctor’s office in perfectly good health in order to get a physical and blood tests. The doc uses his skills and expensive equipment to find things about to go wrong – hopefully while they’re easy to catch and fix. At my last physical, for example, we determined that I was starting to suffer from GERD. My doctor explained how to handle it, and now I’m equipped to keep an eye on it and do preventative maintenance to ward it off. Without help from my doctor, I wouldn’t have been prepared, and I would have suffered some nasty problems down the road.

Your SQL Server Needs a Physical Too

As a database administrator, you’re better equipped than most people to handle database health problems. You do an above-average job of staying current with news and tips from other DBAs, and you understand a lot about how your systems work. If you’re like me, you know much more about what makes a database server tick than how your own body works. (My own medical knowledge comes exclusively from the game Amateur Surgeon, in which a pizza delivery guy uses a pizza cutter and salad tongs to get the job done.)

Please remove your clothes and wait for the DBA.
Please remove your clothes and wait for the DBA.

Don’t get overconfident, though: despite your SQL Server skills, you’re probably still a nurse, not a doctor. Even though you spend a lot of your spare time reading up on how to be a better database administrator, you don’t get the benefit of hands-on experience with the latest and greatest health technologies and techniques. While getting medical attention from a nurse is still better than a pizza delivery guy, it’s still no substitute for seeing a doctor.

Enter the SQL Server Health Check: a visit from a highly experienced consultant. Relax – there’s no paper gown involved. A consultant comes to your office for a few days or longer, uses a series of exotic scripts and tools on your server, and delivers an exhaustively detailed report. The resulting information tells you what’s going right and what’s going wrong, plus what you need to nip in the bud before it becomes an expensive disaster. I blogged about my Microsoft health check experience a couple of years ago, saying:

“I would highly recommend that anyone with a Premier Support agreement get a SQL Health Check. Even if you’re not having problems with your database servers, you’ll walk away with a useful set of knowledge about how to verify that everything’s functioning as it should.”

I’m still surprised by how few people are aware of the health check process, but the other DBAs who’ve done it have given it glowing reviews too. At the recent Quest Customer Advisory Board meetings, several of us shared our health check experiences and I learned about a new source for health checks: Solid Quality Mentors. Microsoft’s health checks are only done for Microsoft Premier customers, which tend to be large enterprise shops, but even small companies can get a SQL Server health check from SolidQ.

Health Checks Are Like a Blood Test

All of us have our own ways to poke around and find out if our server is doing okay, but we don’t have the skills or time to really dive deep. Microsoft and Solid Quality have invested thousands of hours into building very complex auditing tools that look into every nook and cranny of your server configuration and schema. I was stunned during my health check to see the number of things covered in such a short amount of time, and the consultant was kind enough to show me the contents of one of the auditing scripts. My jaw hit the floor – I just couldn’t believe how much logic was built into the tool.

After my health check finished, my first thought was to go build my own auditing script. I took a shot at it, but I quickly gave up because of the amount of time required. I’m glad I did, too, because I never would have been able to match it.

Only one local doctor takes my health insurance plan.
Only one local doctor takes my health insurance plan.

See, these companies continually strengthen their auditing tools based on feedback from their consultants. As they’re brought in to resolve performance problems, they learn new obscure issues faced by database administrators. They build scripts to check for the symptoms and add these scripts into the health check auditing tool. Something that might have taken days or weeks to stumble across now takes just seconds, and it happens automatically as part of the health check audit. When you get a health check, you’re taking advantage of the knowledge gained from consulting gig the company’s performed in the past. That’s like a blood testing machine: it’s crazy expensive, and us nurses can’t afford to have that kind of gear sitting around the house. Sure, an individual blood test only takes a matter of minutes, but think about how much skill and money it took to get there.

My generous, magnificent and good-looking employer, Quest Software, sells tools like Spotlight and Foglight Performance Analysis that do some of this same kind of analysis, so at first glance we look like competitors – not at all. Health checks are reasonably priced, but you probably can’t afford to have a full-time, round-the-clock expert from Microsoft or SolidQ watching all of your database servers. Quest’s tools give you that 24/7 insight into your servers so that when problems pop up, you can investigate and fix it immediately. Health checks are planned engagements that dive deeply into all aspects of a server’s performance, looking at areas our tools can’t reach. For example, we don’t investigate the number of indexes per table, which is something that SolidQ checks. I believe that even if you have all of the Quest tools, you’ll still benefit from getting a regular health check on your most important or worst-performing servers. (Hopefully those aren’t the same ones.)

As much as I’m gushing about health checks, you might think I’m getting paid for this endorsement. I’m purely doing this out of the goodness of my heart. I remember vividly how much I liked my health check and how much it expanded my knowledge, and if I was a DBA, I’d want to know this kind of option was out there.

How to Get a SQL Server Health Check

Begin by scoping out how many blood tests your server needs. Health checks can examine:

  • Hardware configuration
  • Windows configuration
  • SQL Server configuration
  • Database schema & queries

Choose which ones of those is most important to you first, and communicate that to the consultants as a part of the price quote process. That’ll help the consultants figure out the right person for your needs and build a timeline that will be long enough to accomplish your goals. For example, my first health check solely focused on the Windows and SQL Server configuration because the application team was convinced the problem wasn’t their queries.

After your team has agreed on a basic scope, contact Microsoft Premier Services or Solid Quality Mentors to find out how much it’ll cost.  In large cities, you may also have local consulting options too, but make sure the consultant has real-world experience with a lot of servers.  Don’t get a SQL Server database engine health check from someone who’s spent the last year managing an SSRS deployment, for example – their skills won’t be up-to-date with the latest performance and configuration techniques.  (If you’re reading this and your company offers these types of health check services, feel free to add a comment below this post with your web site & contact info.)

Scoping your health check doesn’t mean the consultant will completely ignore any other aspects, though. In my health check, after reviewing Windows and SQL Server, the consultant did a brief review of the database and showed why no hardware, OS or SQL Server changes would be able to accommodate the application – it needed serious reworking. The final health check report didn’t go into any depth on the schema or how to mitigate its problems, but the overall report was enough information to tell us what to do next.

The CAB members echoed my favorite part about an external health check; it’s like getting a second opinion from another doctor. As the in-house DBA, you may have been preaching the same story over and over to your developers and Windows admins. You may have been telling them for months/years/decades that they need to rewrite a particular cursor or they shouldn’t put TempDB on the C drive. Bringing in an independent outsider to take an objective look at your system and give an impartial report can help you effect the changes you’ve always wanted. There’s nothing more empowering than sitting in a conference room while the final report is discussed and saying, “See, I told you guys this was a problem, and now we can work to fix it together.”

Health Checks Are Training In Disguise

Health checks aren’t just ammo for the blame game – they’re also DBA training cleverly disguised as consulting costs. You’re having a hard time getting training money approved, right? Try getting a health check approved instead, but don’t tell your boss it’s actually training, muhahaha. I learned more during my one-week health check than I’d learned the rest of the year. Health checks at your office are great learning tools because they use your own servers as the examples. Instead of learning about theoretical problems with AdventureWorks samples, you’re learning things that are directly applicable to your own environment.

Before the consultant comes on site, block out your calendar for the health check dates. Plan to spend every available minute with the consultant to suck their brain dry. Even when they’re just sitting around waiting for results to come in, you should take advantage of their presence. Ask them about their recent horror stories at other shops and how they fixed the issues. Ask them what they like to look for first when examining performance problems. Listen and learn.

If I had the choice between going to a week of training versus getting a one-week health check on my nastiest database servers, I’d opt for the health check. The health check kills two birds with one stone: it shows me what’s wrong with my servers, and shows me how to correct it.

(Note – I’m on vacation, so I probably won’t be responding to comments for a few days. I scheduled this post ahead of time. I’m on a sailboat in Lake Michigan, and I’ll respond if I get within wireless range. And yes, I’m bringing my laptop.)

Previous Post
Two DBAs Walk Into a Bar
Next Post
The Ozar Family Tradition of Performance Monitoring

1 Comment. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.