Kalen Delaney www.SQLServerInternals.com
Kalen Delaney
Background: MS in Computer Science from UC Berkeley Working exclusively with SQL Server for 22 years Contracted by both Sybase and Microsoft to develop and
teach internals courses to Tech Support staff
Primary Author -- SQL Server 2008 Internals (MS Press 2009) Primary Author -- Inside SQL Server 2005: Query Tuning and Optimization (MS Press 2007) Author -- Inside SQL Server 2005: The Storage Engine (MS Press, 2006)
SQL Server Magazine columnist and contributing editor
© 2010 Kalen Delaney
Query Plans and Query Processing
2
Abstract
Before any query tuning can begin, you need to understand the current query plan. Once you understand how SQL Server is processing your query, you can look for more efficient alternatives. In this workshop, you‟ll learn the different tools for viewing query plans. We‟ll examine the basic plan elements and discuss how to determine when a plan is a „good‟ plan.
© 2010 Kalen Delaney
Query Plans and Query Processing
3
Query Processing and Query Plans: Topics Introduction to SHOWPLAN Basic Table Access Types of Joins Aggregation Sorting Data Modification
© 2010 Kalen Delaney
Query Plans and Query Processing
4
Introduction to SHOWPLAN Text
Based Tools
SET SHOWPLAN_TEXT ON SET SHOWPLAN_ALL ON SET SHOWPLAN_XML ON
Graphical
Showplan
Details shown in Properties window
Actual
vs Estimated Plan
SET STATISTICS PROFILE ON Two extra columns are returned showing actual values
Rows and Executes Use with other STATISTICS options STATISTICS IO and STATISTICS TIME STATISTICS XML © 2010 Kalen Delaney
Query Plans and Query Processing
5
DEMO DEMO 1: SHOWPLAN Variations
© 2010 Kalen Delaney
Query Plans and Query Processing
6
Estimated vs. Actual Plans
Estimated plans cannot be created if query creates objects it uses Temporary tables Work tables
Estimated plans may vary greatly from actual plans
Final plan can change due to: Temporary objects created
Changes in statistics © 2010 Kalen Delaney
Query Plans and Query Processing
7
Accessing Data in a Table Table
Scan
Index Seek Clustered Nonclustered Index Scan Clustered Nonclustered RID © 2010 Kalen Delaney
Lookup / Key Lookup Query Plans and Query Processing
8
Nonclustered Index Seek Into Heap
© 2010 Kalen Delaney
Query Plans and Query Processing
9
Nonclustered Seek Into Clustered Table
© 2010 Kalen Delaney
Query Plans and Query Processing
10
DEMO DEMO 2: SHOWPLAN Basic Icons
© 2010 Kalen Delaney
Query Plans and Query Processing
11
Scans and Seeks Scan – the table is a heap Index Scan – includes clustered index Table
Unordered Ordered
Seek – used with predicates on the leading columns of the index
Index
Unordered (point query)
RID/KEY Lookup is also called Bookmark Lookup Ordered (partial scan) Plan shows a join when using NC index
© 2010 Kalen Delaney
Query Plans and Query Processing
12
Processing of Joins Inner vs Outer Tables Don‟t confuse with Inner and Outer Joins! The table accessed first is called “outer” All other tables accessed are considered “inner” Nested Loop For each row of the outer table
find all matching rows in the inner
Merge Join Process both tables in the order the join
columns
Hash Join Build a hash table Compare all rows of the inner through
the hash table identifying the matches
© 2010 Kalen Delaney
Query Plans and Query Processing
13
Joins in Graphical Showplan
Data Flow Is Indicated From Right to Left, From Top to Bottom Processing flow is from left to right
The Outer Table Is the Top One
Performance Is Effected By: Join Order Type of Join Joins May Be Added to Plan For nc index bookmark lookup For AND processing (Index Intersection)
© 2010 Kalen Delaney
Query Plans and Query Processing
14
DEMO DEMO 3: Types of JOINs
© 2010 Kalen Delaney
Query Plans and Query Processing
15
Nested Loop Join
Most Common Type For each row of the outer table, find all matches in the
inner table
Best When There Is a Supporting Index on the Inner Table Low Memory Requirement Smaller Table is Generally Outer
Size after filtering is considered
How does SQL Server know which is smaller?
© 2010 Kalen Delaney
Query Plans and Query Processing
16
Merge Join
Best When Both Tables are Already Sorted in Same Order A small table may be sorted prior to
merging
Small and Large tables May Appear on Either Side of the Join
Low Memory Requirement
© 2010 Kalen Delaney
Query Plans and Query Processing
17
Hash Join
Outer table (“Build Input”) is used to build a hash table Inner table (“Probe Input”) is used to probe the hash table
Behaviors Unique to Hashing: Outer table is completely read before first inner row is accessed No order is preserved
Smaller Table is Used as Outer SQL Server knows which is smaller?
Large Memory Requirement Depending on size of build input
Useful for Adhoc Queries Not dependent on existing indexes
© 2010 Kalen Delaney
Query Plans and Query Processing
18
Join Type Applicability
Not All 3 Types of Joins May Be Considered in All Circumstances
Nested
Loop Join is Sometimes the Only Option
For a non-equijoin For a Cartesian product For certain types of subqueries which cannot be transformed to an equijoin
© 2010 Kalen Delaney
Query Plans and Query Processing
19
Aggregation GROUP
BY and DISTINCT
Stream Aggregation Hash Aggregation
© 2010 Kalen Delaney
Query Plans and Query Processing
20
GROUP BY and DISTINCT
Identify Matching Values
Aggregation Key Columns in the GROUP BY or DISTINCT clause
Difference Between GROUP BY and DISTINCT DISTINCT keeps no other information For Hash, DISTINCT can return rows immediately GROUP BY is limited to 8060 bytes
© 2010 Kalen Delaney
Query Plans and Query Processing
21
Stream Aggregation
Best for Smaller Sets or Sets Already Sorted From an Index, output of Merge Join, etc.
Using ORDER BY on the Same Columns as the GROUP BY Favors Stream Aggregation
Input Has to Be Sorted Output is automatically ordered
© 2010 Kalen Delaney
Query Plans and Query Processing
22
Hash Aggregation
Large Sets With Smaller Number of Groups More Memory Required Input Can Be in Any Order Output Is Order is not Predictable If Not Used for DISTINCT, All Rows Must Be Consumed Before the First Is Output
© 2010 Kalen Delaney
Query Plans and Query Processing
23
DEMO DEMO 4: Aggregation Query Plans
© 2010 Kalen Delaney
Query Plans and Query Processing
24
Sort
Must Read All Input Before Producing Output Exception is with FASTFIRSTROW hint
Typically used with ORDER BY
Not Needed If Proper Index Exists Indexes can be ascending or descending
May Be Introduced by the Optimizer
To enable a merge join To prepare input for Stream Aggregation To improve nested loop join performance For correlated subquery with aggregate
© 2010 Kalen Delaney
Query Plans and Query Processing
25
DEMO DEMO 5: Indexes for Sorting
© 2010 Kalen Delaney
Query Plans and Query Processing
26
UNION UNION Three
vs UNION ALL
UNION Algorithms
Hash Merge Concat
© 2010 Kalen Delaney
Query Plans and Query Processing
27
Data Modification Insert Delete Update Bulk Operations
© 2010 Kalen Delaney
Query Plans and Query Processing
28
Data Modification Considerations Indexes
Must Be Maintained Locks Are Held Logging is Performed Integrity is Validated
© 2010 Kalen Delaney
Query Plans and Query Processing
29
Insert
Clustered Table Row must be inserted in order Page may need to split
Heap Table New rows can be inserted anywhere SQL Server keeps track of free space
per page
© 2010 Kalen Delaney
Query Plans and Query Processing
30
Delete
Gaps on Page are Not Filled In
Ghost Records May Remain
Rows are only compacted when needed
Rows are marked as deleted Primarily a performance enhancer Also used for snapshot isolation Ghost record count available in sys.dm_db_index_physical_stats
Ghost Record Cleanup
© 2010 Kalen Delaney
Automatically done for committed transactions Also for rows no longer needed by snapshot transactions
Query Plans and Query Processing
31
DEMO DEMO 6: Ghosts
© 2010 Kalen Delaney
Query Plans and Query Processing
32
Delete vs TRUNCATE TABLE
Delete Removes One Row at a Time Each row is logged Each index modification is logged
TRUNCATE TABLE Removes All Data and Index Pages in Single Step Log records that TRUNCATE was performed Page deallocations are logged so command can be rolled
back
TRUNCATE Limitations: Permission is not directly grantable
Can‟t truncate table with foreign keys referencing it Triggers won‟t fire
TRUNCATE TABLE Resets Identity Value
© 2010 Kalen Delaney
Query Plans and Query Processing
33
Update
Update In Place Is Most Efficient
Default method of updating Exceptions: ○ ○ ○ ○
Change to clustering key Row grows so it won‟t fit on page in heap Table has update trigger Table is published for transactional replication
Forward Pointers Used If Row Is Moved
Only applicable to heaps Count available in sys.dm_db_index_physical_stats
© 2010 Kalen Delaney
Query Plans and Query Processing
34
DEMO DEMO 7: Forwarded Records
© 2010 Kalen Delaney
Query Plans and Query Processing
35
Bulk Modifications
Table Level For each row inserted, make the necessary changes to each index
Index Level Presort to match each index, and merge changes into index in one pass
© 2010 Kalen Delaney
Query Plans and Query Processing
36
Spooling
Create a Worktable of Values to Be Reused Table Spool Index Spool
Lazy vs Eager Spool
© 2010 Kalen Delaney
Query Plans and Query Processing
37
DEMO DEMO 8: Index Updates
© 2010 Kalen Delaney
Query Plans and Query Processing
38
Other Query Icons Parallel
Query
Distribute Streams Repartition Streams
(optional) Gather Streams Scan and joins can be processed in parallel Constant Remote © 2010 Kalen Delaney
Scan
Queries Query Plans and Query Processing
39
Summary Introduction to SHOWPLAN Query Plan Elements Types of Joins Aggregation Sorting Data Modification
© 2010 Kalen Delaney
Query Plans and Query Processing
40
vSeminar Wrapup Don‟t forget the quiz for the certificate Q&A in the Chat room
[email protected] for questions On-Demand Download PowerPoint for timestamps
© 2010 Kalen Delaney
Query Plans and Query Processing
41