Archive

Archive for the ‘Query Plan -Part I’ Category

Query Plan -Part I

June 8, 2010 Leave a comment

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.