Indexes and lookups

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.

First create the procedure on a table with 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.

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.

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.

Missing index details suggest

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

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])

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.

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