SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...