THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

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.

 

Inside Microsoft SQL Server 2005: Query Tuning and Optimization 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.

 

I have interviewed Kalen a while back about this book and you can find that interview here:  Interview With Kalen Delaney About Inside Microsoft SQL Server 2005 Query Tuning and Optimization

Published Thursday, February 07, 2008 4:20 PM by Denis Gobo
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Windows Vista News said:

There is an interesting post over at sqlblog.com

February 7, 2008 4:00 PM
 

Paul Nielsen said:

February 8, 2008 1:02 AM
 

Mladen said:

***! :)

you've beaten me to it by 2 days :)

February 8, 2008 2:48 PM
 

Kalen Delaney said:

Thanks, Denis!

~Kalen

February 8, 2008 3:03 PM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement