SQL Server Query Performance Tuning Distilled [Paperback]

Sajal Dam (Author)

Book Description

Publication Date: October 20, 2004

Many SQL Server-based applications perform far below expectation-costing money and time. And until now, there has been very little practical information available that explains how to identify and cure performance problems in SQL Server. But this book offers essential content to fill in the gap.

The second edition includes updated material plus one new chapter, and explains how to design and tune SQL Server applications to cope with ever-increasing workloads. With proven strategy, this book shows how to use SQL Server tools to identify a performance bottleneck, isolate the root cause, apply possible resolutions, and then quantify performance improvements. Also covered are crucial issues like index analysis and tuning, preventing deadlocks, optimizing workload, and minimizing costs.

Editorial Reviews

About the Author

Sajal Dam has been working on Microsoft technologies for over 12 years, with a strong background in database application design. Dam also maintains a strong background in troubleshooting and optimizing performance of Microsoft-based applications, from front-end web pages to back-end databases.

While working as a Development Consultant for Microsoft, Dam helped many Fortune 500 companies optimized database performance, as well as design scalable database solutions. Currently an IT Strategist at Dell, Sajal Dam manages the optimization and baselining effort of production databases, and works with the application development teams to identify and resolve performance bottlenecks in advance.

Product Details

• Paperback: 624 pages
• Publisher: Apress; 2nd edition (October 20, 2004)
• Language: English
• ISBN-10: 1590594215
• ISBN-13: 978-1590594216
• Product Dimensions: 9.2 x 7.4 x 1.5 inches
• Shipping Weight: 2.4 pounds (View shipping rates and policies)
• Average Customer Review: 4.5 out of 5 stars  See all reviews (20 customer reviews)
• Amazon Best Sellers Rank: #1,053,346 in Books (See Top 100 in Books)

Customer Reviews

Most Helpful Customer Reviews

19 of 19 people found the following review helpful:

5.0 out of 5 stars Excellent book focused on SQL Server, October 21, 2004

By 

Jack D. Herrington "engineer and author" (Silicon Valley, CA) - See all my reviews
(VINE VOICE)    (REAL NAME)   

This review is from: SQL Server Query Performance Tuning Distilled (Paperback)

This book is primarily focused on getting the most performance out of SQL servers. Overall the book is well written and very well illustrated. There are some weakpoints where code samples are overused, but those are few and far between. The standout chapters, like the one fragmentation analysis are worth the price of admission for people looking to use SQL server in large performance critical deployments.

This book should be compared with O'Reilly's equally excellent "SQL Tuning" book (0596005733). That book is more theoretical in nature, often using equations and graph theory to demonstrate how get the fastest queries. This book is much more practical in nature, often showing screen shots and pointing out directly what is most critical.

It's in the understanding of the reader where this book excels. All too often authors understand the topic but do not understand how to present it. Sajal obviously understands the fine points of the technology, but also understands how to pass on that experience effectively.

I highly recommend this book for anyone working in an environment where high performance database work is required, and where the database is SQL Server.

20 of 23 people found the following review helpful:

5.0 out of 5 stars A Very Good SQL Server Performance Tuning Book, September 22, 2004

By 

Chris Dickey (San Diego, CA USA) - See all my reviews
(REAL NAME)   

This review is from: SQL Server Query Performance Tuning: Distilled (Paperback)

I am a SQL Server 2000 performance consultant - TuneSQLServer.com. This author 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.

Sure theory is nice. The guy who gave the nasty review must be a serious competitor - g. Chapter 8 of the book has a large discussion about data fragmention. Sure, the optimizer can use DB integrity constraints as hints to formulate better plans, but constraints are not crucial. They are only helpful to the optimizer in a few cases when it is working on a very complex query. And about statistics histograms. Yes you should know that they 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 and 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. For God sakes, leave auto statistics update set to On. And only use a query hint as a very very last resort - that means almost never.

The common problem I see is that people spend too much time learning SQL Server 2000 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 2000 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 10 times by fixing weird problems that theory doesn't cover. The book does a good job setting the correct focus on using Query Analyzer and Profiler. Don't theorize, see what is actually happening and use experience and the bag of tricks to deal with the performance issues.

There are no silver bullets to SQL Server 2000 performance tuning. Some problems are easy to solve but many complex queries take a lot of experimentation to get a good result. Often times skewed data and stored proc 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 2000 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!

10 of 11 people found the following review helpful:

4.0 out of 5 stars Look at the last chapter, November 16, 2004

By 

W Boudville (Terra, Sol 3) - See all my reviews
(VINE VOICE)    (TOP 1000 REVIEWER)    (HALL OF FAME REVIEWER)    (REAL NAME)   

This review is from: SQL Server Query Performance Tuning Distilled (Paperback)

Dam starts off by describing quite general problems that might arise in any relational database. He also wants you to inject some rigour into your efforts, by defining fundamental baseline metrics for your system usage. Then, when trying ideas described later in the book, you can get objective quantitative feedback on their efficacies.

Of course, the bulk of the book is specific to SQL Server. But you should keep in mind that Dam is not primarily offering a bunch of quick tips on performance improvement. To be sure, the chapters do indeed give specific suggestions that you can try. But a more sophisticated reader can get an understanding of the broad constraints of SQL Server and a rough but good idea of its internal workings. You don't need source code for this.

It sbould also be said that you need a good knowledge of SQL and the theory of relational databases, before venturing into the book. Some chapters (like on blocking and deadlock analysis) will considerably test this background.

But the last chapter may be welcomed by some of you. It summarises the book by offering a detailed list of best practices. If you want to quickly gauge what the book has to offer, read the last chapter first.