Let me start by saying that I love books published by Red Gate software. They are concise, easy to read and address specific areas that are often buried in chapters of big books. This is the first book I have read on a much needed area of information – Execution Plans. I make notes as I read books, especially to understand new things and perhaps read more/experiment more..this time I tried to turn my notes into a book review. It is perhaps more of what I learnt from the book than a review per se but I felt it was worth recording.
The book is for beginner-intermediate level DBAs/database programmers who are attempting to understand how to read, interpret, and experiment with execution plans in SQL Server Version 5 and above.
The author is Grant Fritchey aka Scary DBA (http://scarydba.com).
Chapter 1 Execution Plan Basics
This chapter deals with processes that occur in relational and storage engine – parsing of the query followed by algerbrizer for name resolution.The output is called a query processor tree – and passed on to the query optimizer in binary form. The priority of the optimiser is the low cost plan that it can get to in the shortest time.The plan is stored in the plan cache and compared to previous plans, if it finds one similar it uses that, otherwise it keeps the new one. The reasons why actual and estimated plans are sometimes different is one of my favorite parts – and I was particularly enlightened by the fact that parellelism as used by the storage engine during actual execution can be one of the reasons for this.There is a lot of detail on reading graphical plans.In particular the information available on hovering over each operator was interesting and helps learn of two things, cached plan size (and how it can help with performance cache issues) and also that the costs are just numbers that indicate where the processing overload is (I/O or CPU). I also learnt that value of ‘Ordered’ (0 or 1) clause indicates if data is ordered for that operation or not(somehow thought it meant if data was ordered in general), and node id starting left to right gives the order of processing. I was a little confused around estimated rebinds and rewinds – which are explained as number of times init() operator is called (I did not quite understand what that meant until i got to page 82 where it is explained) The rest of the chapter covers familiar ground on capturing xml and text execution plans via profiler and query analyser.
2 Reading Graphical Execution Plans for Basic Queries
It was interesting to know of 79 types of operators and 4 kinds they fall into – DML based logical and physical, parellelism based, cursor operators and language elements. The most common ones are the DML based.There is also an interesting table with 20 types of common operators. I learnt also that there are two types of operators, blocking and non blocking – the first of which require all the data before execution, and second executes as it receives the data. Blocking operators lead to concurrency issues at times. Some examples of which operator is which would have helped a lot here.
The operators covered in this section are familiar for the most part. It was also interesting to learn of the reasons why a hash match join is selected – a missing or incorrect index and/or a missing/non sargeable where clause.A nested loop join may be the most efficient join for smaller to medium queries. Also a merge join may not be ideal if the data is not sorted before joining. I was momentarily misled by the sentence also that ‘if the order by clause does not specify an order the default order is ascending’..I thought it meant the rows would return in ascending order with no order by which is not true. I got it right on re reading it but perhaps this could have been stated as ‘if the order by clause does not specify order as ascending or descending the default order is ascending’. I have encountered the generation of sort warnings if data is too big to be sorted in memory as an indication of more RAM – was glad to find that confirmed.
Rebinds and Rewinds have always puzzled me and found the explanation very clear. Along with this is also a clear explanation of the init,getnext and close methods.Higher rebinds/rewinds pointing to potential high I/O is a very useful tip.
3 Text and XML Execution plans for Basic Queries
There is quite a bit of info on how to read a text based execution plan. I didn’t read this too much since it is going to be deprecated in future versions. On the XML Based execution plan – it was interesting to note the differences and additional information that an XML plan provides over graphical – such as estimated number of rewinds and rebinds.The actual xml plan also shows information such as DegreeofParellelism,MemoryGrant and Runtimeinformation.
4 Understanding more complex Query Plans
This chapter begins with analysis and explanation of a merge join – how it combines input from two clustered index scans and performs the operation, and why the optimiser picked merge join over hash join. There is also a great comparison of one query written in traditional sub query form and also using cross apply operator..with analysis of plans and why cross apply was more efficient. It helped me understand how to analyse comparative plans for efficiency in similar situations.It was also interesting to learn that queries that use aggregates make better candidates for indexed views, and that the optimiser can actually choose between using a plan with materialised view or using the indexed view as a regular view (=materialising during execution).
5 Controlling Execution Plans with Hints
Even before I write this must confess one thing, am not a huge fan of hints.I have dealt with several developers who insist on using hints as shortcut to fixing their query or looking into table design/indexing issues..and a DBA has to be very careful of some of them. But I do admit that hints have their place, especially when you have a lot of code that is not easily changeable. The hint I have found most useful by far is FORCE ORDER since we deal with lot of very uneven data distributions where I work..the way that hint sometimes cleans up an otherwise messy plan was reconfirmed.It was also interesting to note how the ‘keep plan’ hint works.It works by treating temporary tables as permanent tables to minimize recompiles on the query.
6 Cursor Operations
In this chapter I learnt about operators used in cursor operations. I found it extremely interesting and useful – to learn of purpose and difference between logical operators and physical operators as related to cursors. Logical operators are to do with creation, opening, closing and deallocation of cursors, while physical operators are involved with actual processing for cursor records.The generic ‘C’ operator displayed during an estimated execution plan involving cursors has often baffled me but now I clearly understood what it was and why actual plans looked much more informative when it came to execution. There is also an example of comparing the different cursor types – static,read only and keyset cursors for performance. This is definitely an experiment worth trying on cursor based code.
7 Using XML in execution plans
This chapter gives a general overview of xml based commands and how they work.
8 Advanced Topics
The first thing I liked about this chapter was the explanation on how to read large plans using the ‘+’ operator and zooming in specific areas. I have several large plans to deal with and usually opt for the XML version of the plan while reading them – so this was a very good tip to know. It was also interesting to know the optimizer’s decision process when it comes to using parellelism. Apparently
there are two plans created when it decides parellelism is usable – one with parellelism and one without. The usability is then checked every time the plan is used and the appropriate version is used depending on number of threads and system load. I found this a great tip to know. The rest of the chapter is overviews on Forced parameterization and plan guides
The only real criticism I would have of the book is that the screenshots of execution plans are tiny and very difficult to read.
All in all a great informative book and looking forward to read the next version.
This book is available on :
diligentdba 
Aug 29, 2011 @ 01:06:55
Good point on the screen shots. I tried to do it better on the new version. We’ll see how it comes out. Excellent review, very fair. Thanks!