Performance improvement for parameterized queries in SQL Server 2008: Optimze for Unknown

Parameterized queries are a well known SQL Server practice. This technique ensures caching and reuse of existing query execution plans (instead of constantly compiling new plans). This will improve performance of your application. However it can have a negative impact if the initial parameter is not representative of the values passed in the subsequent re-execution of the query. For the initial query, SQL Server compiled and cached a 'good' plan for the first parameter value. However this 'good' cached execution plan is performing very badly for all subsequent parameters values. The overall performance of this parameterized query depends on the parameter which is used for the first time. This is out of the control of the developer.

In SQL 2008 a new query hint option is introduced which is called: Optimize for Unknown

This hint directs the query optimizer to use the standard algorithms it has always used if no parameters values had been passed to the query at all. In this case the optimizer will look at all available statistical data to reach a determination of what the values of the local variables used to generate the query plan should be, instead of looking at the specific parameter values that were passed to the query by the application.

To further explain I will use a script which can be used in SQL 2008

CREATE DATABASE TEST

SET STATISTICS PROFILE OFF
GO
USE TEST
GO
-- Create a table with content.
CREATE TABLE Test(COL1 INT, COL2 CHAR(4),COL3 VARCHAR(25))
DECLARE @COL1 int, @COL2 INT
SET @COL1 = 0
WHILE @COL1 < 10000
BEGIN
INSERT INTO Test(col1,col2,col3) VALUES (@COL1,'Scan','THIS IS A TEST')
SET @COL1 = @COL1 + 1
END

SET @COL2 = @COL1
WHILE @COL2 < @COL1 + 11
BEGIN
INSERT INTO Test(COL1,COL2,COL3) VALUES (@COL2,'Seek','THIS IS A TEST')
SET @COL2 = @COL2 + 1
END
GO

-- Create indexes on generated table
CREATE UNIQUE CLUSTERED INDEX IX_CLUS ON TEST(COL1)
CREATE INDEX IX_2 ON TEST(COL2)
GO

DBCC SHOW_STATISTICS(TEST,IX_2) WITH HISTOGRAM
-- Show statistics for index IX_2.
-- How many times do the values for Column2 occur.
-- 'Seek' 11 times
-- 'Scan' 10000 times
GO

-- Stored procedure with SQL 2005\2000 behavior
CREATE PROCEDURE SLAP (@COL2 CHAR(4)) AS
SELECT COL3
FROM Test
WHERE COL2 = @COL2
GO

-- Stored procedure with SQL 2008
--Optimize for Unknown behavior
CREATE PROCEDURE COOL (@COL2 CHAR(4)) AS
SELECT COL3
FROM Test
WHERE COL2 = @COL2 OPTION (OPTIMIZE FOR (@COL2 UNKNOWN))
GO

-- Stored procedure with SQL 2008
-- Optimize for configured parameter value behavior
CREATE PROCEDURE COOLParDef (@COL2 CHAR(4)) AS
SELECT COL3
FROM Test
WHERE COL2 = @COL2 OPTION (OPTIMIZE FOR (@COL2 = 'Seek'))
GO

-- Display execution plan after execution
SET STATISTICS PROFILE ON

-- Clear procedure cache
DBCC FREEPROCCACHE

-- Start demo SQL 2000/2005 behavior
EXEC SLAP Seek -- Parameter 'Seek' is leading for query plan.
--It will use an Index Seek

GO

EXEC SLAP Scan
-- Stored procedure SLAP is executed for parameter value 'Scan'
--with an Index Seek (cached)


GO
-- Clear procedure cache
DBCC FREEPROCCACHE
EXEC SLAP Scan
-- Parameter 'Scan' is leading for query plan.
--It will use an Index Scan
GO
EXEC SLAP Seek
-- Stored procedure SLAP is executed for parameter value 'Seek'
--with an Index Scan (cached)
GO

DBCC FREEPROCCACHE
GO
EXEC COOL Seek
-- Stored procedure COOL is executed for parameter value 'Seek'
-- with an Index Scan.
-- Parameter value 'Seek' is ignored and the statistics are used. In this case value scan occurs 10.000 times.
-- Value Seek occurs only 11 times.
-- For overall performance of this query the Index Scan is the best.
GO
EXEC COOL Scan
GO

DBCC FREEPROCCACHE
GO
EXEC COOLParDef Scan
GO
-- Stored procedure is compiled with OPTIMIZE FOR (@COL2 = 'Seek'
-- Execution plan is always based on the parameter value 'Seek'
GO
EXEC COOLParDef Seek

-- Clean up all created stuff
USE MASTER
GO
DROP DATABASE TEST


Conclusion: This Optimize for Unknown option is a performance improvement. However I do not understand why this option is not default bahviour. Has somebody experience (good or bad) with this parameter?