Query Optimizer Deep Dive Scripts

01 Bound Tree.sql

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC TRACEON(3604);
GO
-- Test query
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM 
    Production.Product AS p,
    Production.ProductInventory AS inv
WHERE
    inv.ProductID = p.ProductID
    AND p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name;
GO
-- Input tree (ISO-89)
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM 
    Production.Product AS p,
    Production.ProductInventory AS inv
WHERE
    inv.ProductID = p.ProductID
    AND p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name
OPTION (RECOMPILE, QUERYTRACEON 8605);
GO
-- Input tree (ISO-92)
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
WHERE
    p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name
OPTION (RECOMPILE, QUERYTRACEON 8605);
GO
-- Forced index hint
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p WITH (INDEX([AK_Product_Name]))
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
WHERE
    p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name
OPTION (RECOMPILE);
GO
-- Hash join
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p WITH (INDEX([AK_Product_Name]))
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
WHERE
    p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name
OPTION (HASH JOIN, RECOMPILE);
GO

02 Simplification.sql

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC TRACEON(3604);
GO
-- Directly using a constant
SELECT TOP (10) *
FROM Production.Product AS p
WHERE p.ProductID < 30;
GO
-- Constant folding an expression
SELECT p.Name
FROM Production.Product AS p
WHERE p.Name LIKE SUBSTRING(LEFT(CHAR(ASCII(CHAR(68))), 1) + '%', 1, 2)
GO
-- Remove redundancy
SELECT TOP (10) * FROM
(
    SELECT * FROM 
    (
        SELECT * FROM
        (
            SELECT * FROM Production.Product AS p
            WHERE p.ProductID = 400
        ) AS q1
        WHERE q1.ProductID = 400
    ) AS q2
    WHERE q2.ProductID = 400)
AS q3
WHERE q3.ProductID = 400;
GO
-- Simplify domains
SELECT TOP (10) * 
FROM Production.Product AS p
WHERE p.ProductID BETWEEN 300 AND 400
AND p.ProductID BETWEEN 200 AND 500
AND p.ProductID BETWEEN 400 AND 600;
GO
-- Computed column matching
DECLARE @Example AS TABLE
(
    col1 int NULL, 
    col2 int NULL, 
    col3 AS col1 * col2 UNIQUE CLUSTERED
);

SELECT * FROM @Example AS e WHERE col3 = 5;
SELECT * FROM @Example AS e WHERE col1 * col2 = 5;
SELECT * FROM @Example AS e WHERE col2 * col1 = 5;
GO
-- Remove unnecessary joins
SELECT
    th.ProductID,
    SUM(th.ActualCost)
FROM Production.TransactionHistory AS th
JOIN Production.Product AS p ON
    p.ProductID = th.ProductID
GROUP BY
    th.ProductID;
GO
-- Outer join to join (null rejection)
SELECT * 
FROM Production.Product AS p
LEFT JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
WHERE th.ProductID < 10;
GO
-- Complex example combining multiple simplifications
WITH Complex AS
(
    SELECT
        pc.ProductCategoryID, pc.Name AS CatName,
        ps.ProductSubcategoryID, ps.Name AS SubCatName,
        p.ProductID, p.Name AS ProductName,
        p.Color, p.ListPrice, p.ReorderPoint,
        pm.Name AS ModelName, pm.ModifiedDate
    FROM Production.ProductCategory AS pc
    FULL JOIN Production.ProductSubcategory AS ps ON
        ps.ProductCategoryID = pc.ProductCategoryID
    FULL JOIN Production.Product AS p ON
        p.ProductSubcategoryID = ps.ProductSubcategoryID
    FULL JOIN Production.ProductModel AS pm ON
        pm.ProductModelID = p.ProductModelID
)
--SELECT * FROM Complex;
SELECT c.ProductID, c.ProductName
FROM Complex AS c
WHERE c.ProductName LIKE N'G%';

