|
Execute the following Microsoft SQL Server T-SQL scripts to demonstrate two ways to cover a query with index for boosting performance. Covering index has increased size, as a result it may slow down other queries. Therefore careful tests are necessary to judge its benefits. Very high frequency and business critical queries are prime candidates for index covering. Junction tables usually benefit from covering index by avoiding bookmark lookups.
Keep covering index as narrow as possible. Wide covering index is counter productive.
------------
-- 100 fold estimated query cost reduction with covering index
------------
USE AdventureWorks2008;
-- Cost without covering index: 0.576
SELECT Date=convert(date,OrderDate), TotalDue, SubTotal, TaxAmt, Freight
FROM Sales.SalesOrderHeader
WHERE OrderDate >='2004-02-01' AND OrderDate < '2004-02-05'
-- Create covering index with INCLUDE
CREATE INDEX idxOrdDtDue ON Sales.SalesOrderHeader(OrderDate) -- key column
INCLUDE(TotalDue, SubTotal, TaxAmt, Freight) -- non-key columns
-- Cost with covering index: 0.0052
------------
------------
-- Covering Index: Testing Composite Index option - SQL Server all versions
------------
USE Northwind
-- CLUSTERED INDEX SCAN on Primary Key index
-- COST 0.0056
SELECT City
FROM Customers
WHERE Country = 'UK'
-- The following covering index (composite index) can be created:
CREATE INDEX IDXLocation ON Customers ( Country, City)
GO
-- Index Covering improves SQL Server query performance
-- INDEX SEEK on the new covering index - SQL Server optimization
-- COST 0.0032
SELECT City
FROM Customers
WHERE Country = 'UK'
GO
DROP INDEX Customers.IDXLocation
------------
-- Covering one table in multi-table query
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit,
Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1
ORDER BY Categories.CategoryName, Products.ProductName
GO
-- Cost: 0.0303
CREATE INDEX idxProduct ON Products(CategoryID, Discontinued)
INCLUDE(ProductName, QuantityPerUnit, UnitsInStock)
-- Cost: 0.0198
------------
------------
-- Testing INCLUDE index option - SQL Server 2005 & SQL Server 2008
-- Improve query performance with SQL Server 2005 and on covering index enhancements
------------
USE AdventureWorks2008;
GO
-- Drop existing index just for the testing
IF EXISTS (SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[Production].[Product]')
AND name = N'AK_Product_ProductNumber')
DROP INDEX [AK_Product_ProductNumber] ON [Production].[Product] WITH ( ONLINE = OFF )
GO
-- CLUSTERED INDEX SCAN on ProductID
-- COST 0.012
SELECT ProductNumber,
ProductName = Name,
ListPrice,
Color
FROM Production.Product
WHERE ProductNumber LIKE 'FR-%';
GO
-- Create a covering index with non-key columns INCLUDE-d
-- Only the leaf level of the index tree is effected as opposed to
-- all levels in a composite index, hence it is faster than the composite index
CREATE NONCLUSTERED INDEX idxProductNum ON Production.Product (
ProductNumber)
INCLUDE (Name,ListPrice,Color);
GO
-- NON-CLUSTERED INDEX SEEK on the new index - SQL Server index optimization
-- COST 0.0041
SELECT ProductNumber,
ProductName = Name,
ListPrice,
Color
FROM Production.Product
WHERE ProductNumber LIKE 'FR-%';
GO
DROP INDEX dxProductNum ON Production.Product;
GO
-- RECREATE the unique index which was dropped for testing
CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_ProductNumber]
ON [Production].[Product] (
[ProductNumber] ASC)
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
EXEC sys.sp_addextendedproperty
@name = N'MS_Description' ,
@value = N'Unique nonclustered index.' ,
@level0type = N'SCHEMA' ,
@level0name = N'Production' ,
@level1type = N'TABLE' ,
@level1name = N'Product' ,
@level2type = N'INDEX' ,
@level2name = N'AK_Product_ProductNumber'
GO
Related article link: SQL Server Optimization
|
