Secrets of SQL Server Execution plan (3/5)

Haja Batcha

So for we have saw somelimitations of execution plan, some alarming signs which tell us about the need for update statistics etc on my previous blogs. Now we will see how to analyse some more complex execution plans. As usual we will start with tip. It is always better to set the IO Statistics on while optimizing the query, which gives some clear picture about your query. While working with very big execution plan it will be little bit hard for us to check whether our indexes are used are not. for such situation just wright click anywhere on the plan and click “Show Execution Plan on XML” then you can use CTL+F to find any specific index. Also generating a XML plan has lot more benefits, such as memory fractions, parameters list used while optimization etc..

Also we should keep in mind the execution plan distributes the cost across the operators,  you may saw high number on Key lockups which require covering index. And you may saw warning symbol over hash match join etc.. which are all just instigators. There is no one rule fits all in query optimization which is the reason why we don’t have a query tuning robot yet.

Now we will move further to analyse a query deeply.

Using AdventureWorks as an example, the Production.Production ListPriceHistory table maintains a running list of changes to product
price. To start with, we want to see what the execution plan looks like not using a derived table so, instead of a derived table, we’ll use a
subselect within the ON clause of the join to limit the data to only the latest versions of the ListPrice.

SELECT p.Name,

p.ProductNumber,

ph.ListPrice

FROM Production.Product p

INNER JOIN Production.ProductListPriceHistory ph

ON p.ProductID = ph.ProductID

AND ph.StartDate = (SELECT TOP (1)

ph2.StartDate

FROM Production.ProductListPriceHistory ph2

WHERE ph2.ProductID = p.ProductID

ORDER BY ph2.StartDate DESC

) ;

Using AdventureWorks as an example, the Production.Production ListPriceHistory table maintains a running list of changes to product
price. To start with, we want to see what the execution plan looks like not using a derived table so, instead of a derived table, we’ll use a
sub select within the ON clause of the join to limit the data to only the latest versions of the ListPrice.

What appears to be a somewhat complicated query turns out to have a straightforward execution plan. Reading it in physical operator order, there are two Clustered Index Scans against Production.Product and Production.ProductListPriceHistory. These two data streams are combined using the Merge Join operator.

The Merge Join requires that both data inputs are ordered on the join key, in this case, ProductId. The data resulting from a clustered index is always retrieved in an ordered fashion, so no additional sort operation is required here.

Merge Joins create a worktable to complete the operation. The creation of a worktable adds a great deal of cost to the operation because it will mean writing data out to tempdb. However, the Merge Join operation will generally still be less costly than the use of a Hash Match join, which is the other choice the query optimizer can make to solve the same type of join.

The two data streams are then joined through a Nested Loops operator, to produce the final result. On my next blog we will travel to the next level to explore other operators on the execution plan. To read my previous post click the links below. Thanks your time on my blog