03 Trivial Plan.sql

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC TRACEON(3604);
GO
-- Trivial plan
SELECT p.ProductID
FROM Production.Product AS p
WHERE p.Name = N'Blade';
GO
-- Still trivial
SELECT
    p.Name,
    RowNumber =
        ROW_NUMBER() OVER (
            ORDER BY p.Name)
FROM Production.Product AS p
WHERE p.Name LIKE N'[A-G]%';
GO
-- 'Subquery' prevents a trivial plan
SELECT (SELECT p.ProductID)
FROM Production.Product AS p
WHERE p.Name = N'Blade';
GO
-- Inequality
SELECT p.ProductID
FROM Production.Product AS p
WHERE p.Name <> N'Blade';
GO
-- Cost threshold for parallelism
EXECUTE sys.sp_configure
    @configname = 'show advanced options',
    @configvalue = 1;
RECONFIGURE;

EXECUTE sys.sp_configure
    @configname = 'cost threshold for parallelism',
    @configvalue = 0;
RECONFIGURE;
GO
-- Full optimization
SELECT p.ProductID
FROM Production.Product AS p
WHERE p.Name = N'Blade';
GO
-- Reset to default
EXECUTE sys.sp_configure
    @configname = 'cost threshold for parallelism',
    @configvalue = 5;
RECONFIGURE;
GO
-- Check
SELECT c.[description], c.value_in_use
FROM sys.configurations AS c 
WHERE c.name = N'cost threshold for parallelism';
GO

04 CBO Input Tree.sql

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC TRACEON(3604);
GO
-- Input tree to CBO
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
WHERE
    p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name
OPTION (RECOMPILE, QUERYTRACEON 8606);
GO
SELECT decoded =
    NCHAR(91) +
    NCHAR(65) +
    NCHAR(45) +
    NCHAR(71) +
    NCHAR(93) +
    NCHAR(37);

05 Segment Top.sql

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC TRACEON(3604);
GO
DECLARE @x varchar(30) = 0x444243432052554C454F46462028274275696C644762546F7027293B0000;
EXECUTE (@x);
GO
-- Without segment top
SELECT
    inv.ProductID,
    inv.LocationID,
    inv.Shelf,
    inv.Bin,
    inv.Quantity
FROM Production.ProductInventory AS inv
WHERE inv.Quantity =
(
    SELECT MIN(inv2.Quantity)
    FROM Production.ProductInventory AS inv2
    WHERE inv2.Shelf = inv.Shelf
    AND inv2.Bin = inv.Bin
)
OPTION (RECOMPILE);
GO
DECLARE @x varchar(30) = 0x444243432052554C454F4E2028274275696C644762546F7027293B000000;
EXECUTE (@x);
GO
-- With segment top
SELECT
    inv.ProductID,
    inv.LocationID,
    inv.Shelf,
    inv.Bin,
    inv.Quantity
FROM Production.ProductInventory AS inv
WHERE inv.Quantity =
(
    SELECT MIN(inv2.Quantity)
    FROM Production.ProductInventory AS inv2
    WHERE inv2.Shelf = inv.Shelf
    AND inv2.Bin = inv.Bin
)
OPTION (RECOMPILE);
GO

06 Initial Memo.sql

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC TRACEON(3604);
GO
-- Initial memo contents
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
WHERE
    p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name
OPTION (QUERYTRACEON 8608);

07 Phases.sql

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC TRACEON(3604);
GO
-- Supported DMV
SELECT
    deqoi.[counter],
    deqoi.occurrence
FROM sys.dm_exec_query_optimizer_info AS deqoi
WHERE
    deqoi.[counter] IN
    (
        N'trivial plan',
        N'search 0',
        N'search 1',
        N'search 2'
    );
GO
-- Search 0 (TP)
SELECT
    p.ProductID,
    inv.Shelf,
    inv.Bin,
    l.Name
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
JOIN Production.Location AS l ON
    l.LocationID = inv.LocationID
WHERE
    p.Name = N'Blade'
OPTION (QUERYTRACEON 8675);
GO
-- Search 1 (QP)
SELECT
    p.Name,
    average_qty = AVG(th.Quantity)
FROM Production.Product AS p
JOIN Production.TransactionHistory AS th ON
    th.ProductID = p.ProductID
GROUP BY
    p.Name
OPTION (QUERYTRACEON 8675);
GO
-- Search 0, then search 1 twice (parallel)
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS th
CROSS JOIN Production.TransactionHistory AS th2
CROSS JOIN Production.TransactionHistory AS th3
OPTION (QUERYTRACEON 8675);
GO
-- Search 1 twice (parallel)
SELECT COUNT_BIG(*)
FROM Production.TransactionHistory AS th
CROSS JOIN dbo.bigTransactionHistory AS bth
OPTION (QUERYTRACEON 8675);
GO
-- Time out
SELECT *
FROM HumanResources.vEmployee AS ve
JOIN HumanResources.vEmployeeDepartmentHistory AS vedh ON
    vedh.BusinessEntityID = ve.BusinessEntityID
OPTION (QUERYTRACEON 8675);
GO
-- Search 2
SELECT * FROM HumanResources.vJobCandidate AS vjc
JOIN HumanResources.vEmployeeDepartmentHistory AS vedh ON
    vedh.BusinessEntityID = vjc.BusinessEntityID
OPTION (QUERYTRACEON 8675);

08 Undocumented.sql

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC TRACEON(3604);
GO
--                                        -- 0.0295
--DBCC RULEOFF ('GbAggBeforeJoin');       -- 0.0351
--DBCC RULEOFF ('JNtoIdxLookup');         -- 0.0431
--DBCC RULEOFF ('JNtoSM');                -- 0.0500
--DBCC RULEOFF ('SelIdxToRng');           -- 0.0531
--DBCC RULEOFF ('GetIdxToRng');           -- 0.0598
--DBCC RULEOFF ('SelOnJN');               -- 3.1244
--DBCC RULEOFF ('BuildSpool');            -- 3.5980
--DBCC RULEOFF ('NormalizeGbAgg');        -- 3.5980
GO
SET STATISTICS XML OFF;
GO
SELECT * INTO #q1 FROM sys.dm_exec_query_transformation_stats AS deqts;
GO
SELECT * INTO #q2 FROM sys.dm_exec_query_transformation_stats AS deqts;
GO
DROP TABLE #q1, #q2;
GO
SELECT * INTO #q1 FROM sys.dm_exec_query_transformation_stats AS deqts;
GO
SET SHOWPLAN_XML ON;
GO
-- TEST QUERY
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
WHERE
    p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name;
GO
SET SHOWPLAN_XML OFF;
GO
SELECT * INTO #q2 FROM sys.dm_exec_query_transformation_stats AS deqts;
GO
SELECT
    q.name,
    q.promised,
    q.built_substitute,
    q.succeeded,
    q2.succeeded
FROM #q1 AS q 
JOIN #q2 AS q2 ON
    q.name = q2.name
    AND q.succeeded < q2.succeeded;
GO
DBCC RULEON ('GbAggBeforeJoin');
DBCC RULEON ('JNtoSM');
DBCC RULEON ('JNtoIdxLookup');
DBCC RULEON ('SelOnJN');
DBCC RULEON ('BuildSpool');
DBCC RULEON ('SelIdxToRng');
DBCC RULEON ('GetIdxToRng');
DBCC RULEON ('NormalizeGbAgg');
GO
DROP TABLE #q1, #q2;

09 Final.sql

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;
DBCC FREEPROCCACHE;
DBCC TRACEON(3604);
GO
-- Final memo
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
WHERE
    p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name
OPTION (RECOMPILE, QUERYTRACEON 8615);
GO
-- Output tree
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
WHERE
    p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name
OPTION (RECOMPILE, QUERYTRACEON 8607);
GO
-- Final form
SELECT
    p.Name,
    Total = SUM(inv.Quantity)
FROM Production.Product AS p
JOIN Production.ProductInventory AS inv ON
    inv.ProductID = p.ProductID
WHERE
    p.Name LIKE N'[A-G]%'
GROUP BY
    p.Name
OPTION (RECOMPILE, QUERYTRACEON 7352);