Home > Query Plan -Part I, SQL Server Internals > Query Plan -Part I

Query Plan -Part I

This post is about how to see or get compiled query plans. I thought of writing this blog to help  DBA’s who run around to seek help how to get compiled query plans and see which one is being used for their query.

SQL Server follows some basis rules to generate the Query Plan.
1. Complier generates Complied plans for your Query.
2. Next is it creates an Execution Plans based on Compiled Query Plans.
3. Query Execution Happens, While Query Execution starts the optimizer look for indexes or Stats for best use).
To get all Query Plans in XML Format .

SELECT convert(xml,query_plan)    FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
 WHERE text like ‘%with (index(IX_CDS_Campaign_Login_UserID),NOLOCK)%’

To get Query Plan in XML Format for a Specific Query or StoredProc

In case u have an OLTP Environment where hundreds of request are flowing  and u want to get the Query Pan for a specific query , in the where conduction of the below u can give some specific sort of text which is written in the query.

 SELECT convert(xml,query_plan)    FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
 WHERE text like ‘%with (index(IX_CDS_Campaign_Login_UserID),NOLOCK)%’

To get Query Plan for a Specific Query or StoredProc  or all Complied Plans

    SELECT text,query_plan,plan_handle    FROM sys.dm_exec_query_stats AS qs
             CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp

SELECT text,query_plan,plan_handle  FROM sys.dm_exec_query_stats AS qs
      CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
      CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
 WHERE text like ‘%with (index(IX_CDS_Campaign_Login_UserID),NOLOCK)%’

In case u have different plans for the same query then u need to look into the Query Execution Plan for the reason.

Will write about the reason why different query plans are generated for the same query and how it affects the performance of the Database and System.

Hope this post will be helpful.

  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: