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

Review of SQL Server 2000 Performance Tuning

By Andy Warren,

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 this 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.

Total article views: 8249 | Views in the last 30 days: 2
Related Articles

PASS Data Arch. Virtual Chapter: Row Versioned Data Warehouses

PASS Data Arch. Virtual Chapter: Row Versioned Data Warehouses Subject:Row Versioned Data Warehou...


I’m speaking for the Performance Virtual Chapter of PASS

On January 4, I’m speaking about index internals for the Performance Virtual Chapter of PASS (http:...


PASS Performance Virtual Chapter’s 2015 Performance Palooza - TOMORROW! - 7/23

PASS HEADQUARTERS: The PASS Performance Virtual Chapter’s 2015 Performance Palooza is tomorrow...


Speaking about Columnstore Indexes at PASS Performance Palooza

The PASS Performance Virtual Chapter has a tradition of yearly “Palooza events”, in which there are ...


SQL PASS Performance Virtual Chapter Presentation

Today I was fortunate enough to be able to give a new presentation to the SQL PASSPerformance Virtua...