execution plan basics - publications.sqltopia.compublications.sqltopia.com/execution plan...

Execution Plan BasicsWhat is going on under the hood?

About me

• MCT 2011

• MCTS and MCITP 2011

• SQL Server MVP 2009

• MCP and MCDBA 2001

• Technical reviewer Team Based Development

• TR/TE Exam 70-462 Administering SQL Server 2012

• Co-author of SQL Server MVP Deep Dives 2

• Twitter @SwePeso

• Active as SwePeso

• Blog at SQLTopia.com

Agenda

•Query Optimizer

•Reading the execution plan

•Basic indexing strategy

•Real world examples

QUERY PARSING

Prerequisites

Query Parsing

SQL query statement

Execution Plan

Identify

Localize

Resolve

Send

Algebrizer

Query Tree generation

Reuse Evaluate

StoreSend

Receive tree

Make exceptions

Generate plans

Execute

Storage Engine

Storage Engine

Locks Blocks

Dreadlocks Deadlocks

Latches

Plan Cache

Parameter sniffing

Plan ageLazy

writer

Additional removal reasons

Index

Statistics

RECOMPILE

DDL + DMLSET OPTION

CURSOR

Schema

Execution Plan security• Three formats

• Graphical

• Text

• XML

• Automatic access for• sysadmin

• dbcreator

• db_owner

GRANT SHOWPLAN TO [User]

GRAPHICAL EXECUTION PLAN ICONS

What you see is what you get!

Graphical execution plan icons

READING THE EXECUTION PLAN

Learning a new language

Activate the execution plan

Estimated Actual

Reading the execution plan

Tooltips

Lookups

How to tell if it’s a lookup

• SQL Server 2000

• BOOKMARK LOOKUP has it own icon.

• Same for clustered index and a heap.

• SQL Server 2005

• CLUSTERED INDEX SEEK icon on a clustered index.

• RID icon on a heap index.

• CLUSTERED INDEX SEEK is a lookup when

• Attribute LOOKUP equals 1 in the tooltip

What does lookup mean• A Key Lookup

• Displays that the Query Optimizer couldn’t return the wanted information in one step

• Is an indication of a lacking proper index.

• Is always accompanied by a Nested Loop Join

• Query Optimizer uses a ClusterKey or RID (Row ID) to match and get the other missing columns.

• Most common when using “SELECT *”

Consequences• BOOKMARK LOOKUP is a very expensive operation

• Generates random IO on clustered index.

• Query Optimizer• Can decide for a lookup if the total IO is less.• Prefers sequential IO before random IO

• You can help SQL Server to generate a better plan by adding the missing column to an existing index, or create a new index.• More and larger indexes need more disk space

• How to decide for scan or seek• Scan and get more records?• Seek and get less IO?• A simple WHERE clause can make the difference between them.

• This is a common reason you see execution plans not behave the way you would expect.

INDEXING STRATEGY

The Good Samaritan

Indexing• In SQL Server 2000 your only option is to add the missing

column as a key column• In SQL Server 2005 you can use the INCLUDE keyword to add

the column to the index.• Included columns are more effective than key columns!• They save space, ease searches and index updates• Workaround for the old 900 bytes index size.

• Choose wisely• Many indexes affects other operations

• UPDATE• DELETE• INSERT

• Don’t over-index your table• Keep indexes covering for most frequent queries.

REAL WORLD EXAMPLES

Let the fun begin!

Plan size sample

Bushy plan - before

CPU – 4,259Duration – 2,363

Reads – 2,995,846

CPU – 0Duration – 155

Reads – 129

Bushy plan - after

• SQLServer Quiz 2010 - SQL Server Query Optimizer – Friend or Foe?

• T-SQL Deep Dives: Create Efficient Queries

Simple MTD example

-- Query 1

SELECT TravelerID,

SUM(MilesTraveled) AS TotalMiles

FROM dbo.Travel

WHERE YEAR(TravelStart) = 2011

AND MONTH(TravelStart) = 1

AND DAY(TravelStart) <= 14

GROUP BY TravelerID

-- Query 2

SELECT TravelerID,

SUM(MilesTraveled) AS TotalMiles

FROM dbo.Travel

WHERE TravelStart >= '20110101'

AND TravelStart < '20110115'

GROUP BY TravelerID

Simple MTD example

When the plan is not enough

Query Scans Reads CPU Duration

Query 1 1 2,609 1,732 1,882

Query 2 3 5,506 561 368

set statistics io on

set statistics time on

-- Query 1

SELECT TravelerID,

SUM(MilesTraveled) AS TotalMiles

FROM dbo.Travel

WHERE YEAR(TravelStart) = 2011

AND MONTH(TravelStart) = 1

AND DAY(TravelStart) <= 14

GROUP BY TravelerID

-- Query 2

SELECT TravelerID,

SUM(MilesTraveled) AS TotalMiles

FROM dbo.Travel

WHERE TravelStart >= '20110101'

AND TravelStart < '20110115'

GROUP BY TravelerID

set statistics time off

set statistics io off

Implicit conversion

Implicit conversion

Implicit conversion - Number

Implicit conversion - String

Implicit conversion

Implicit conversion

(40 row(s) affected)

Table 'factMagazine'. Scan count 16, logical reads 94.

Table 'dimDivisionType'. Scan count 17, logical reads 10.

Table 'dimMagazineType'. Scan count 1, logical reads 16258.

Table 'dimDistributionType'. Scan count 2, logical reads 37.

