Query Optimizer – Table Scan, Index Scan/Seek and Query Hint OPTION (OPTIMIZE FOR ())

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:

Columnstore Index:
• SQL SERVER – Fundamentals of Columnstore Index
  by Grant Fritchey
• SQL SERVER – How to Ignore Columnstore Index Usage in Query
  by Pinal Dave
• SQL SERVER – Updating Data in A Columnstore Index
  by Pinal Dave

Execution Plan:

• Execution Plan Basics
  by Grant Fritchey
• SQL SERVER – Execution Plan – Estimated I/O Cost – Estimated CPU Cost – No Unit
  by Pinal Dave

Fillfactor:

• SQL SERVER – Fillfactor, Index and In-depth Look at Effect on Performance
  by Pinal Dave

Filtered Index:

• SQL SERVER – 2008 – Introduction to Filtered Index – Improve performance with Filtered Index
  by Pinal Dave

Fragmentation:

• SQL SERVER – Fragmentation – Detect Fragmentation and Eliminate Fragmentation
  by Pinal Dave
• SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
  by Pinal Dave

Index:

• SQL SERVER – Index Seek vs. Index Scan – Difference and Usage – A Simple Note
  by Pinal Dave
• Targeted Index Performance Improvements
  by Pinal Dave

Indexed View:

• SQL SERVER – Indexed View always Use Index on Table
  by Pinal Dave
• SQL SERVER – Interesting Observation – Index on Index View Used in Similar Query
  by Pinal Dave

Performance Tuning:

• SQL Server Performance Tuning
  by Pinal Dave
• SQL SERVER – Introduction to SQL Server 2008 Profiler
  by Pinal Dave
• SQL SERVER – Performance Tuning – Part 1 of 2 – Getting Started and Configuration
  by Pinal Dave

Statistics:

• Importance of Statistics
  by Pinal Dave
• Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
  by MSDN