Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

Chapter VII.4. Tuning SQL Server

If you've been a database administrator for a while, chances are that you're accustomed to tinkering with the settings of your relational database platform in an attempt to squeeze out some additional performance. Some administrators even enjoy this often tedious, but frequently necessary chore. If this kind of experimentation appeals to you, we have good news and bad news. The bad news is that SQL Server 2008 automatically adjusts many of its internal settings so that you don't have to. The good news is that you can still have an impact on SQL Server's performance, which is what this chapter is all about.

To begin, we remind you that tweaking database server settings is no substitute for a solid performance foundation, including database design, indexing, optimal queries, and so on. With that admonition out of the way, the next task is to explore memory and other processor-related properties that you can set to augment system speed. We then explore how disk-related settings can shape your server's overall responsiveness. Finally, because communication protocols and other topics are important, too, the chapter closes with an examination of what these settings mean to you from a performance vantage.

NOTE

This chapter doesn't focus on optimal ways to interact with your data: That essential theme is covered in the previous chapter. Also, although you can make many of these changes using a script, stored procedure, or other character-based technique, ...