Denis Gobo said
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.
N. Mckechnie said
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.
Jaewoo Kim said
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.
Pushin' Fifty said
I was disappointed with this book, which I bought together with SQL Tuning by Dan Tow, hoping to get well-digested expert advice. SQL Tuning was all that I hoped for, and I highly recommend it.
This book, though, has that creepy quality so common to MSFT Press books, where very knowledgeable people, usually connected with the MSFT development teams, list feature after feature in long, passive-voice descriptions, failing to discriminate for the reader and advise as to what is useful and what is not. You have the sense that they spent lots of time at trade shows touting the latest horde of "features", and little time coding under the strain of deadlines and client expectations. To them, every SQL Server nuance is always useful and wonderful and should get fair mention :(
This is a simple example, but SQL Tuning tells me that table scans are normally fine when selecting above 20% of rows, and index seeks are good for row counts under a percent, the space between depending on circumstance (which gray space the book goes on to address). This book, meanwhile, provides no real guidance, and tells me that table scans can be good, and indexes are useful too, and that SQL Server handles both nicely, and that the optimizer selects one or the other, and that it uses iterators, and that they are important, and that you can see what the optimizer has selected, and that you can change that if you want, and that you can automate the change, and that you can document the change, and here are the 4 related undocumented stored procs, and that this is new for 2005, and that there are other related matters, and that SQL Server has all this. Thanks!
Seems they are always plugging the product and never can admit to having suffered with its complexity. The recommendations, if you get them, are always muted by a kool-aid soaked affinity for SQL Server, which does all things well and will never fail to offer just the feature you need to succeed.
The book runs very long and strikes me as a big core dump on 3,000 topics, none of which seem prioritized or emphasized in distinct categories. Sure, the book has distinct chapters into which related material is dumped, but this fails to serve as **guidance**, which is what you are buying the book for. Not written by people in the trenches. Not recommended unless you want to buy some additional MSFT documentation.
Regan H. Thacker said
I am only half way through this book, but the information it has given me has been valuable. I am excited to further test out the information, as well as learn more.
I have had issues using the Profiler before and was looking for more help. This book has provided it.
It does go a bit long for the issues I'm looking at, but I have been impressed so far.
Comments