Microsoft SQL Server 2012 Bible

The Path of the Query

A good way to understand how to design efficient indexes is to observe and learn from the various possible paths' queries use to locate data using indexes.

The following section compares and contrasts ten different query paths. Not every query path is an efficient query path.

A good test table for observing the 10 query paths in the AdventureWorks2012 database is the Production.WorkOrder table. It has 72,591 rows, 10 columns, and a single-column clustered primary key. Here's the table definition:

CREATE TABLE [Production].[WorkOrder](
[WorkOrderID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NOT NULL,
[OrderQty] [int] NOT NULL,
[StockedQty] AS (isnull([OrderQty]-[ScrappedQty],(0))),
[ScrappedQty] [smallint] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[DueDate] [datetime] NOT NULL,
[ScrapReasonID] [smallint] NULL,
[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_WorkOrder_WorkOrderID] PRIMARY KEY CLUSTERED 
 ([WorkOrderID] ASC) 
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

The WorkOrder table has three indexes, each with one column as identified in the index name:

• PK_WorkOrder_WorkOrderID (Clustered)
• IX_WorkOrder_ProductID (Nonunique, Nonclustered)
• IX_WorkOrder_ScrapReasonID (Nonunique, Nonclustered)

Performance data for each path, listed in Table 45.1, was captured by watching the T-SQL → SQL:StmtCompleted and Performance ...