A Very Good SQL Server Performance Tuning Book!
This author of this SQL Server performance tuning book presents the right stuff - lots of Query Analyzer and Profiler information. What is unique about this book is the large number of Query Analyzer graphical query plans. That is the right way to learn about performance and the best way to tune a system. I could worry about this book putting me out of business, but I know that it takes a long time to really understand the issues which are presented.
The common problem I see is that people spend too much time trying to understand SQL Server theory. That time is better spent becoming fluent in reading graphical plans in Query Analyzer and doing experiments like those shown in the book. It is best to have developers get the correct results with their SQL and then tune the parts that Profiler shows are a problem. Too much time is spent trying to optimize queries during the design phase. The so called "optimized query formulations" often run slow because the developers don't have advanced SQL Server performance skills. What is the point in trying to optimize when you really don't know how to do it - a waste of time. Sure, try to use techniques that are known to work well, but don't overdo it. Most of the time what I see are query formulations and indexes that were supposed to be optimized by theory and in practice run badly. I often speed up systems 2 to 15 times by fixing weird problems that theory doesn't cover. This book does a good job setting the correct focus on using Query Analyzer and Profiler. Lots of detailed examples are given. The examples are more real world than the overly simplistic ones that you commonly see in discussions of index tuning. Don't theorize, see what is actually happening and use experience and the bag of tricks to deal with the performance issues. The book gives a good explanation of how to do that.
The book covers table and index data fragmention. Data fragmentation has been a problem on many systems I have reviewed. Use a fill factor on indexes. You should know that statistics histograms exist on the first column of an index and that is about it. What is really important is being able to read the information in a Query Analyzer graphical plan to see if estimated values based on the statistics histograms differ greatly from the actual values when the query is run - turn on the Query / Show Execution Plan option and run your query. If the results are way off, you may end up scratching your head for a while. Estimates are only estimates. In some cases they are way off and that is the problem you must deal with. There are solutions even in those case but they are not always pretty. Do leave auto statistics create and update set to On. And only use a query hint as a very very last resort - that means almost never.
There are no silver bullets to SQL Server performance tuning. The list of performance issues is a long one with lots of variety injected by the complexity of real world systems. Some problems are easy to solve but many complex queries take a lot of experimentation and clever solutions to get a good result. Often times skewed data and stored procedure plan retention is a big problem. SQL Server 2005 statement level recompiles are going to go a long way towards getting rid of that problem. I do not agree with everything the author claims, but 90% of what he shows in his book is the right stuff for understanding how to get SQL Server to perform well! Buy the book and learn more than you know. But don't expect to know all the answers after reading this very good book!
Chris Dickey
www.FastSqlServer.com
www.ExpertSQLServer.com
www.TuneSQL.com