Land of Confusion

Most SQL Servers out there have never had anyone do a thorough, targeted assessment on them, the way we do our Critical Cares.

This leads to a lot of astray troubleshooting, which may make sense for profiling other problems, but not SQL Server.

You know, the kind of tips that come from blogs so littered with typos that your monitor ends up covered with red ink and you can’t even see where SSMS is anymore?

Level one troubleshooting is looking at task manager, maybe some PerfMon counters, and pointing fingers at the disks if ever read latency touches the 20ms mark. Maybe someone pokes around the GUI, defragments an index or two, and puzzles about an imaginary problem with page splits or disk queue lengths.

Level two would be grabbing single-use, copy and paste scripts that give you an incomplete picture of what’s going on with your server and don’t provide any real analysis, just numbers. Maybe you also run DTA and create every index. Again. And for some reason I/O got worse. Again.

Level three would be setting up a repeatable process and applying it to all of your servers, like Brent wrote about recently in How to Do a Free SQL Server Health Check and How to Do a Free SQL Server Performance Check.

Beyond that are the type of shops that have dedicated, full-time staff, and SQL-specific monitoring in place. They have people and processes in place who are comfortable with the technology.

n00b gainz

If you’ve got an unhealthy SQL Server, the first 3 months of troubleshooting are typically the easiest.

There’s so much low hanging fruit, you can usually get away with just making a handful of simple changes to buy yourself time on the harder stuff.

But you actually have to make the changes. Just collecting the data isn’t enough.

This is a lot like what happens to most people who start to work on some aspect of their physical appearance, whether it’s trying to get stronger, or trying to get skinnier.

You can usually find some easy changes to make at first. Stop drinking soda, or stop putting so much crap in your coffee that it turns into a cake recipe. Stop horsing around with 10 lb kettle bells and balance balls.

Structure and programming is important to any long term goal, because you’re going to hit plateaus, and those plateaus are going to be frustrating.

It doesn’t matter where or when they happen.

Eventually the impact of making sure you have your settings right and taking that shrink database task out of your maintenance plan will wear off, and you’ll need to figure out the next layer of problems.

If you don’t keep it up, things won’t magically keep getting better.

Test, Tossed

Servers are a lot like bodies, in that they process changes pretty quickly.

If you always run 5 miles, your body will get really good at running 5 miles. Likewise, your server will have a fairly constant level of performance with Cost Threshold for Parallelism set to 5.

If you always curl 20 lbs for three sets of eight, your biceps will always look exactly like they can curl 20 lbs for three sets of eight. Again, your server will have a fairly constant level with MAXDOP set to 8.

After making changes, your server will pretty quickly start giving you feedback in the form of wait stats, query plans, index usage, and so forth. It’s up to you to take that new feedback and figure out if it’s good or bad, and if you need to keep making more changes.

If you don’t follow a plan, you’ll be sitting there staring at the few changes you made wondering why you still have a lot of the same problems.

It’s time to ask: what’s next?

What kind of help do you need, and when do you need it?

You can go a long way on free stuff. Blogs, scripts, webcasts, podcasts, Q&A sites, forums — they’re fine resources, but when do you pull the trigger and spend money?

I spent about $25 and got a 515lb deadlift. The book didn’t lift the weight for me. I had to read the book, and I had to do the work. But it was enough for me.

If I wanted to double that weight, I’d probably have to spend more money on targeted coaching, equipment, and bathtub chemicals from overseas.

The same basic maxim applies to you working with SQL Server: if you do the work, and you follow a process, your server will be in better shape.

You can get by on lower levels of spending to get things to a pretty good place, even if you opt for paid training.

If you want professionals to give you an assessment and design a program for you, that’s where our Critical Care comes into play.

We’ll spend three days going over your server from head to toe, finding its biggest problems, and designing a custom plan to solve them.

And we’ll show you how to do it on all your other servers.

If that sounds like the kind of help you need, drop us a line.

But remember: if you don’t follow the program, the pain isn’t going to go away.

Thanks for reading!

Brent says: over and over again, I hear people say, “I’ve been logging sp_WhoIsActive, Perfmon counters, and file stats to a table,” but they’re not actually doing anything with it. If that rings a bell, it’s time for you to put the active in sp_WhoIsActive.