Task: to give a deeper look at the work of the query optimizer with the query hint OPTION (OPTIMIZE FOR ()) in SQL Server
Indices represent one of the best performance techniques. The operator seek is being used on large tables (the overhead by using B-Tree on small tables leads to poor performance). The operator scan is being used on small tables. An improper using of indices can have a negative impact on the performance.
The query optimizer usually selects the optimal execution plan with regard to statistical data. For this reason, the using of query hints is recommended only for advanced database developers or DBAs.
/* STEP 1 – BEGIN */
/* PREPARE – WITHOUT INDICES (BEGIN) */
USE [Test]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[QueryOptimizer]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QueryOptimizer](
[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[CountryRegionCode] [varchar](3) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET NOCOUNT ON
GO
/* PREPARE – WITHOUT INDICES (END) */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */
/* TEST 1: WITHOUT INDICES – SMALL TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northwest', 'US')
SET @rows += 1
END
GO
DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Canada', 'CA')
SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */
/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, TABLE SCAN, 1000 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 4,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, TABLE SCAN, 10 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 4,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TEST 1: WITHOUT INDICES – SMALL TABLE: SELECTIVITY (END) */
/* STEP 4 – BEGIN */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 4 – END */
/* TEST 2: WITHOUT INDICES – LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 5 – BEGIN */
-- Execution Plan: OFF
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Central', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Canada', 'CA')
SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 5 – END */
/* STEP 6 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, TABLE SCAN, 60000 of 60010 rows
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 231,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
GO
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, TABLE SCAN, 10 of 60010 rows
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 231,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
GO
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 6 – END */
/* TEST 2: WITHOUT INDICES – LARGE TABLE: SELECTIVITY (END) */
Source code 1: Tests without using indices
Figure 1: SSMS result – TEST 1: WITHOUT INDICES – SMALL TABLE: SELECTIVITY (US: LOW / CA: HIGH)
Figure 1: SSMS result – TEST 2: WITHOUT INDICES – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)
/* STEP 1 – BEGIN */
/* PREPARE – WITH INDICES (BEGIN) */
USE [Test]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[QueryOptimizer]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QueryOptimizer](
[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[CountryRegionCode] [varchar](3) NOT NULL,
CONSTRAINT [PK_QueryOptimizer] PRIMARY KEY CLUSTERED
(
[TerritoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_QueryOptimizer_CountryRegionCode]
ON [dbo].[QueryOptimizer]
(
[CountryRegionCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET NOCOUNT ON
GO
/* PREPARE – WITH INDICES (END) */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */
/* TEST 1: WITH INDICES – SMALL TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northwest', 'US')
SET @rows += 1
END
GO
DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Canada', 'CA')
SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */
/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 1000 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 6,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SCAN (Clustered), 10 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 6,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TEST 1: WITH INDICES – SMALL TABLE: SELECTIVITY (END) */
/* STEP 4 – BEGIN */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 4 – END */
/* TEST 2: WITH INDICES – LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 5 – BEGIN */
-- Execution Plan: OFF
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Central', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Canada', 'CA')
SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 5 – END */
/* STEP 6 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 60000 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SEEK (NonClustered), 10 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 22,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 6 – END */
/* TEST 2: WITH INDICES – LARGE TABLE: SELECTIVITY (END) */
Source code 2: Tests with using indices
Figure 2: SSMS result – TEST 1: WITH INDICES – SMALL TABLE: SELECTIVITY (US: LOW / CA: HIGH)
Figure 2: SSMS result – TEST 2: WITH INDICES – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)
/* STEP 1 – BEGIN */
/* PREPARE – WITH PRIMARY KEY INDEX ONLY (BEGIN) */
USE [Test]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[QueryOptimizer]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QueryOptimizer](
[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[CountryRegionCode] [varchar](3) NOT NULL,
CONSTRAINT [PK_QueryOptimizer] PRIMARY KEY CLUSTERED
(
[TerritoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET NOCOUNT ON
GO
/* PREPARE - WITH PRIMARY KEY INDEX ONLY (END) */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */
/* TEST 1: WITH PRIMARY KEY INDEX ONLY – SMALL TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northwest', 'US')
SET @rows += 1
END
GO
DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Canada', 'CA')
SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */
/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 1000 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 6,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SCAN (Clustered), 10 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 6,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TEST 1: WITH PRIMARY KEY INDEX ONLY – SMALL TABLE: SELECTIVITY (END) */
/* STEP 4 – BEGIN */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 4 – END */
/* TEST 2: WITH PRIMARY KEY INDEX ONLY – LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 5 – BEGIN */
-- Execution Plan: OFF
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Central', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Canada', 'CA')
SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 5 – END */
/* STEP 6 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 60000 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SCAN (Clustered), 10 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 6 – END */
/* TEST 2: WITH PRIMARY KEY INDEX ONLY – LARGE TABLE: SELECTIVITY (END) */
Source code 3: Tests with primary key index only
Figure 3: SSMS result – TEST 1: WITH PRIMARY KEY INDEX ONLY – SMALL TABLE: SELECTIVITY (US: LOW / CA: HIGH)
Figure 3: SSMS result – TEST 2: WITH PRIMARY KEY INDEX ONLY – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)
/* STEP 1 – BEGIN */
/* PREPARE – WITH NONCLUSTERED INDEX ONLY (BEGIN) */
USE [Test]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[QueryOptimizer]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QueryOptimizer](
[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[CountryRegionCode] [varchar](3) NOT NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_QueryOptimizer_CountryRegionCode]
ON [dbo].[QueryOptimizer]
(
[CountryRegionCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET NOCOUNT ON
GO
/* PREPARE – WITH NONCLUSTERED INDEX ONLY (END) */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */
/* TEST 1: WITH NONCLUSTERED INDEX ONLY – SMALL TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northwest', 'US')
SET @rows += 1
END
GO
DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Canada', 'CA')
SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */
/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, TABLE SCAN, 1000 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 4,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, TABLE SCAN, 10 of 1010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 4,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TEST 1: WITH NONCLUSTERED INDEX ONLY – SMALL TABLE: SELECTIVITY (END) */
/* STEP 4 – BEGIN */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 4 – END */
/* TEST 2: WITH NONCLUSTERED INDEX ONLY – LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 5 – BEGIN */
-- Execution Plan: OFF
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Central', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Canada', 'CA')
SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 5 – END */
/* STEP 6 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'US'
-- US: NONSELECTIVE VALUE, TABLE SCAN, 60000 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 231,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = 'CA'
-- CA: SELECTIVE VALUE, INDEX SEEK (NonClustered), 10 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 12,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 6 – END */
/* TEST 2: WITH NONCLUSTERED INDEX ONLY – LARGE TABLE: SELECTIVITY (END) */
Source code 4: Tests with non-clustered key index only
Figure 4: SSMS result – TEST 1: WITH NON-CLUSTERED INDEX ONLY – SMALL TABLE: SELECTIVITY (US: LOW / CA: HIGH)
Figure 4: SSMS result – TEST 2: WITH NON-CLUSTERED INDEX ONLY – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)
/* STEP 1 – BEGIN */
/* PREPARE – Query Hint: OPTION (OPTIMIZE FOR()) (BEGIN) */
USE [Test]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[QueryOptimizer]') AND type in (N'U'))
BEGIN
DROP TABLE [dbo].[QueryOptimizer]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[QueryOptimizer](
[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[CountryRegionCode] [varchar](3) NOT NULL,
CONSTRAINT [PK_QueryOptimizer] PRIMARY KEY CLUSTERED
(
[TerritoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_QueryOptimizer_CountryRegionCode]
ON [dbo].[QueryOptimizer]
(
[CountryRegionCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET NOCOUNT ON
GO
/* PREPARE – Query Hint: OPTION (OPTIMIZE FOR()) (END) */
/* CLEAN (BEGIN) */
TRUNCATE TABLE dbo.QueryOptimizer
GO
/* CLEAN (END) */
/* STEP 1 – END */
/* TESTS: WITH Query Hint – OPTION (OPTIMIZE FOR())
- LARGE TABLE: SELECTIVITY (BEGIN) */
/* STEP 2 – BEGIN */
-- Execution Plan: OFF
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Northeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Central', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southwest', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 1001 -- 1000 rows
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Southeast', 'US')
SET @rows += 1
END
GO 10 -- 10 * 1000 rows
DECLARE @rows INT = 1
WHILE @rows < 2 -- 1 row
BEGIN
INSERT INTO dbo.QueryOptimizer (Name, CountryRegionCode)
VALUES('Canada', 'CA')
SET @rows += 1
END
GO 10 -- 10 * 1 rows
/* STEP 2 – END */
/* STEP 3 – BEGIN */
-- Execution Plan: ON
SET STATISTICS IO ON
GO
SET STATISTICS TIME ON
GO
DECLARE @WhereValue VARCHAR(50)
SET @WhereValue = 'US'
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = @WhereValue
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 60000 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
DECLARE @WhereValue VARCHAR(50)
SET @WhereValue = 'CA'
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = @WhereValue
-- CA: SELECTIVE VALUE, INDEX SCAN (Clustered), 10 of 60010 rows
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
DECLARE @WhereValue VARCHAR(50)
SET @WhereValue = 'US'
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = @WhereValue
-- US: NONSELECTIVE VALUE, INDEX SCAN (Clustered), 60000 of 60010 rows
OPTION (OPTIMIZE FOR (@WhereValue = 'US'))
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 223,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
DECLARE @WhereValue VARCHAR(50)
SET @WhereValue = 'CA'
SELECT *
FROM dbo.QueryOptimizer
WHERE [CountryRegionCode] = @WhereValue
-- CA: SELECTIVE VALUE, INDEX SEEK (NonClustered), 10 of 60010 rows
OPTION (OPTIMIZE FOR (@WhereValue = 'CA'))
GO
/*
Table 'QueryOptimizer'. Scan count 1, logical reads 22,
physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
*/
SET STATISTICS IO OFF
GO
SET STATISTICS TIME OFF
GO
/* STEP 3 – END */
/* TESTS: WITH Query Hint – OPTION (OPTIMIZE FOR())
– LARGE TABLE: SELECTIVITY (END) */
Source code 5: Tests with using the query hint – OPTION (OPTIMIZE FOR())
Figure 5: SSMS result – TESTS: WITH Query Hint – OPTION (OPTIMIZE FOR()) – LARGE TABLE: SELECTIVITY (US: LOW / CA: HIGH)
The query optimizer does not know what will be the value of the variable @WhereValue at runtime and does not check statistics, before the query run. The query optimizer will always choose the operator scan.
If the value of the variable @WhereValue will most often be set to the ‘CA’, it would be smarter to optimize the query to use the seek operator instead the scan operator.
It is possible to be done by using of the query hint OPTION (OPTIMIZE FOR()).
Additional references:
48.312872
18.086038
