SQLServerCentral Article

Review of SQL Server 2000 Performance Tuning


This is a book you should own. At 19 chapters and 464 pages it's not a huge

book, but there is no better reference to have on the shelf. Those chapters

cover everything from capacity planning to performance monitoring to tuning

query performance at a very low level. The discussion of when to use RAID-5 vs

RAID-10 is the best I've read. Not just theory, this book has the numbers and

formulas to back up the theory! Another part I was pleased to see was a good

discussion of when to use the various boot.ini flags (/3GB and /PAE) to support

using additional memory over the standard 2g limit.

The performance monitoring sections are done well. Lots of information about

what the counters mean. For example, the system object has a context switches

counter - do you know at what point you should be concerned and as easy way to

reduce them? Answer: 10,000 per second, change the lightweight pooling option to

1. It also contains some great suggestions such as keeping at least one backup

on disk, so that if you need to restore you can begin immediately compared with

waiting on the tape drive. Not rocket science, but an important point to make in

any discussion of backups and not always obvious to new users/DBA's.

One other useful tip I picked up was about versioning stored procedures. You

can create two versions of the same procedure like this:

    create proc usp_test as ...........

    create proc usp_test;2 as .......

Then when you execute, if you just type usp_test you get the first version by

default. But you can execute "usp_test;2" to run the other version.

Have to think I'll find that useful for a few things!

I was disappointed not to see coverage of functions, both in terms of

performance tuning/considerations as well as more subtle things like the use of fn_trace_gettable

to load a trace from a file into a table for analyzing. My only other complaint

is not getting an e-version of the book on CD (book does not include a CD) so

that I find something quickly that may not be included in the index.

Microsoft Press does a great job of giving you a snapshot of the book, follow


link to see a complete listing of the table of contents, index, and a sample

chapter. I'd like to note that if you're still using SQL 7 there is an earlier

version of the book that I highly recommend as well. Sells for about $35 at

Amazon. Our thanks to Microsoft Press for providing us with a review copy -

we'll be giving it away to a lucky reader in an upcoming contest!

I'll rate this one four stars out of five.


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating