Systems Engineering and RDBMS

“Optimize For” enhancement in SQL Server 2008

Posted by decipherinfosys on February 16, 2009

We had covered the “Optimize For” query hint in one of blog post before and had covered what it does and where it can be of use.  We had also discussed bind variable peeking (Oracle lingo) and parameter sniffing (SQL Server lingo).  In this post, we are going to look at an enhancement done in SQL Server 2008 release for the “optimize for” query hint.

The enhancement in SQL Server 2008 is to “optimize for unknown parameter values”.   Using this, the optimizer will look at all the available statistical data in order to determine what the values for the variables should be in order to generate an optimal query plan.  So, if we take the same example as we had taken in our previous blog post on “optimize for” query hint feature:

USE DECIPHERTEST
GO

CREATE TABLE DECIPHER_TEST (COL1 INT IDENTITY NOT NULL, STAT_CODE INT NOT NULL)
GO

CREATE INDEX DECIPHER_TEST_IND_1 ON DECIPHER_TEST (STAT_CODE)
GO

SET NOCOUNT ON
GO

DECLARE @I INT
SET @I = 1
WHILE (@I <= 1000000)
BEGIN
IF @I <=10
INSERT INTO DECIPHER_TEST (STAT_CODE) VALUES (0)

IF (@I > 10 AND @I <= 10000)
INSERT INTO DECIPHER_TEST (STAT_CODE) VALUES (30)

IF (@I > 10000 AND @I <= 1000000)
INSERT INTO DECIPHER_TEST (STAT_CODE) VALUES (90)

SET @I = @I + 1
END
GO

And running the same set of SQLs as we had shown in that post, we can see that when we first use the the value of 90, the execution plan uses the full table scan path which is the right thing to do but then when we switch the parameter value to be 0, it still uses the same plan since it was already in the cache – in the case of heavily skewed data, this is a likely issue.  There are many ways to resolve this – one was what we had mentioned in that post – using the “optimize for” query hint, usage of plan guides, recompiling (not advisable but can be suitable in certain scenarios), using a constant (again, not advisable), using hints like index hints to force a particular plan (helps in certain scenarios).  Or you can now use the optimize for unknown feature:

SET SHOWPLAN_TEXT ON
GO

DECLARE @STAT_CODE INT
SET @STAT_CODE = 0

SELECT * FROM DECIPHER_TEST WHERE STAT_CODE = @STAT_CODE OPTION (OPTIMIZE FOR (@STAT_CODE UNKNOWN))
GO

It then used the statistical data to determine a value in order to form the plan – the actual execution did use the value that got passed into the query but the plan generation could use another value that the optimizer deemed fit based on the statistics gathered based on prior executions.

As you can see, using this hint with the unknown keyword, helps in writing queries which retain the benefits of parameterized queries and at the same time combating any issues caused by the skewness of the data.  However, instead of using it blindly, see first whether it applies to that corner case in your application and in case it does, you now have another arrow in your quiver.

One Response to ““Optimize For” enhancement in SQL Server 2008”

  1. […] https://decipherinfosys.wordpress.com/2009/02/16/optimize-for-enhancement-in-sql-server-2008/ […]

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: