Blog Post

Why Tune Queries?


It’s just a query against the database. If things are running slow, buy some more memory, a faster CPU or get a few more disks. Right? Seriously, tuning queries is just a pain and there’s no clear evidence that writing them correctly or tuning them has a major impact. Right?

Yeah, I’m being facetious. I’ve spent a considerable portion of my career  trying to make T-SQL code run faster. The fact is, throwing moneyhardware at the performance problem can fix it in many instances. At least temporarily. But let’s face it, you’re constantly changing the code. There are new queries, changes to old queries, it’s always changing. The code is probably the most volatile aspect of a database system because it is the easiest to change. So, you may think you have a handle on your queries as of this morning, but what about this afternoon?

If you have  a system where the data is never changing, never getting updated, never getting deleted, then your queries can probably be written once and work fine forever. But if you’re like most of us and you’re dealing with the fact that your data is constantly in a state of flux, you may need to worry about your queries. This is because the optimizer within SQL Server bases the decisions it makes on how it’s going to satisfy the query you gave it on the data within your system. As that data changes, the query that was running fine, might change.

Also, if you modify your structure, you’re going to affect your code. You can’t get away from that one. Especially if you add, delete or modify indexes or constraints. These all affect the performance of the code.

Here’s the tough one. Sometimes, people write T-SQL in a less than optimal fashion. While writing a routine in C# or Ruby  or whatever may or may not cost you some processing power if you get it a little bit wrong, T-SQL is just flat out unforgiving. You can make minor mistakes which have major impacts on performance. There’s not getting away from it.

Finally, the less efficient your code is, the more you’re going to see resource use go up. And once resources get consumed, you’ll be waiting on them for all your queries. This becomes a very vicious cycle.

All this combined, and more, is why you will probably have to focus on tuning queries at some point in your data professional career. So, are you ready to tune your queries? If not, I’d like to suggest you attend my full-day pre-conference seminar at the PASS Summit 2012 this year. I’m going to go through a complete and thorough cycle from identifying resource issues, to telling which query caused the problems, to learning how to troubleshoot the issues. That’s why the session is called Query Performance Tuning: Start to Finish.