Archive for the ‘Query Plans’ Category

How to Query the Cached Plans?

If you are required to navigate your way around a new city without a GPS or a map and you will have to find your way back to the same restaurant next week for the great beers or delicious food, I bet you would write down the route on how to get back there to prevent re-inventing the wheels again next week. This will save you time and energy on figuring out how to get back to the same restaurant. SQL Server works similarly as it doesn’t like doing the same work again. When you send your TSQL statements to SQL Server, before it returns any results back to you it creates an execution plan with the mindset and preparation that there may be a chance you will make the same request again. It will cache the execution plan in memory. If the same TSQL statement is being executed again, SQL Server will check the cache and reuse the same plan to avoid redoing the work on creating the plan again.

Great! Now, how do you find and view the cached plans? You can do so using the combination of the following Dynamic Management View (DMV) and Dynamic Management Functions (DMFs):

  • sys.dm_exec_cached_plans
  • sys.dm_exec_sql_text
  • sys.dm_exec_query_plan

sys.dm_exec_cached_plans is a Dynamic Management View (DMV) that returns the query plans that is cached by SQL Server for faster query execution.

sys.dm_exec_sql_text is a Dynamic Management Function (DMF) that accepts the plan handle parameter and returns the text of the SQL batch that is identified by the specified plan handle.

sys.dm_exec_query_plan is a Dynamic Management Function (DMF) that returns the query plan in XML format for the batch specified by the plan handle.

The following is an example on how to query the SQL Server’s plan cache:

1. Run the code to clear the plan cache. Do not do this on the production server.

USE AdventureWorks2012;
GO
-- Clearing the plan cache
-- *** Do not do this in production***
DBCC FREEPROCCACHE;
GO

2. Generate a cached plan by running the following query.

-- Run this query to generate a cached plan
SELECT BusinessEntityID, JobTitle 
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO

3. Query the cache plans by running the following query. As you can see, it is impossible to read the plan handles and interpret what they mean.

-- Query the sys.dm_exec_cached_plans to see all the cache plans
SELECT  size_in_bytes,
        cacheobjtype,
        objtype,
        plan_handle
FROM    sys.dm_exec_cached_plans;

4. To find the plan based on the query text, you need to do query the sys.dm_exec_cached_plans DMV and do a CROSS APPLY with the sys.dm_exec_sql_text function and pass in the plan handle.

-- Find the plan based on query text
-- Write down the plan handle
SELECT  cp.size_in_bytes,
        cp.cacheobjtype,
        cp.objtype,
        cp.plan_handle,
        st.text
FROM    sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE   st.text LIKE '%Production%';

5. Query the sys.dm_exec_query_plan DMF by passing in the plan handle you had identified in the previous step.

-- Query the sys.dm_exec_query_plan by passing the plan handle 
SELECT  DB_NAME(dbid) AS DatabaseName,
        query_plan
FROM    sys.dm_exec_query_plan(0x06000800CEC935259049B9ED0400000001000000000000000000000000000000000000000000000000000000);
GO

REFERENCES:

sys.dm_exec_cached_plans
http://technet.microsoft.com/en-us/library/ms187404.aspx

sys.dm_exec_sql_text
http://technet.microsoft.com/en-us/library/ms181929.aspx

sys.dm_exec_query_plan
http://technet.microsoft.com/en-us/library/ms189747.aspx

SET SHOWPLAN_XML ON vs SET STATISTICS XML ON

To view the estimated query execution plan and the actual query execution plan, rather than using the GUI menus, you can wrap the queries around the SET SHOWPLAN_XML ON and SET SHOWPLAN_XML OFF lines to see the estimated query execution plan and wrap the queries around the SET STATISTICS XML ON and the SET STATISTICS XML OFF lines to see the actual query execution plan.

Before you go ahead to run and test the queries, you will have to be aware of the critical notes about each. Here are the critical notes about SET SHOWPLAN_XML ON:

  • It will not execute the TSQL statements.
  • It will return detailed information about how the statements are going to be executed in XML.

Example:

SET SHOWPLAN_XML ON;
GO
SELECT BusinessEntityID, JobTitle 
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO
SET SHOWPLAN_XML OFF;
GO

Here are the critical notes about SET STATISTICS XML clause:

  • It will execute the TSQL statements.
  • It will generate detailed information about how the statements were executed in XML.

Example:

SET STATISTICS XML ON;
GO
SELECT BusinessEntityID, JobTitle 
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO
SET STATISTICS XML OFF;
GO

REFERENCES:
SET SHOWPLAN_XML
http://technet.microsoft.com/en-us/library/ms187757.aspx

SET STATISTICS XML
http://technet.microsoft.com/en-us/library/ms176107.aspx

Actual vs. Estimated Query Plans

If you had asked a psychic about what your future will be like 5 years from now, hopefully you already know that you are in it just for the fun of hearing the predications. The psychic will only be making predications based on how she feels at the moment and what hints she has picked up from reading you at the time you entered her magical kingdom room -> The Psychic Office. In a similar fashion, SQL Server can also provide you with estimated query plans on how your queries will be executed. Let’s just tell it like it is -> it is not a panacea. Nevertheless, you can tell Billy Bob to bet his farm on it that it will be more accurate than what the psychics will tell him on his luck with the ladies 5 years from now.

Let’s keep this short and sweet. The estimated execution plan will not execute the query being examined. It shows you on how the statements will be executed. On the other hand, the actual execution plan will execute the query and it will include the estimated and the actual number of rows by operator and executions by operators.

To view the estimated query plan, you can use the following example:
1. In SSMS, key in the following:

SELECT BusinessEntityID, JobTitle 
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';

2. On the menu bar, click Query > Display Estimated Execution Plan. Yes, you can also hit Ctrl+L just like what the menu is telling you.
Display Estimated Execution Plan

3. SSMS will then display the execution plan. If you click on the “Clustered Index Scan” operator and then press the F4 button on your keyword, you will then see the Properties for the operator and you will see almost all the Defined Values will contain the keyword “Estimated”:
Estimated Query Plan

To view the actual query plan, you can use the following example:
1. In SSMS, key in the following query:

SELECT BusinessEntityID, JobTitle 
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';

2. On the menu bar, click Query > Include Actual Execution Plan. Yes -> You can instead press Ctrl+M just like what the menu is telling you to accomplish the same.
Include Actual Execution Plan

3. You will then need to click on the Execute button or press F5 to get the query executed to view the query results in addition to the execution plan.

4. If you then click on the operator “Clustered Index Scan” and then press F4 to view the properties, you will see the keywords “Actual” and also “Estimated” in the properties menu:
Actual Query Plan

For performance tuning exercise, you should know that for the select queries, you can use the actual execution plans in the production environment. If the queries include insert, update, and delete, if you should only use estimated query execution plan. If you need more thorough testing, you should restore a copy of the production database to a development environment.