Inside Microsoft SQL Server 2005: Query Tuning and Optimization

by Kalen Delaney, Adam Machanic, Sunil Agarwal, et al.

Book Overview

Dig into the internals of tuning and optimization features in SQL Server 2005--with insights from leading SQL Server experts. This in-depth guide delivers best practices, pragmatic advice, and code... This description may be from another edition of this product.

• Format: Paperback
• Language: English
• ISBN: 0735621969
• ISBN13: 9780735621961
• Release Date: September 2007
• Publisher: Microsoft Press
• Length: 406 Pages
• Weight: 1.95 lbs.
• Dimensions: 1.0" x 7.4" x 9.0"

Customer Reviews

Review of Inside Microsoft SQL Server 2005 Query Tuning and Optimization

SQL performance tuning is probably one of those things you can do to really make a HUGE difference in performance. Let's put this in perspective: take a typical application, if you can improve the performance by 100% then you really made a huge improvement. You can improve a SQL query by 1000% with 2 lines of code (sometimes all you have to do is take away a % sign). If you can make a query sargable so that the optimizer can do an index seek instead of an index scan your query might go from 12 seconds to 200 milliseconds. Now try doing that in an application, even if you change all the string concatenation to use a stringbuilder instead of creating new strings all the time you will not get such a drastic performance improvement. I am sure you get the point by now, let's talk about the book. This book is part 4 of the Inside Microsoft SQL Server 2005 series, it is written by Kalen Delaney and five other authors. There are 6 chapters in this book 1 A Performance Troubleshooting Methodology This chapter explains some typical things that affect performance and also gives a troubleshooting overview 2 Tracing and Profiling This chapter explains how to use the profiler and how to analyze traces. SQL Server's built-in traces are also covered 3 Query Execution This chapter gives a query processing and execution overview. It explains how to read plans and goes into a lot of detail about analyzing plans 4 Troubleshooting Query Performance This chapter explains how to detect problems in plans, how to improve queries and some best practices 5 Plan Caching and Recompilation This chapter goes into detail about plan caching and recompilation and how to troubleshoot plan cache issues 6 Concurrency Problems The final chapter deals with concurrency (locking, blocking and deadlocking) This is an excellent book for an intermediate/advanced developer. There is so much new stuff in SQL Server 2005 compared to 2000 to help you with tuning queries that you probably want to read each chapter several times. The Dynamic Management Views are a big help and this book shows you how to use them. Some other cool stuff in this book is the discussion of internal tables, undocumented DBCC commands and undocumented trace flags to discover information which could help you determine much faster what the cause of a performance problem might be. Some pages are packed with so much information that you need to pause for a second and process all that info (I have read some pages two to three times in a row). You will also find out that there are more joins besides left, full and outer. Page 137 for example has a nice table with the three Physical Join Operators: Nested Loop Join, Hash Join and Merge Join. This table lists the characteristics for each of these joins. If you are an intermediate to advanced developer then I highly recommend this book.

A must-have for tuning SQL Queries

I rarely review books, but for this one I feel compelled to do so. I knew that SQL Queries were creating major bottlenecks in my web application, but I did not know which ones they were and how to fix them. My app was already live with thousands of users, and since I am primarily a web developer (and not a database expert), I needed practical help, and fast. Because of this book, within a matter of days I went from clueless to expert in reading query execution plans, creating effective indexes, and tuning my queries. By making my application much more responsive, I am sure I retained countless customers. Another big benefit: I am saving thousands of dollars per year on postponing or cancelling hardware upgrades (more processors, faster disk/raid systems, etc) that I thought were necessary to support my users. With my queries tuned and optimized, it looks like I can handle 4-5 times the load on my current infrastructure than I had previously thought. This book really is a must read for anyone with a web application with even a modestly sized database, who is concerned with performance and scalability.

A must for serious SQL 2005 specialists

