In Optimizing a Suboptimal Query Plan (InstantDoc #94775) I described a query for which the default plan that the optimizer produced was slower than one that was forced with a hint. Recently I received comments about the article from Will Alber from the UK who attended one of my classes. Will had very interesting findings about the scenario that lead to a more broad discussion about optimization and caching in general. Will found a certain pattern in my sample data that affected the efficiency of the plan, and his findings are quite intriguing. I’ll first briefly describe the scenario in the aforementioned article, then introduce Will’s findings, and then discuss the implications.
Scenario in Article Optimizing a Suboptimal Query Plan (InstantDoc #94775)
The aforementioned article described the following scenario:
You have a table T1 with columns col1 (INT), col2 (INT), and filler (CHAR(200)). The table has a clustered index on col1 and a nonclustered index on col2. The table is populated with 1,000,000 rows using the following code:
SET NOCOUNT ON;
IF DB_ID('testdb') IS NULL
CREATE DATABASE testdb;
GO
USE testdb;
GO
-- Create the function fn_split
IF OBJECT_ID('dbo.fn_nums', 'IF') IS NOT NULL
DROP FUNCTION dbo.fn_nums;
GO
CREATE FUNCTION dbo.fn_nums(@n AS INT)
RETURNS TABLE AS RETURN
WITH
C0 AS(SELECT 0 AS const UNION ALL SELECT 0),
C1 AS(SELECT 0 AS const FROM C0 AS A, C0 AS B),
C2 AS(SELECT 0 AS const FROM C1 AS A, C1 AS B),
C3 AS(SELECT 0 AS const FROM C2 AS A, C2 AS B),
C4 AS(SELECT 0 AS const FROM C3 AS A, C3 AS B),
C5 AS(SELECT 0 AS const FROM C4 AS A, C4 AS B),
C6 AS(SELECT 0 AS const FROM C5 AS A, C5 AS B)
SELECT TOP(@n) ROW_NUMBER() OVER
(ORDER BY const) AS n
FROM C6;
GO
-- Create the table T1
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL
DROP TABLE dbo.T1;