Category Archives: Plan cache

OPTIMIZE for UNKNOWN

Performance Optimisation, Plan cache October 30, 2013 Leave a comment

In this article I will discuss about the ‘Optimize for Unknown’ query hint that was introduced in SQL Server 2008 and how we can use it.

So what is ‘OPTIMIZE for UNKNOWN’?
‘OPTIMIZE for UNKNOWN’ directs the query optimizer to use the standard rules it uses if no parameter values have been passed.The optimizer will use statistical data instead of the initial values for all variables when the query is compiled and optimized.

Now the big question is how does this help? Well ‘OPTIMIZE for UNKNOWN’ is one of the mechanisms which helps us to avoid parameter sniffing.

So what is parameter sniffing?
I will explain this with an example. I will use the Adventureworks2012 database for demonstration purpose on SQL Server 2012. However you can use any other version but not lower than SQL Server 2008.

Let us run the below query on the database and observe the output and execution plan

USE AdventureWorks2012
GO
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 744

Row Count: 13
ofu1Now lets take a look at one of the aspects of the execution plan of the above query. Make a note that the actual number of rows is 13 and the estimated number of rows is 44.
ofu2Let us have a look at the plan xml for the run time values and compiled time values:
ofu3Now let us run another query with a different value.

USE AdventureWorks2012
GO
SELECT * FROM Sales.SalesOrderDetail
Where ProductID = 707

Let us observe the row count, execution plan and plan xml values as we did for the previous query
Row Count:
ofu4Execution plan: Make a note that the estimated number of rows is still 44.5 which was for the parameter used in the previous query i.e 744
ofu5Plan XML for Compiled and Runtime values: Make a note here that the compiled value used by the query optimizer here to create the plan is 744 which was the value that the previous query used.
ofu6This is called parameter sniffing where the optimizer sniffs the current parameter value during compilation.

Can this cause trouble?
Yes, it can. When a non-similar parameter is passed when a plan is compiled for the first time, the plan that the optimizer will find in the memory may not be the optimal one for that parameter passed. This will can result in a plan that is suboptimal and can cause a devastating effect on performance.

So how can we use ‘OPTIMIZE for UNKNOWN’ to avoid parameter sniffing. let us understand that with the below example.

We are going to run the above 2 queries here also but with the Optimize query hint in the second query:

USE AdventureWorks2012
GO
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 744
go
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 707
OPTION (OPTIMIZE FOR UNKNOWN)--Query Hint

Observations post execution of the two queries:
1)  It creates 2 different plans:
ofu72) Estimated and Actual row calculation for the two queries respectively:
Query 1:
ofu8Query 2:
ofu93) Compiled value and RunTime value from Plan xml:
Query 1: With parameter value 744
ofu10  Query 2: With parameter value 707 and query hint (Optimize for Unknown)
ofu11 So with the use of the query hint the optimizer does not use the value that it got from the cache but generates a new plan based on the available stats in the database.

What are the kind of stored procedures that can be a victim of Parameter Sniffing?
A)  SP’s which has optional parameters
B)  SP’s which has parameters in range operators.

Advertisement

How to find the last execution details of a stored procedure in SQL Server

Plan cache, SQL Server 2012 October 23, 2013 1 Comment

In this article I will demonstrate how we can find the last execution details of a stored procedure in SQL Server 2012 or SQL Server 2008 R2.With DMV’s getting modified in these 2 editions of SQL Server, the amount of information we can obtain from the plan cache can be handy for investigation purpose.
When a stored procedure is created or recompiled, a plan for the same is created and cached in the plan cache. Whenever the same stored procedure is executed, the plan is recalled from the SQL memory for execution purpose. The details of an execution is stored internally in SQL Server which can be fetched via the dynamic management views.

A key element in this process is that the plan has to be in the plan cache for us to derive the information. By any chance if the SQL Server gets restarted or the plan cache is cleared then the information would not be available.

Let us see how we can fetch the execution details with an example. I have used the Adventureworks2012 database for demonstration purpose
SQL Server 2012:
Execute the below mentioned SP on SQL Server 2012

USE [AdventureWorks2012]
GO
EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 135

The execution produces 4 rows
ED1Now let us find the execution details of this stored procedure from the plan cache. Open another Query editor and execute the below mentioned query. The query searches the plan cache for the execution details of the stored procedure, whose name we have filtered in the last line of the query

SELECT qs.sql_handle,qs.creation_time,qs.last_execution_time,
qp.dbid,qs.execution_count,qs.last_rows,qs.last_logical_reads,
qs.last_logical_writes,qs.last_physical_reads,st.text
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 st.text like '%USPGET%'---filter by name of the SP

The output would be as follows:
ED2Now if we observe the outcome we would find the following information very handy
1. Last execution Time
2. Execution Count
3. Last_rows: This depicts the number of rows as output when the SP executed last.
4. Last_Logical_Reads
5. Last_Logical_Writes
6. Last_Physical_Reads

