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.
I’m currently dealing with an issue where a user has code that has worked for years. It’s now timing out. Turns out the reason his code is timing out is a) it’s less than optimized (being nice here) and b) there is a piece of code on another instance on the same server that is chewing up all of the CPU. So now I have to explain to the first guy why he needs to re-write less than efficient code even though it’s been working forever. Then at the same time convince (strong arm) the owner of the other piece of code and tell him he needs to optimize his code. Believe me I’m all for tuning queries!
And that’s a pretty classic problem. Stuff can be bad without being catastrophic for a long time. Then suddenly, BOOM. It really is difficult sometimes to convince people that they have problematic code or structures.
PASS is fine but for those of us in another continent who can’t go to PASS but want to tune queries, please keep the articles coming.
Absolutely. I promise. And I’ll keep the books up to date. The Execution Plans book is getting an update real soon (within the next three weeks I hope).
Had an instance where code that had not changed in years was running very slow. It had been getting slower but the customer never complained until it was no longer tolerable. (Scramble)
Our current product is very comprehensive. It is used by a wide range of industries. Each industry and each client in an industry uses the product differently. We tune stuff all the time.
Fantastic point. I have seen a stored procedure go from an execution time of over an hour to under a minute with proper tuning of the code and tweaking of the indexes.