Tags: MSSQL

What is Execution Plan?
As word suggest, Its the plan that shows how actually database executes the query by analyzing available table information. It includes database indexes and statistics on that table.

Execution Plan is tool provided by MSSQL to optimize your queries. Its very popular in community of Database Administrator. Yet so many programmer not really aware of this tool. It helps me to analyze query on daily basis.

Advantages
Execution Plan show us what is cost of executing particular query. Its holds one unique feature that It suggest Index on table and also promising analysis of index compare to MSSQL Profiler. Execution plan we'll show you following statistics on any node.

• Physical Operation
• Logical Operation
• Actual Number of Rows
• Estimated I/O Cost
• Estimated CPU Cost
• Estimated Number of Executions
• Number of Executions
• Estimated Operator Cost
• Estimated Subtree Cost
• Estimated Number of Rows
• Estimated Row Size
• Actual Rebinds
• Actual Rewinds
• Ordered
• NodeID
• Seek Predicates

How to enable "Execution Plan" in MSSQL Server?
Find Execution Plan icon  in the top toolbar. Click on it to enable and disable or shortcut key Ctrl + m. You've to do it for each query windows.

Execution Plan in the Action
On my database I'm executing following query and lets see what Execution plan show us.

SELECT * 
FROM stage_master sm 
LEFT OUTER JOIN task_master tm 
ON sm.id = tm.stage_id 
WHERE sm.document_id = 4592
AND sm.library_id = 25614

And the Execution Plan

Click to enlarge

Whats it says?
Well I'm going to very brief on this as there are other things here not available in this execution plan like Parallelism other things that differ from query to query.

• At the top green line says you should have index on table. It'll also generate query to create index. Right click on execution plan area then Missing Index Details. 
• Thick line shows it is processing lots of information and has much cost on query. 
• Hover your mouse pointer on any node it'll show you actual statistics as follow...

Tags: MSSQL