Category Archives: Performance Optimisation

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

Optimize for adhoc workloads – Server Configuration Option – How it works

Performance Optimisation September 12, 2013 Leave a comment

In this article I want to demonstrate with an example how this feature works when configured. let us begin by first understanding what is “Optimize for adhoc workloads” .

This feature helps improve the plan cache’s efficiency to handle workloads that contain many single use of ad hoc batches. When set to 1, the engine keeps a small compiled plan stub in the plan cache when an ad hoc  batch is compiled for the first time, instead of the full compiled plan. This helps in releasing memory pressure by not allowing the plan cache to become filled with compiled plans that will not be used again.

This compiled plan stub enables the SQL engine to decide that an ad hoc batch has been compiled previously but has only stored a compiled plan stub. So when this batch is compiled or executed again, the SQL engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache. This will only affect new plans. Plans that are already in the plan cache are unaffected.

let us understand this with an example
Step 1: Clear the Buffer cache and plan cache
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Step 2: Run an Ad hoc query
USE AdventureWorks2012
GO
SELECT * FROM Production.Document WHERE DocumentLevel = 2
GO
WL1Step 3: Find if the query is cached
SELECT usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
 text like ‘%SELECT * FROM Production.Document%’
ORDER BY usecounts DESC;
GO
WL2Step 4: Enable the “Optimize for ad hoc workloads”
SP_CONFIGURE ‘show advanced options’,1
RECONFIGURE
GO
SP_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO

Step 5: Repeat Step 1
Step 6: Repeat Step 2
Step 7: Repeat Step 3
You will find that the query is not cached
WL3Step 8: Lets clear the cache and buffers again. Repeat step 1
Step 9: We need to run the ad hoc more than once now. Execute the below
SELECT * FROM Production.Document WHERE DocumentLevel = 2
GO 3
WL4Step 10: Now lets see if the query is cached
Step 11: Repeat step 3.
WL6 Yes we do see that when the same batch has multiple executions, the query is indeed cached.

To summarize the utility of this feature we can say that it helps SQL Server memory by not allowing to store Ad hoc query plans which are for single use only.

%d bloggers like this: