SQL Execution plan deep dive (01-Basics I)

Majority of DBAs and Developers think about converting Scan into Seek after analyzing execution plan. This is a good start but is not sufficient to address most of the performance problems. I will start with basics in this blog and continue with detailed information in subsequent blogs.

• What is an execution plan:
Once SQL Server receives SQL Query it parses the query and optimizer identifies best sequence of steps to complete the request. This best sequence generated by the optimizer is called query execution plan!

• On what basis best (optimal) execution plan is generated
Optimizer uses the statistics and the constraints to calculate the estimated cost to complete the query with different approaches. Based on estimated cost it will identify the optimal execution plan.

• Can the execution plan be reused:
It is expensive to generate execution plan for every query every single time and the execution plans are stored in plan cache and reused by SQL Server.
Cache sql query information can be verified using following SQL query.

-- To view all cache plans
SELECT TOP 100 *
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY usecounts DESC 

-- To clear All Cache plans
DBCC FREEPROCCACHE

-- To clear just one cache plan
DBCC FREEPROCCACHE(<0xABCD…>)

-- To clear cache plan in specific database
DBCC FREEPROCCACHE(<dbid>)

-- To see the Execution plan from Cache
-- Note: This is only estimated execution plan!
SELECT  [cp].[refcounts]        ,[cp].[usecounts]        ,[cp].[objtype]        ,[st].[dbid]        ,[st].[objectid]        ,[st].[text]        ,[qp].[query_plan] 
FROM    sys.dm_exec_cached_plans cp         
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st         
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp ;

• Estimated and Actual execution plans:
Estimated Execution plans and Actual execution plan structure will be same most of the time but it could differ in few scenarios (changes are related to Parallel Execution threshold)
Estimated costs, number of rows, and number of executions may also defer depending (When statistics are not up to date and when dynamic parameters[filtering criteria] used)
Estimated execution plans are not created in few scenarios (When new table is created dynamically in the query/batch)

• What are different components you see in Execution plan:
• Clustered Index Scan
• Table Scan
• Top
• Index Seek
• Nested Loops
• Merge Join
• Hash Match
• Key Lookup
• RID Lookup
• Compute Scalar
• Filter
• Table Insert
• Table Spool
• Sequence Project
• Sort
• Stream Aggregate
• Index Insert
• Table Delete
• Table Update
• Constant Scan
• Concatination
• Segment
• Parallelism (Re-partition Streams, Gather Streams)
• Table Spool
• Hash Match

ExecutionPlan_Components

Good description of operators:
https://msdn.microsoft.com/en-us/library/ms191158.aspx

(To be continued in next blog…)

Advertisements
This entry was posted in Execution Plan, Performance, Productivity, SQL, SQL Server, Uncategorized and tagged , , . Bookmark the permalink.

2 Responses to SQL Execution plan deep dive (01-Basics I)

  1. Pingback: SQL Execution plan deep dive (02-Basics II) | Blog for reference – Vijred

  2. Pingback: SQL Execution plan deep dive (04-Spool) | Blog for reference – Vijred

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s