Understanding Indexes and Lookups in SQL Server: Execution Plans and Performance Tuning

This post provides a practical walkthrough on how index creation and modification affect execution plans, particularly in SQL Server 2008 R2 using FactResellerSales and DimProduct tables from the AdventureWorks DW database.

Recompiling a stored procedure and rebuilding indexes is something that many DBAs are familiar with but even then there are a lot of misunderstandings about how the process works. Today I am going to look at a very specific example.

• Start by creating the stored procedure on a table that has no index.
• Look at the execution plan
• Create an index and then execute the procedure one more and review the execution plan
• Rebuild the index and then execute the procedure and review the execution plan.

We will use SQL Server 2008 R2 for the example.

Initial Execution Without Indexes

I have created a copy of the factReseller and Dimproduct tables from the Adventureworks Datawarehouse database.The below query is a simple inner join to fetch product and sales details from the above tables.

CREATE PROCEDURE GetProductSales @qnty INT
AS
SELECT p.ProductKey
    ,EnglishProductName
    ,ListPrice
    ,f.*
FROM dbo.product p
JOIN dbo.FactResellerSales f ON p.productkey = f.ProductKey
WHERE EnglishProductName = 'AWC Logo Cap'
    AND f.OrderQuantity = @qnty

The below execution plan is the result of running the store procedure.

As you can see the table scan occurs on both tables because there is no index. Now let’s create a clustered index on the table DimProduct and a nonclustered index on the FactReseller.

Creating Indexes

Missing index details suggest

CREATE NONCLUSTERED INDEX NCFact ON [dbo].[FactResellerSales] ([OrderQuantity]) INCLUDE (
    [ProductKey]
    ,[OrderDateKey]
    ,[DueDateKey]
    ,[ShipDateKey]
    ,[ResellerKey]
    ,[EmployeeKey]
    ,[PromotionKey]
    ,[CurrencyKey]
    ,[SalesTerritoryKey]
    ,[SalesOrderNumber]
    ,[SalesOrderLineNumber]
    ,[RevisionNumber]
    ,[UnitPrice]
    ,[ExtendedAmount]
    ,[UnitPriceDiscountPct]
    ,[DiscountAmount]
    ,[ProductStandardCost]
    ,[TotalProductCost]
    ,[SalesAmount]
    ,[TaxAmt]
    ,[Freight]
    ,[CarrierTrackingNumber]
    ,[CustomerPONumber]
    )
GO

CREATE NONCLUSTERED INDEX NCproduct ON [dbo].[product] ([EnglishProductName])

Analyzing RID Lookups

The below execution plan shows that even after creating the required indexes there is RID lookup happening. Note that till now we havent had to recompile the procedure even once. It is automatically pikcing up the changes in the index. http://msdn.microsoft.com/en-us/library/ms190439.aspx the recommendations in this article however indicate that adding the index doesn’t force a recompile since the table object itself hasn’t been modified.

The above query adds one more missing index message saying the include the columns from the fact table so as to improve the performance by only having to seek from index to fetch all rows.

Additionally the RID lookup indicates that we should also add the product key and listprice to the Nonclustered index of the Product table. As soon as the index is created we see that we have avoided table scans and lookups.

Optimizing With INCLUDE Columns

Below are the scripts for the final set of indexes that got created.

CREATE NONCLUSTERED INDEX [NCproduct] ON [dbo].[product] (     [EnglishProductName] ASC) INCLUDE (
    [ProductKey]
    ,[ListPrice]
    )
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NC_FACT] ON [dbo].[FactResellerSales] (
         [ProductKey] ASC
    ,     [OrderQuantity] ASC
    ) INCLUDE (
    [OrderDateKey]
    ,[DueDateKey]
    ,[ShipDateKey]
    ,[ResellerKey]
    ,[EmployeeKey]
    ,[PromotionKey]
    ,[CurrencyKey]
    ,[SalesTerritoryKey]
    ,[SalesOrderNumber]
    ,[SalesOrderLineNumber]
    ,[RevisionNumber]
    ,[UnitPrice]
    ,[ExtendedAmount]
    ,[UnitPriceDiscountPct]
    ,[DiscountAmount]
    ,[ProductStandardCost]
    ,[TotalProductCost]
    ,[SalesAmount]
    ,[TaxAmt]
    ,[Freight]
    ,[CarrierTrackingNumber]
    ,[CustomerPONumber]
    )
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ) ON [PRIMARY]
GO

Please Consider Subscribing

Subscribe