I have read around 25 SQL 7/2000/2005 books including all 3 books and 1 pamphlet from Kalen Delaney. This is by far the best Kalen Delaney book and one of the best SQL 2005 books. Unlike her other SQL 2005 book (SQL 2005 Storage Engine), this book does not suffer from convoluted and often incomprehensible writing. This book covers (and covers it well): 1)Performance tuning using different indexes. 2)SQL execution plans and what each process means. 3)Explanation of statistics (the best I have read). 4)Troubleshooting Query Performance (mostly better usage of indexes). What this book does not cover are: 1)Database Administration (backup/recovery, replication, stored procedures etc). 2)Business Intelligence 3)How to write T-SQL (functions, views, SP etc). 4)Integration Services. Overall, this book is not a "how to" book as much as a very high level explanation of the workings of SQL and how they process T-SQL. I highly recommend it for those with already keen understanding of T-SQL and SQL 2005.

Kudos to Kalen & Company for the Definitive SQL 2005 PTO Manual

I received a copy of this latest- & -greatest book on SQL Server 2005 performance tuning & optimization--my passion & my career-- & am frankly in awe. Being a long-time fan, admittedly I was favorably predisposed. Yet I am frankly amazed at the extent to which in my initial review my expectations have been exceeded. The work of Kalen et al is likely to serve as not merely a guidebook but a milestone, providing a step-by-step manual surpassing all previously published pretenders. If only I didn't work for a living & could spend the time straightaway to absorb what I need from it. As it is, Inside Microsoft SQL Server 2005: Query Tuning & Optimization is likely to be my constant companion for the next few months & like her other books will soon become highlighted & dog-eared from extensive use. It will take quite a while to transcribe the complete methodology & DMVs & incorporating them properly into my library, but what fun it will be! My deliverables will undoubtedly have extensive references to Query Tuning & Optimization. Indeed, if I only this book were available just a few weeks ago it'd've been put to good use in my current engagement. Kudos to Kalen & company for providing us this wonderful work, long-awaited yet worth it.

Kalen's Magnum opus

At first glance, Inside SQL Server Query Tuning and Optimization, appears to be a multiple-author ensemble book with only 1½ chapters written by Kalen, which might be disappointing. However, the reality is that this database dream team is hand-picked by Kalen, and following Kalen's plan the book meets the high standards Kalen is known for. The flow of the information is the right way to understand and then solve query performance issues. Chapter 1 - A Performance Troubleshooting Methodology by Sunil Agarwal (Program Manager in the SQL Server Storage Engine Group at Microsoft.) The opening chapter introduces the many factors that influence query performance. Although it fails to connect every dot, the chapter is a comprehensive overview of SQL Server performance and a sound intro for readers without a solid background in SQL Server. Chapter 2 - Tracing and Profiling by Adam Machanic (SQL Server MVP. Leader of the New England SQL Server User Group in Boston, and all around smart guy.) Even if you use Profiler daily, you'll pick up some useful info in this thorough converge of SQL Server Engine Trace and the Profiler UI. Chapter 3 - Query Execution by Craig Freedman (Microsoft SQL Server Query Execution Team.) This chapter has more beef than a 16 oz filet in Kansas City. Wow. If you enjoy reading Query execution plans, then you'll read this chapter 3 or 4 times. There's deep knowledge in here you won't find anywhere else. I've lost sleep wondering about some of the questions answered by this chapter, and I've lost more sleep reading it. Chapter 4 - Troubleshooting Query Performance by Kalen Delaney and Craig Freedman. This is the practical part two of Craig's amazing chapter 3. Here Kalen and Craig show exactly how to diagnose and solve difficult query performance issues. Chapter 5 - Plan Caching and Recompilation by Kalen Delaney. This is the topic Kalen presented at the 2007 PASS Summit pre-con and her depth shows in this chapter. Since query plan caching is so important to executing queries, this chapter makes perfect sense in this book. Chapter 6 - Concurrency Problems by Ron Talmage (SQL Server MVP, and true gentleman. Ron leads the Pacific Northwest SQL Server Users Group which meets in Building 35, the SQL Server team building on the MIcrosoft Redmond campus.) In any high transaction production system, diagnosing and tuning locking and blocking is the difference between "it runs on my notebook" and "it runs with thousands of users." Ron goes beyond the basic explanation of locks and isolation levels to explain how to resolve specific conncurency issues. Book prerequisite: at least 2-3 years of writing SQL Server queries and a decent understanding of SQL Server. Like Kalen's other books, Inside SQL Server Query Tuning and Optimization, is readable, authoritative, and a requirement on every serious database developer's desk. Buy this book! and read it at least twice.