|
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.
------------
-- 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
|
