SQL Server Performance, pt I

If your in Amsterdam, you have to look at windows.

Ok, so my career brought me back to Windows and Microsoft products. Bummer. One of my tasks recently required me to go over SQL Server 2005 Performance guidelines and Troubleshooting techniques and attending a lab of some sort where this performance guru shared some really nice tips.

It was actually very pleasant, and I want to share with you all some of the things I got from the training and if MS SQL Server is your thing, maybe you'll get some value out if this post.

So, first things first:
1 - tempdb: You can read more about it in this Technet article. The default size for this very important db is only 8 MB. Change it according to the few basic rules outlined in the link above.

2- Gathering generic performance statistics. MSSQL Tips gives a very nice overview on how and what kinds of statistics you can gather in MS SQL Server. I find this very useful to start a performance evaluation. Better yet, the Performance Dashboard add-on that can be downloaded here. It's too big and complex for this post, but if you need some pointers, ask me. Another really useful set of tools, jointly referred to as RML Utilities for SQL Server can be found here. Do some read-up on them and enjoy.

3 - I/O. This is one one the key elements in performance for just about any application. This might be a good time to introduce you to the SQL Server Best Practices
. The whitepaper on Predeployment I/O Best Practices is a must read if you don't know the basics on how to measure and improve this performance parameter.

4 - Isolation Levels. Be sure to read the Books Online on the 6 supported isolation levels in SQL Server 2005. Read uncommitted (or nolock), read committed, repeatable read, serializable, read committed snapshot and snapshot. Understand how these different levels affect concurrency.

5 - Stored Procedures. In most situations, SP's will provide better performance over queries any day. The reason for this is two-fold: The Data and Procedure caches, aka Memory Hogs R' us. A nice article I found on this can be read here.

There is so much more to say about this topic that I might consider transforming this in a series of posts. It would be just too much for a single post and there are several areas that I find would be really interesting to go over in depth, provided that I have the time to actually write something that is. :-)

Managing MS SQL Server is much more than pointing and clicking and therefore it is very easy to get it wrong. On the other hand, this is really valuable knowledge if you're considering a career as a performance engineer / consultant. Or even if you're just writing a Web App (RoR springs to mind) that you just can't figure out how to go about squeezing out every last bit of performance of, really knowing what you can do with the database layer is a definite plus.