1014
Part VII: Performance Tuning and Optimization
The query optimizer chooses the access method with the least overall cost. Sequentially
reading the data is an effi cient task, so an index scan and fi lter operation may actually be
cheaper than an index seek with a bookmark lookup (see Query Path 5 in the next
section) involving hundreds or thousands of random IO index seeks. SQL Server heavily
uses statistics to determine the number of rows touched and returned by each operation in
the query execution plan. If statistics are accurate, SQL Server has a great opportunity to
choose the appropriate access method to most effi ciently return the requested data. On the
other hand, if statistics are skewed or out-of-date, the likelihood that SQL Server chooses
the correct access method decreases signifi cantly. I’ve seen hundreds of performance issues
over the years caused by skewed statistics.The Path of the Query
A good way to understand how to design effi cient 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 effi cient 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 defi nition: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 identifi ed in the index name:■ PK_WorkOrder_WorkOrderID (Clustered)■ (^) IX_WorkOrder_ProductID (Nonunique, Nonclustered)
■ IX_WorkOrder_ScrapReasonID (Nonunique, Nonclustered)
c45.indd 1014c45.indd 1014 7/31/2012 10:16:39 AM7/31/2012 10:16:39 AM
http://www.it-ebooks.info