Chapter 12. Keeping Your SQL Server Running Smoothly

Any complex mechanism you deal with requires some type of routine maintenance. You probably bring your car in for service every 3,000 miles to verify that it's functioning properly. You may have an air conditioning specialist perform preventive maintenance on your HVAC system before the warm summer months arrive. SQL Server databases also require maintenance to stay in tip-top shape and deliver optimal performance.

In this chapter, I describe a number of ways you can improve the performance of your SQL Server databases. I begin by discussing the use of indexes and partitioning to speed up database queries. Then I show you how you can optimize disk utilization by automatically or manually shrinking files. Finally, I give you some advice on verifying the integrity of your database and placing limits on the use of resources by individual users.

Indexing Data to Improve Query Performance

Here's a challenge for you: Pick up this book and identify every page that contains information about SQL Server's use of transactions. You have two basic options for meeting my challenge:

  • Read every page in the book and report each page that contains a reference to transactions.

  • Turn to the index in the back of the book and look up "transactions."

Obviously, it's a whole lot faster to consult the index. If I didn't include one in this book, you would have much more trouble quickly finding information on a particular topic. It's obviously a good idea for ...

Get Microsoft® SQL Server® 2008 For Dummies® now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.