Table 'Worktable'. Scan count 0, logical reads 0.

Table 'factMagazineStatistics'. Scan count 17, logical reads 75012.

Table 'dimProject'. Scan count 1, logical reads 5.

Table 'factScheduleHeader'. Scan count 16, logical reads 88.

Table 'Worktable'. Scan count 0, logical reads 0.

Table 'dimScheduleType'. Scan count 0, logical reads 400.

Table 'factScheduleDetail'. Scan count 4032, logical reads 8919.

SQL Server Execution Times:

CPU time = 1800311 ms, elapsed time = 257092 ms.

(40 row(s) affected)

Table 'factMagazine'. Scan count 16, logical reads 94.

Table 'dimDivisionType'. Scan count 17, logical reads 10.

Table 'dimMagazineType'. Scan count 1, logical reads 210.

Table 'dimDistributionType'. Scan count 2, logical reads 4.

Table 'Worktable'. Scan count 0, logical reads 0.

Table 'factMagazineStatistics'. Scan count 17, logical reads 75012.

Table 'dimProject'. Scan count 0, logical reads 2.

Table 'factScheduleHeader'. Scan count 14, logical reads 88.

Table 'Worktable'. Scan count 0, logical reads 0.

Table 'dimScheduleType'. Scan count 0, logical reads 2.

Table 'factScheduleDetail'. Scan count 15, logical reads 151.

SQL Server Execution Times:

CPU time = 16396 ms, elapsed time = 1719 ms.

select case datepart(MONTH,dbo.fnGetOrderDate(salesorderid)) when 1 then 'Jan' when 2 then 'Feb' when 3 then 'Mar' when 4 then 'Apr' when 5 then 'May' when 6 then 'Jun' when 7 then 'Jul' when 8 then 'Aug' when 9 then 'Sept' when 10 then 'Oct' when 11 then 'Nov' when 12 then 'Dec' end from sales.salesorderheader where salesorderid in (43659, 45038)

SQL ProfilerCreate Function fnGetOrderDate(@SalesOrderId integer) returns date with schemabinding as begin declare @OrderDate smalldatetime select @OrderDate = OrderDate from sales.SalesOrderHeader where SalesOrderID = @SalesOrderId return @OrderDate end

SQL Profiler

Table 'SalesOrderHeader'. Scan count 2, logical reads 6

SQL Profiler

CASE

WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 1 THEN 'Jan'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 2 THEN 'Feb'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 3 THEN 'Mar'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 4 THEN 'Apr'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 5 THEN 'May'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 6 THEN 'Jun'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 7 THEN 'Jul'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 8 THEN 'Aug'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 9 THEN 'Sept'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 10 THEN 'Oct'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 11 THEN 'Nov'

ELSE CASE WHEN datepart(month, dbo.fnGetOrderDate(SalesOrderID)) = 12 THEN 'Dec'

ELSE NULL

END

END

END

END

END

END

END

END

END

END

END

END

SQL Profilerselect case datepart(MONTH, OrderDate) when 1 then 'Jan' when 2 then 'Feb' when 3 then 'Mar' when 4 then 'Apr' when 5 then 'May' when 6 then 'Jun' when 7 then 'Jul' when 8 then 'Aug' when 9 then 'Sept' when 10 then 'Oct' when 11 then 'Nov' when 12 then 'Dec' end from sales.salesorderheader where salesorderid in (43659, 45038)

CASE WHEN datepart(month, OrderDate) = 1 THEN 'Jan' ELSE CASE WHEN datepart(month, OrderDate) = 2 THEN 'Feb' ELSE CASE WHEN datepart(month, OrderDate) = 3 THEN 'Mar' ELSE CASE WHEN datepart(month, OrderDate) = 4 THEN 'Apr' ELSE CASE WHEN datepart(month, OrderDate) = 5 THEN 'May' ELSE CASE WHEN datepart(month, OrderDate) = 6 THEN 'Jun' ELSE CASE WHEN datepart(month, OrderDate) = 7 THEN 'Jul' ELSE CASE WHEN datepart(month, OrderDate) = 8 THEN 'Aug' ELSE CASE WHEN datepart(month, OrderDate) = 9 THEN 'Sept' ELSE CASE WHEN datepart(month, OrderDate) = 10 THEN 'Oct' ELSE CASE WHEN datepart(month, OrderDate) = 11 THEN 'Nov' ELSE CASE WHEN datepart(month, OrderDate) = 12 THEN 'Dec' ELSE NULL END END END END END END END END END END END END

Trace Flags - Advanced

• All versions of SQL Server (2005, 2008, 2008R2 and 2012)• 7352 : Final query tree • 8605 : Converted tree • 8606 : Input, simplified, join-collapsed, and normalized trees • 8607 : Output tree • 8608 : Initial memo • 8615 : Final memo • 8675 : Optimization stages and times

• Some versions only• 2373 : Memory before and after deriving properties and rules• 7357 : Unique hash optimization used • 8609 : Task and operation type counts • 8619 : Apply rule with description • 8620 : Add memo arguments to 8619 • 8621 : Rule with resulting tree

Want to know more?

• Married with children• Girls - 8 year, 4 year and 1 year

• Boy - 2 year

• Live outside Helsingborg in Sweden.

• Blog at• http://weblogs.sqlteam.com/peterl/

• http://www.sqltopia.com

• Phil Factor Speed Phreak challenges• 3 time winner

• Simple Talk article series

• The Road To Professional Database Developer