Database Reference
In-Depth Information
■
You can read more about
sys.dm_db_index_operation_stats
DMF at Books Online:
Note
■
Make sure that usage statistics collects enough information representing typical system workload before
performing an analysis.
Important
Index Consolidation
As we discussed in Chapter 2, “Tables and Indexes: Internal Structure and Access Methods,” SQL Server can use
composite index for an Index Seek operation as long as a query has a SARGable predicate on the leftmost query column.
Let's look at the table shown in Listing 6-13. There are two nonclustered indexes,
IDX_Employee_LastName_
FirstName
and
IDX_Employee_LastName
, which have a
LastName
column defined as the leftmost column in the index.
The first index,
IDX_Employee_LastName_FirstName
, can be used for an Index Seek operation as long as there is a
SARGable predicate on the
LastName
column, even when a query does not have a predicate on the
FirstName
column.
Thus the
IDX_Employee_LastName
index is redundant.
Listing 6-13.
Example of redundant indexes
create table dbo.Employee
(
EmployeeId int not null,
LastName nvarchar(64) not null,
FirstName nvarchar(64) not null,
DateOfBirth date not null,
Phone varchar(20) null,
Picture varbinary(max) null
);
create unique clustered index IDX_Employee_EmployeeId
on dbo.Employee(EmployeeId);
create nonclustered index IDX_Employee_LastName_FirstName
on dbo.Employee(LastName, FirstName);
create nonclustered index IDX_Employee_LastName
on dbo.Employee(LastName);
As the general rule, you can remove redundant indexes from the system. Although such indexes can be slightly
more efficient during scans due to their compact size, update overhead usually outweighs this benefit.
