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
• Index Seek
• Nested Loops
• Merge Join
• Hash Match
• Key Lookup
• RID Lookup
• Compute Scalar
• Table Insert
• Table Spool
• Sequence Project
• Stream Aggregate
• Index Insert
• Table Delete
• Table Update
• Constant Scan
• Parallelism (Re-partition Streams, Gather Streams)
• Table Spool
• Hash Match
Good description of operators:
(To be continued in next blog…)