BISQL # 54 – SQL Server Execution Plan #1 – All Basics and Introduction to SQL Server Execution Plan !!
Hi friends ,
As I have started random but in series blogging with different topics on Business Intelligence I have taken decision to go ahead with SQL server as well.
In this random thoughts I am going to cover lots of out focused topics !!
In this post I am starting with SQL Server Execution Plan all in one
As I am going through following book which free available online I am doing side by side posting too !!
In this book he has specially cover various topics such as
• How to capture execution plans in graphical, as well as text and XML formats
• A documented method for interpreting execution plans, so that you can create these plans from your own code and make sense of them in your own environment
• How SQL Server represents and interprets the common SQL Server objects – indexes, views, derived tables etc – in execution plans
• How to spot some common performance issues such as bookmark lookups or nused/missing indexes
• How to control execution plans with hints, plans guides and so on, and why this is a double-edged sword
• How XML code appears in execution plans
• Advanced topics such as parallelism, forced parameterization
and plan forcing.
Lets try to go from simple to complex and understand SQL Server Execution Plan
Fritchey has simply divided T-SQL Execution into two parts
1.Processes that occur in the relational engine
2.Processes that occur in the storage engine
From this chapter I just drown some graphs for my understanding ,I don’t know weather you are going for that or not but its simple as shown ..
He have explain Query execution in very good flow its in just 9 step as follows
First we write a query on query editor and just hit execute button then whole cycle begins 
as it first hits to Data Base Engine and Query Parsing
Query Parsing – Checking query errors and throw same
After Query Parsing it performs algebrizer operation.
Algebrizer – The algebrizer resolves all the names of the various objects, tables and columns, referred to within the query string.
after Algebrizer it performs The Query Optimizer
The Query Optimizer – Optimizer generates potential plans, it compares them to previously generated plans in the cache.
After this simple 4 steps :
- The Query Optimizer
- Query Execution
- Estimated and Actual Execution Plans
- Execution Plan Reuse
- Now after learning bit of theory regarding execution plan I just wanted to see how to make work execution plan
- Lets take query but here is something about this query also with this query we can easily
put together a query to get a very complete set of information about the execution plans on our system
- Here is script !!
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 ;
- …Output of the query is
- In which actually we have all the details about query execution plan
But I want to see Execution Plan its simple Just Press Ctr + Lhere you go with you plan with well organized Diagram
In fact we will have more details when we are putting our mouse into particular plan
as shown in following snap
So in this way we will see our query execution plan
We will surely have lots of post on same topics as well !!
No doubt we are always thank full for Fritchey for his beautiful book !!
Thanks for visiting my blog !!
Hope you will like this post on SQL Server Execution Plan and ready to use
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
SQL Server Mentalist 