Secrets of SQL Server Execution plan (2/5)

Haja Batcha

In the previous post we saw some limitations of execution plan, along with some tweaks. In this post we will go through some of the backstage things which are happening on the SQL Engine.

First i would like share some truths regarding the query optimizer, actually it was the disclaimer by the query optimizer that the indexes suggested by the optimizer should be reviewed by human brain. But some guys will apply the index suggested by the optimizer without much analysis, in that case this index will not be used by the query at all.

For example you are optimizing a code, and the optimizer is suggesting for a missing index. And you have created the suggested index and go back to the query execution plan but still it is suggesting the same missing index which you have created. Once this happens you can make sure, what you have created is not appropriate for that query. In this case the best way to resolve is to look deeper into the query. The query may need the same index with some small changes, which include changing the order of the key column or included column according to the need or adding index filter according to your where condition. By this way you will get the full benefit of the query optimizer.

When you sometimes look at Execution Plans, you can see that the SELECT operator has sometimes a so-called Memory Grant assigned. This Memory Grant is specified in kilobytes and is needed for the query execution, when some operators (like Sort/Hash operators) in the Execution Plans need memory for execution – the so called Query Memory.

This query memory must be granted by SQL Server before the query is actually executed. The Query Optimizer uses the underlying Statistics to determine how much Query Memory must be acquired for a given query. The problem is now, when the Statistics are out-of-date, and SQL Server underestimates the processed rows. In this case, SQL Server will also request to less Query Memory for the given query. But when the query actually executes, the query can’t resize its granted Query Memory, and can’t just request more. The query must operate within the granted Query Memory. In this case, SQL Server has to spill the Sort/Hash-Operation into TempDb, which means that our very fast in-memory operation becomes a very slow physical On-Disk operation. SQL Server Profiler will report those Query Memory Spills through the events Sort Warnings and Hash Warning.

Will see now about how to interpret some more complex query.

The best place to get started is with stored procedures. We’ll create a new one for AdventureWorks:

CREATE PROCEDURE [Sales].[spTaxRateByState]

@CountryRegionCode NVARCHAR(3)

AS

SET NOCOUNT ON ;

SELECT [st].[SalesTaxRateID],

[st].[Name],

[st].[TaxRate],

[st].[TaxType],

[sp].[Name] AS StateName

FROM [Sales].[SalesTaxRate] st

JOIN [Person].[StateProvince] sp

ON [st].[StateProvinceID] =

[sp].[StateProvinceID]

WHERE [sp].[CountryRegionCode] = @CountryRegionCode

ORDER BY [StateName]

GO

Then we will execute the above the stored procedure as below

EXEC [Sales].[spTaxRateByState] @CountryRegionCode = 'US'

You will get the execution plan which will look similar to the one which is given below.

Starting from the right, as usual, we see a Clustered Index Scan operator, which gets the list of States based on the parameter,@CountryRegionCode, visible in the ToolTip or the Properties window. This data is then placed into an ordered state by the Sort operator. Data from the SalesTaxRate table is gathered using an Index Seek operator as part of the Nested Loop join with the sorted data from the States table.

Next, we have a Key Lookup operator. This operator takes a list of keys, like those supplied from Index Seek on the AK_CountryRegion _Name index from the SalesTaxRate table, and gets the data from where it is stored, on the clustered index. This output is joined to the output from the previous Nested Loop within another Nested Loop join for the final output to the user.