Skip to content

Instantly share code, notes, and snippets.

@SQLKiwi SQLKiwi/ScanSeek.sql Secret
Created Nov 26, 2018

Embed
What would you like to do?
Scans and Seeks
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';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.