In SQL Server 2008 R2, the column that would be missing is Last_rows. So the query that you can use in SQL Server 2008 R2 would be as below

SELECT qs.sql_handle,qs.creation_time,
qs.last_execution_time,
qp.dbid,qs.execution_count,qs.last_logical_reads,
qs.last_logical_writes,qs.last_physical_reads,st.text
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 st.text like '%Name of Stored Proc%'
Related articles

How to find and remove a query plan from the plan cache in SQL Server

Plan cache October 14, 2013 1 Comment

In this article I will try to explain how we can obtain the plan of a SQL query from the plan cache and also how we can remove the plan from the cache keeping other plans intact. Before we see how we can achieve the above we will understand few basic things on plan cache.
Structure
1) What is a query plan: A query plan is an organized and ordered set of steps used by the SQL engine to fetch data. Every query has a query plan cached in the SQL memory
2) What is a plan cache: Apart from Buffer pool which is the biggest consumer of SQL Server memory, SQL Server allocates a proportion of this memory for use of storing the query plans. This storage of query plans in the SQL memory is called plan cache. The storage is a volatile storage and if the SQL server is restarted then all the plans are removed from this cache.
3) What is a plan handle: A plan handle is an unique identifier of the plan.

Let us now see how we can locate the query plan of an already executed query:
Step 1: In this example I have executed the below query on the Adventureworks2012 database.

SELECT [BusinessEntityID],[Title]
,[FirstName],[MiddleName]
,[LastName],[Suffix]
,[JobTitle],[PhoneNumber]
,[PhoneNumberType],[EmailAddress]
,[EmailPromotion],[AddressLine1]
,[AddressLine2],[City]
,[StateProvinceName],[PostalCode]
,[CountryRegionName],[AdditionalContactInfo]
FROM [AdventureWorks2012].[HumanResources].[vEmployee]
WHERE PostalCode = '98104'

Step 2: Now let us find the query in the plan cache. We will use the below query to find the plan of the query from the plan cache. Observe carefully that in line number 11 we have used a filter to search all the queries in the plan cache which has the word ‘PostalCode’ which is also a part of our executed code on the Adventureworks2012 database.

select a.plan_handle,query_plan,text,
execution_count,size_in_bytes,
creation_time,usecounts,
last_execution_time,execution_count,
total_elapsed_time,last_elapsed_time
from sys.dm_exec_cached_plans a
inner join sys.dm_exec_query_stats b
on a.plan_handle=b.plan_handle
cross apply sys.dm_exec_sql_text(b.sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(a.plan_handle) as query_plan
where text LIKE '%PostalCode%'

Snapshot of the output below
Qp1
Step 3: The highlighted text is the query that we ran and you can see the the query plan associated with it on the second column. Click on the plan and it will open on a new window.
qp2
qp3
So now we know how we can find the plan of an already executed query from the cache. In short use the above mentioned query and use a string from the executed query as filter in the above given query.

Now let us see how we can remove the query plan from the plan cache
Step 1: Find the plan handle of the query plan.
qp4
Step 2: Execute the below code to drop the plan from the plan cache

DBCC freeproccache(0x06000100A8CEE12640BB20D50200000001000000000000000000000000000000000000000000000000000000)

This will only drop that particular plan from the plan cache.Be very careful that you do not practice this on a production environment without understanding the implications of the absence of the query plan which you dropped.

I hope this article was helpful in finding and removing query plans from the plan cache without impacting other plans.

Related articles

Mathematics behind Query Recompilation

Plan cache August 17, 2013 Leave a comment

We DBA’s have all experienced query recompilation in our SQL Server environment. The entire journey of a query, right from inception till the formation of the Query Plan followed by execution is very interesting and involves a lot of internal processes. Recompilation is nothing but the re-initiation of compilation of a query when a certain compilation check fails. So what is the underlying algorithm or mathematics that the SQL server uses to decide whether to recompile or not. Here is how it works:

ABS( colmodctr(current) – colmodctr(snapshot)) ) >= RT

1) RT = Each column has a recompilation threshold (RT) associated with it. RT is a function of the number of rows in a table. We will discuss how RT is calculated later in this article.

2) colmodctr = This counter tracks the number of modifications that a table has undergone. The counter is not transactional in nature, which means if 1000 rows were inserted into the table and then rolled back then the counter value would not be rolled back.

3) colmodctr(current) is the current value of the counter and colmodctr(snapshot) is the value of the counter when the query plan was last compiled.

So when this absolute value crosses the RT, it is then that the query is recompiled. The next quetion is how does the engine decides or calculates the RT. Here is how it is done:

1) For permanent table:If n<=500 then RT = 500.If n>500 then RT = 500 + 0.20 * n.

2) For temporary table: If n<6 then RT = 6.If n between 6 and 500 then RT = 500. If n>500 then RT = 500 + 0.20*n

3) For Table variable: RT does not exist. They are not recompiled.

Here n stands for numbers of rows in the table.

I hope this concise article was helpful in understanding the decision making process for Recompilation.

%d bloggers like this: