Execution Plan

 

Contents 1 12 Execution Plan 2 1.1 12.1 Types of Execution Plans Statistics  (query statistics) : 2 1.1.1 12.1.1 Estimated Execution Plan 2 1.1.2 12.1.2 Live Execution Plan 2 1.1.3 12.1.3 Actual Execution Ption; 2 - 12.1.4 generated after the query execution 2 1.2 12.2 Possible Plan Types: 2 1.2.1 12.2.1 Table Scan 2 1.2.2 12.2.2 Index scan : 2 1.2.3 12.2.3 index seek : 2 1.2.4 12.2.4 spooling : 2 1.3 12.3 Query Cost Types: 3 1.3.1 12.3.1 IO cost 3 1.3.2 12.3.2 CPU cost 3 1.3.3 12.3.3 sub tree cost 3 1.3.4 12.3.4 operator cost 3

1. 12 Execution Plan

• "Execution Plan" Is Used By "Query Optimizer" Component To Ensure The Best Usage Of Resources (Memory & Cpu) 

• the above execution plans also give recommendations about missing indexes for a specific query. also, query execution stats. 

• but for complete set of recommendations we use dta tool [database engine tuning advisor tool].

• 
  

1. 12.1 Types of Execution Plans Statistics  (query statistics) :

1. 12.1.1 Estimated Execution Plan  

• plan for query execution; generated before the query execution

2. 12.1.2 Live Execution Plan 

• plan for query execution; generated during  the query execution >=2016

3. 12.1.3 Actual Execution Ption; 

• 12.1.4 generated after the query execution

2. 12.2 Possible Plan Types:

1. 12.2.1 Table Scan

• Applicable for heap (table without clustered index). the entire table is scanned from 1st row to last row.

2. 12.2.2 Index scan : 

• index pages content is scanned for data retrieval. all index pages are accessed for reads

3. 12.2.3 index seek : 

• index pages content is searched for data retrieval. only required index pages are involved

4. 12.2.4 spooling : 

• required data is loaded to tempdb for faster calculations, comparisons, loops, audits, etc..

3. 12.3 Query Cost Types:

1. 12.3.1 IO cost

• refers to the disk io for reads & writes on the database files.  for higher io cost, track for missing indexes & implement dta tool.

2. 12.3.2 CPU cost

• Refers to the thread management factor based on "numa" nodes on your processor. Each processor has two cores in a socket.

• ex: if you have a core 2 duo processor means you have four processor nodes.  Ensure lesser cpu cost. For higher cpu cost values, boost sql server priority and set proper thread count. 

• ssms > connect to server > right click server > properties

3. 12.3.3 sub tree cost

• refers to the cost involved in analysing the parse tree and compile tree.

reduce the number of sub queries and self joins, if possible. 

also ensure to check the health of the database. 

recompile the long running stored procedures. 

4. 12.3.4 operator cost

• this refers to query predicates, keywords and operations within sql queries.

ex: merge join option is faster compared to hash join option.

for higher operator cost, it is advisable to ensure stats updates and sp recompilations.

** avoid nested loops in the execution plans. for this, we "merge join" option.