Seek vs. scan
QR Code JIS X 0510 Encoder In Visual C#.NET
Using Barcode printer for Visual Studio .NET Control to generate, create QR Code ISO/IEC18004 image in VS .NET applications.www.OnBarcode.com
Recognizing QR Code In Visual C#.NET
Using Barcode recognizer for .NET framework Control to read, scan read, scan image in VS .NET applications.www.OnBarcode.com
Several important terms are used when discussing index usage. An index seek is used when the query optimizer chooses to navigate through the levels of a clustered or nonclustered index B-tree to quickly reach the appropriate leaf level pages. In contrast, an index scan, as the name suggests, scans the leaf level, left to right, one page at a time.
PDF-417 2d Barcode Creator In Visual C#
Using Barcode creator for VS .NET Control to generate, create PDF417 image in .NET applications.www.OnBarcode.com
Generating QR Code In C#
Using Barcode maker for .NET Control to generate, create QR Code image in .NET framework applications.www.OnBarcode.com
Clustered index scan
Generating GS1-128 In C#.NET
Using Barcode printer for .NET framework Control to generate, create UCC-128 image in .NET framework applications.www.OnBarcode.com
Printing EAN13 In Visual C#
Using Barcode encoder for .NET Control to generate, create UPC - 13 image in VS .NET applications.www.OnBarcode.com
Clustered index seek
Barcode Drawer In C#
Using Barcode encoder for .NET Control to generate, create Barcode image in .NET applications.www.OnBarcode.com
Create 2/5 Industrial In Visual C#.NET
Using Barcode printer for VS .NET Control to generate, create Industrial 2 of 5 image in .NET applications.www.OnBarcode.com
Nonclustered index scan
Encode QR Code JIS X 0510 In Java
Using Barcode generation for Java Control to generate, create QR Code image in Java applications.www.OnBarcode.com
QR Code ISO/IEC18004 Decoder In .NET Framework
Using Barcode reader for .NET Control to read, scan read, scan image in .NET applications.www.OnBarcode.com
Nonclustered index seek
Make Code-39 In Java
Using Barcode encoder for Java Control to generate, create Code39 image in Java applications.www.OnBarcode.com
Painting ECC200 In None
Using Barcode generation for Font Control to generate, create Data Matrix ECC200 image in Font applications.www.OnBarcode.com
Generating EAN128 In None
Using Barcode generation for Font Control to generate, create EAN128 image in Font applications.www.OnBarcode.com
Make UPCA In None
Using Barcode creation for Font Control to generate, create UPC Symbol image in Font applications.www.OnBarcode.com
Code 39 Printer In VS .NET
Using Barcode generation for Reporting Service Control to generate, create Code 39 Extended image in Reporting Service applications.www.OnBarcode.com
Barcode Generation In Java
Using Barcode creator for Java Control to generate, create Barcode image in Java applications.www.OnBarcode.com
Figure 13.4 Common icons used in graphical execution plans
Paint Barcode In VS .NET
Using Barcode maker for Visual Studio .NET Control to generate, create Barcode image in .NET framework applications.www.OnBarcode.com
Decoding Code 128C In Visual Basic .NET
Using Barcode scanner for Visual Studio .NET Control to read, scan read, scan image in .NET applications.www.OnBarcode.com
Index design and maintenance
Encode Barcode In Java
Using Barcode creator for BIRT reports Control to generate, create Barcode image in Eclipse BIRT applications.www.OnBarcode.com
Print USS-128 In None
Using Barcode generator for Excel Control to generate, create EAN 128 image in Microsoft Excel applications.www.OnBarcode.com
The Person.Contact table, as defined below (abbreviated table definition), contains approximately 20,000 rows. For the purposes of this test, we ll create a nonunique, nonclustered index on the LastName column:
-- Create a contact table with a nonclustered index on LastName CREATE TABLE [Person].[Contact]( [ContactID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED , [Title] [nvarchar](8) NULL , [FirstName] [dbo].[Name] NOT NULL , [LastName] [dbo].[Name] NOT NULL , [EmailAddress] [nvarchar](50) NULL ) GO CREATE NONCLUSTERED INDEX [ixContactLastName] ON [Person].[Contact] ([LastName] ASC) GO
For our first example, let s run a query to return all contacts with a LastName starting with C:
-- Statistics indicate too many rows for an index lookup SELECT * FROM Person.Contact WHERE LastName like 'C%'
Despite the presence of a nonclustered index on LastName, which in theory could be used for this query, SQL Server correctly ignores it in favor of a clustered index scan. If we execute this query in SQL Server Management Studio using the Include Actual Execution Plan option (Ctrl+M, or select from the Query menu), we can see the graphical representation of the query execution, as shown in figure 13.5.
Figure 13.5 A clustered index scan is favored for this query in place of a nonclustered index seek plus key lookup.
No great surprises here; SQL Server is performing a clustered index scan to retrieve the results. Using an index hint, let s rerun this query and force SQL Server to use the ixContactLastName index:
-- Force the index lookup with an index hint SELECT * FROM Person.Contact WITH (index=ixContactLastName) WHERE LastName like 'C%'
Looking at the graphical execution plan, we can confirm that the index is being used, as per figure 13.6.
Adding an index hint to the previous query results in an index seek plus key lookup.
On a small database such as AdventureWorks, the performance difference between these two methods is negligible; both complete in under a second. To better understand how much slower the index lookup method is, we can use the SET STATISTICS IO option, which returns disk usage statistics6 alongside the query results. Consider the script in listing 13.2.
Listing 13.2 Comparing query execution methods
-- Compare the Disk I/O with and without an index lookup SET STATISTICS IO ON GO DBCC DROPCLEANBUFFERS GO SELECT * FROM Person.Contact WHERE LastName like 'C%' DBCC DROPCLEANBUFFERS SELECT * FROM Person.Contact with (index=ixContactLastName) WHERE LastName like 'C%' GO
This script will run the query with and without the index hint. Before each query, we ll clear the buffer cache using DBCC DROPCLEANBUFFERS to eliminate the memory cache effects. The STATISTICS IO option will produce, for each query, the number of logical, physical, and read-ahead pages, defined as follows:
Logical Reads Represents the number of pages read from the data cache. Physical Reads If the required page is not in cache, it will be read from disk. It follows that this value will be the same or less than the Logical Reads counter.
Not to be confused with index statistics, query statistics refer to disk usage, such as the number of pages read from buffer or physical disk reads.
Index design and maintenance
Figure 13.7 Forcing a nonclustered index seek plus key lookup significantly increases the number of pages read.
Read Ahead Reads The SQL Server storage engine uses a performance opti-
mization technique called Read Ahead, which anticipates a query s future page needs and prefetches those pages from disk into the data cache. In doing so, the pages are available in cache when required, avoiding the need for the query to wait on future physical page reads. So with these definitions in mind, let s look at the STATISTICS IO output in figure 13.7. These statistics make for some very interesting reading. Note the big increase in logical reads (3326 versus 569) for the second query, which contains the (index= ixContactLastName) hint. Why such a big increase A quick check of sys.dm_ db_index_physical_stats, covered in more detail later in the chapter, reveals there are only 570 pages in the table/clustered index. This is consistent with the statistics from the query that used the clustered index scan. So how can the query using the nonclustered index read so many more pages The answer lies in the key lookup. What s actually occurring here is that a number of clustered index pages are being read more than once. In addition to reading the nonclustered index pages for matching records, each key lookup reads pages from the clustered index to compete the query. In this case, a number of the key lookups are rereading the same clustered index page. Clearly a single clustered index scan is more efficient, and SQL Server was right to ignore the nonclustered index. Let s move on to look at an example where SQL Server uses the nonclustered index without any index hints:
SELECT * FROM Person.Contact WHERE LastName like 'Carter%'
The graphical execution plan for this query is shown in figure 13.8, and it confirms the index is being used. We can see that of the overall query cost, 98 percent is the key lookup. Eliminating this step will derive a further performance increase. You ll note that in our queries so far we ve been using select *; what if we reduced the required columns for the query