Home > Query, SQL > BISQL # 54 – SQL Server Execution Plan #1 – All Basics and Introduction to SQL Server Execution Plan !!

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 !!

image_thumb1

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 ..

image 

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 Smile

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 :

  1. The Query Optimizer
  2. Query Execution
  3. Estimated and Actual Execution Plans
  4. 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

image

    In which actually we have all the details about query execution plan SmileBut I want to see Execution Plan its simple Just Press Ctr + L

here you go with you plan with well organized  Diagram

image 

In fact we will have more details when we are putting our mouse into particular plan  

as shown in following snap

image

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 Smile

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

Link Resource # 24: Sept 08–Sept 12

Advertisements
Categories: Query, SQL
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: