Scans and Seeks

ScanSeek.sql

USE master;
GO
IF DB_ID('ScansAndSeeks') IS NOT NULL
BEGIN
    DROP DATABASE ScansAndSeeks;
END;
GO
CREATE DATABASE ScansAndSeeks;
GO
USE ScansAndSeeks;
GO
ALTER DATABASE ScansAndSeeks
SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE ScansAndSeeks 
SET AUTO_CLOSE OFF,
    AUTO_SHRINK OFF,
    AUTO_CREATE_STATISTICS OFF,
    AUTO_UPDATE_STATISTICS OFF,
    PARAMETERIZATION SIMPLE,
    READ_COMMITTED_SNAPSHOT OFF,
    RESTRICTED_USER;
GO
CREATE PROCEDURE dbo.ResetTest
    @Partitioned bit = 'false'
AS
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID(N'dbo.Example', N'U') IS NOT NULL
    BEGIN
        DROP TABLE dbo.Example;
    END;

    -- Test table is a heap
    -- Non-clustered primary key on 'key_col'
    CREATE TABLE dbo.Example
    (
        key_col integer NOT NULL,
        [data] integer NOT NULL,
        padding char(100) NOT NULL 
            DEFAULT SPACE(100),

        CONSTRAINT [PK dbo.Example key_col]
            PRIMARY KEY NONCLUSTERED (key_col)
    );

    IF @Partitioned = 'true'
    BEGIN
        -- Enterprise, Trial, or Developer 
        -- required for partitioning tests
        IF  SERVERPROPERTY('EngineEdition') = 3
        BEGIN
            EXECUTE
            (
                '
                DROP TABLE dbo.Example;

                IF EXISTS 
                (
                    SELECT 1 
                    FROM sys.partition_schemes 
                    WHERE name = N''PS''
                )
                BEGIN
                    DROP PARTITION SCHEME PS;
                END;

                IF EXISTS 
                (
                    SELECT 1 
                    FROM sys.partition_functions 
                    WHERE name = N''PF''
                ) 
                BEGIN
                    DROP PARTITION FUNCTION PF;
                END;

                CREATE PARTITION FUNCTION PF (integer)
                AS RANGE RIGHT
                FOR VALUES (20, 40, 60, 80, 100);

                CREATE PARTITION SCHEME PS
                AS PARTITION PF
                ALL TO ([PRIMARY]);

                CREATE TABLE dbo.Example
                (
                    key_col integer NOT NULL,
                    data integer NOT NULL,
                    padding char(100) NOT NULL
                        DEFAULT SPACE(100),

                    CONSTRAINT [PK dbo.Example key_col]
                        PRIMARY KEY NONCLUSTERED (key_col)
                )
                ON PS (key_col);'
            );
        END
        ELSE
        BEGIN
            RAISERROR('Invalid SKU for partition test', 16, 1);
            RETURN;
        END;
    END
    ;

    -- Non-unique non-clustered index on the 'data' column
    CREATE NONCLUSTERED INDEX
        [IX dbo.Example data]
    ON dbo.Example ([data]);

    -- Add 100 rows
    INSERT dbo.Example
        WITH (TABLOCKX)
        (
            key_col, 
            [data]
        )
    SELECT
        key_col = V.number, 
        [data] = V.number
    FROM master.dbo.spt_values AS V
    WHERE
        V.[type] = N'P'
        AND V.number BETWEEN 1 AND 100;
END;
GO
CREATE PROCEDURE dbo.ShowStats
    @Partitioned bit = 'false'
AS
BEGIN
    -- Index Usage Stats DMV (QE)
    SELECT
        index_name = ISNULL(I.[name], I.[type_desc]),
        scans = IUS.user_scans, 
        seeks = IUS.user_seeks, 
        lookups = IUS.user_lookups
    FROM sys.dm_db_index_usage_stats AS IUS
    JOIN sys.indexes AS I
        ON I.[object_id] = IUS.[object_id]
        AND I.index_id = IUS.index_id
    WHERE
        IUS.database_id = DB_ID(N'ScansAndSeeks')
        AND IUS.[object_id] = OBJECT_ID(N'dbo.Example', N'U')
    ORDER BY
        I.index_id;

    -- Index Operational Stats DMV (SE)
    IF @Partitioned = 'true'
    BEGIN
        SELECT
            index_name = ISNULL(I.[name], I.[type_desc]),
            [partitions] = COUNT(IOS.partition_number),
            range_scans = SUM(IOS.range_scan_count),
            single_lookups = SUM(IOS.singleton_lookup_count)
        FROM sys.dm_db_index_operational_stats
        (
            DB_ID(N'ScansAndSeeks'), 
            OBJECT_ID(N'dbo.Example', N'U'), 
            NULL, 
            NULL
        ) AS IOS
        JOIN sys.indexes AS I
            ON I.[object_id] = IOS.[object_id]
            AND I.index_id = IOS.index_id
        GROUP BY
            I.index_id, -- Key
            I.[name], 
            I.[type_desc]
        ORDER BY
            I.index_id;
    END
    ELSE
    BEGIN
        SELECT
            index_name = ISNULL(I.[name], I.[type_desc]),
            range_scans = SUM(IOS.range_scan_count),
            single_lookups = SUM(IOS.singleton_lookup_count)
        FROM sys.dm_db_index_operational_stats
        (
            DB_ID(N'ScansAndSeeks'), 
            OBJECT_ID(N'dbo.Example', N'U'), 
            NULL, 
            NULL
        ) AS IOS
        JOIN sys.indexes AS I
            ON I.[object_id] = IOS.[object_id]
            AND I.index_id = IOS.index_id
        GROUP BY
            I.index_id, -- Key
            I.[name], 
            I.[type_desc]
        ORDER BY
            I.index_id;
    END;
END;
GO
CREATE PROCEDURE dbo.RunTest
    @SQL varchar(8000),
    @Partitioned bit = 'false'
AS
BEGIN
    -- No execution plan yet
    SET STATISTICS XML OFF;

    -- Reset the test environment
    EXECUTE dbo.ResetTest @Partitioned;

    -- Previous call will throw an error if a partitioned
    -- test was requested, but SKU does not support it
    IF @@ERROR = 0
    BEGIN
        -- IO statistics and plan on
        SET STATISTICS XML, IO ON;

        -- Test statement
        EXECUTE (@SQL);

        -- Plan and IO statistics off
        SET STATISTICS XML, IO OFF;

        EXECUTE dbo.ShowStats @Partitioned;
    END;
END;
GO
-- Table Scan plan : 1 scan on heap, 1 range scan on heap
EXECUTE dbo.RunTest 
    @SQL = 'SELECT * FROM Example';
GO
-- Index seek, single value in unique index : 1 seek on PK, 1 single lookup on PK
EXECUTE dbo.RunTest
    @SQL = 'SELECT key_col 
            FROM Example 
            WHERE key_col = 32';
GO
-- Index seek, single value in non-unique index : 1 seek on IX, 1 range scan on IX
EXECUTE dbo.RunTest 
    @SQL = 'SELECT data 
            FROM Example 
            WHERE data = 32';
GO
-- Index seek in non-unique index : 1 seek on IX, 2 range scans on IX
EXECUTE dbo.RunTest 
    @SQL = 'SELECT data 
            FROM Example 
            WHERE data = 32 
            OR data = 33';
GO
-- Index seek, range in non-unique index : 1 seek on IX, 1 range scan on IX
EXECUTE dbo.RunTest 
    @SQL = 'SELECT data 
            FROM Example 
            WHERE data BETWEEN 32 AND 33';
GO
-- Index seek, four values in unique index : 1 seek on PK, 4 single lookups on PK
EXECUTE dbo.RunTest 
    @SQL = 'SELECT key_col 
            FROM Example 
            WHERE key_col IN (2,4,6,8)';
GO
-- Index seek, (same four values) : 1 seek on PK, 1 range scan on PK (+ residual predicate)
EXECUTE dbo.RunTest 
    @SQL = 'SELECT key_col 
            FROM Example 
            WHERE key_col BETWEEN 2 AND 8 
            AND key_col % 2 = 0';
GO
-- Index seek + RID lookup: 1 seek on PK + 1 lookup on heap; 1 range scan on PK + 8 single lookups on heap
EXECUTE dbo.RunTest 
    @SQL = 'SELECT * FROM Example 
            WITH (INDEX([PK dbo.Example key_col])) 
            WHERE key_col BETWEEN 1 AND 8';
GO
-- Index seek + RID lookup: 1 seek on PK + 1 lookup on heap; 1 range scan on PK + 8 single lookups on heap (6 partitions)
EXECUTE dbo.RunTest 
    @SQL = 'SELECT * FROM Example 
            WITH (INDEX([PK dbo.Example key_col])) 
            WHERE key_col BETWEEN 1 AND 8',
    @Partitioned = 'true';
GO
-- Index seek, startup filter, no executions : 1 seek on PK, no SE activity
EXECUTE dbo.RunTest 
    @SQL = 'SELECT key_col 
            FROM Example 
            WHERE key_col = 8 
            AND @@TRANCOUNT < 0';
GO
-- Table scan : 1 scan on heap; 6 partitions, 6 range scans on IX
EXECUTE dbo.RunTest 
    @SQL = 'SELECT * FROM Example', 
    @Partitioned = 'true';
GO
-- Ordered seek on a heap (2008+ only)
-- 1 seek on heap, 2 range scans (one per partition) on heap
EXECUTE dbo.RunTest 
    @SQL = 'SELECT * 
            FROM Example 
            WHERE key_col BETWEEN 1 AND 30', 
    @Partitioned = 'true';
GO
-- Backward scan (2008+ only)
EXECUTE dbo.RunTest 
    @SQL = 'SELECT TOP (2) WITH TIES * 
            FROM Example 
            WHERE key_col BETWEEN 1 AND 50 
            ORDER BY $PARTITION.PF(key_col) DESC', 
    @Partitioned = 'true';