Database Reference
In-Depth Information
As you see, SQL Server had to read the data pages 10 times even though query returned just two rows. The
number of I/O operations can be calculated based on the following formula:
(# of levels in nonclustered index)
+ ((number of pages read from the leaf level of nonclustered index) + (number of rows found) * (# of
levels in clustered index).
As you can guess, a large
number of rows found
(key lookup operations) leads to a
large number of I/O operations, which makes nonclustered index usage inefficient.
There is another important factor contributing to nonclustered index inefficiency.
Key lookups
read the data from
different places in the data files. Even though data pages from root and intermediate index level are often cached and
introduce just logical reads, accessing leaf-level pages leads to random physical I/O activity. In contrast, index scans
trigger sequential I/O activity, which is usually more efficient than random I/O in the case of magnetic hard drives.
As a result, SQL Server is very conservative in choosing nonclustered indexes when it expects that a large number
of
key lookup
operations will be required. Let's create a table and populate it with the data shown in Listing 2-15.
Listing 2-15.
Nonclustered index usage: Creating a test table
create table dbo.Books
(
BookId int identity(1,1) not null,
Title nvarchar(256) not null,
-- International Standard Book Number
ISBN char(14) not null,
Placeholder char(150) null
);
create unique clustered index IDX_Books_BookId on dbo.Books(BookId);
-- 1,252,000 rows
;with Prefix(Prefix)
as
(
select 100
union all
select Prefix + 1
from Prefix
where Prefix < 600
)
,Postfix(Postfix)
as
(
select 100000001
union all
select Postfix + 1
from Postfix
where Postfix < 100002500
)
insert into dbo.Books(ISBN, Title)
select
CONVERT(char(3), Prefix) + '-0' + CONVERT(char(9),Postfix)
,'Title for ISBN' + CONVERT(char(3), Prefix) + '-0' + CONVERT(char(9),Postfix)
from Prefix cross join Postfix
option (maxrecursion 0);
create nonclustered index IDX_Books_ISBN on dbo.Books(ISBN);
