“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:
CREATE TABLE DECIPHER_TEST (COL1 INT IDENTITY NOT NULL, STAT_CODE INT NOT NULL)
CREATE INDEX DECIPHER_TEST_IND_1 ON DECIPHER_TEST (STAT_CODE)
SET NOCOUNT ON
DECLARE @I INT
SET @I = 1
WHILE (@I <= 1000000)
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
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
DECLARE @STAT_CODE INT
SET @STAT_CODE = 0
SELECT * FROM DECIPHER_TEST WHERE STAT_CODE = @STAT_CODE OPTION (OPTIMIZE FOR (@STAT_CODE UNKNOWN))
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.