On the way home yesterday evening had a bit of downtime, No Internet Access, and so all I had to read were previously downloaded documents.
Insofar as Microsoft SQL Server 2008, you really not can not do much better than:
Troubleshooting Performance Problems in SQL Server 2008
It is a must read as the writers are Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng, Burzin Patel
One of the areas covered astutely is Indexes and in this particular web post, I will like to cover what was known as “Bookmark Lookup”.
But, let us specifically “paint” a background. SQL Server provides a Dynamic Management View (DMV) known as sys.dm_db_index_usage_stats. And, one of the columns referenced is the Lookup column:
Lookup: Indicates that a clustered index that is defined on a table was used to look up data that was identified by seeking through a nonclustered index that is also defined on that table. This describes the scenario known as bookmark lookupin SQL Server 2000 or earlier. It represents a scenario where a nonclustered index is used to access a table and the nonclustered index does not cover the columns of the query SELECT list and the columns defined in the WHERE clause.
SQL Server increments the value of the column user_seeks for the nonclustered index used plus the column user_lookups for the entry of the clustered index. This count can become very high if there are multiple nonclustered indexes defined on the table. If the number of user seeks against a clustered index of a table is pretty high, the number of user lookups is pretty high as well, and if the number of user seeks against one particular non-clustered index is very high as well, you should consider making the nonclustered index with the high count the clustered index.
What are Bookmark Lookups:
The Bookmark Lookup logical and physical operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index. The Argument column also contains the name of the table or clustered index in which the row is looked up. If the WITH PREFETCH clause appears in the Argument column, then the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the table or clustered index.
So basically Bookmark Lookups occur whenever SQL Server uses a non-clustered index to deduce which records are covered in a where clause, but the clustered index still needs to be queried to get other columns referenced in the select clause.
One way to get around this need is to use covering index.
Here is a query that can be used to identify when this is occurring for specific tables or for the entire database.
Stored Procedure – dbo.sp_Diagnostics_Index_Identify_BookmarkLookup
use master go if OBJECT_ID('dbo.sp_Diagnostics_Index_Identify_BookmarkLookup') is null begin exec('create procedure [dbo].[sp_Diagnostics_Index_Identify_BookmarkLookup] as select ''not implemented'' '); end go alter procedure [dbo].[sp_Diagnostics_Index_Identify_BookmarkLookup] ( @ratioBookmarkLookup float = 0.01 , @minimalNonClusteredCount bigint = 10000 , @minimalClusteredCount bigint = 10000 , @MaxNumberofRecords int = 1000 ) as begin ;with cte as ( select objectName = OBJECT_NAME(tblI.object_id) , tblI.name , tblI.index_id , tblI.is_disabled , tblINC.user_seeks , tblINC.user_scans , [clusteredIndex] = tblIUSCLCI.name , [bookmarkLookups] = tblIUSCL.user_lookups , [ratioBookmarkLookup] = cast ( ( cast ( tblIUSCL.user_lookups as float ) ) / ( NULLIF ( cast ( (tblINC.user_seeks + tblINC.user_scans ) as float ) , 0 ) ) as decimal(30, 4) ) from sys.objects tblO inner join sys.indexes tblI on tblO.object_id = tblI.object_id inner join sys.dm_db_index_usage_stats tblINC on tblI.object_id = tblINC.object_id and tblI.index_id = tblINC.index_id inner join sys.dm_db_index_usage_stats tblIUSCL on tblINC.object_id = tblIUSCL.object_id and tblINC.index_id != 1 and tblIUSCL.index_id = 1 inner join sys.indexes tblIUSCLCI on tblIUSCL.object_id = tblIUSCLCI.object_id and tblIUSCL.index_id = tblIUSCLCI.index_id where tblO.[type] = 'U' and tblINC.[index_id] != 1 /* Non-Clustered Index --> user seeks + user scans */ and ( ( (tblINC.user_seeks + tblINC.user_scans) > @minimalNonClusteredCount ) ) -- clustered index lookups occured and (tblIUSCL.user_lookups != 0) and ( ( tblIUSCL.user_lookups >= @minimalClusteredCount ) ) ) select top ( @MaxNumberofRecords ) cte.* from cte where ( ([ratioBookmarkLookup] >= @ratioBookmarkLookup ) ) order by ( user_seeks + user_scans + [bookmarkLookups] ) desc , [ratioBookmarkLookup] desc end go exec sys.sp_MS_MarkSystemObject 'dbo.sp_Diagnostics_Index_Identify_BookmarkLookup' go
declare @ratioBookmarkLookup float declare @minimalNonClusteredCount bigint declare @minimalClusteredCount bigint declare @MaxNumberofRecords int set @minimalClusteredCount = 1E5 exec [dbo].[sp_Diagnostics_Index_Identify_BookmarkLookup] @minimalClusteredCount = @minimalClusteredCount
And, here is our result
And, here is our result
In our case I have a deep knowledge of the specific indexes and know that though the first index listed is by far the one that is experiencing the most “bookmark” lookups, I can not simply correct it by adding “covering indexes”.
The index is being used during the course of an update statement, and not just per a select query.
The result above is also illuminating as both the Non Clustered Index user-seeks and Clustered Index User Lookups are tracking very closely; one is 1438211051 and the other is 143951069.
In fact on a later run:
The Non-Clustered Index (user-seeks) and Clustered Index (User-lookups) are exactly the same @ 143951069.
- You can probably ignore the entries bearing is_disabled =1; as those indexes are disabled
- You want to pay attention to the entries that have correlating non-clustered index seeks/scans and clustered index lookup
- The closer the non-clustered index seeks + scans to clustered index lookup the more likely the index is using the clustered index during bookmark lookups
What is the great help?
Let us show quick scenarios.
Table = dbo.AdvPartnerAssignedZipcodes, INDEX = INDX_AdvPartnerAssignedZipcodes_ZIP
The fourth row below shows an exact match for user seeks and bookmark Lookup…
To correct review the queries and possibly
- Extend the Index to be a covering index and thus ensure that the Clustered Index will not be touched
- In our case, we disabled the index, and created a new covering one
Table = Products.CarTrainingPackage, INDEX = INDX_AdvPartnerAssignedZipcodes_ZIP
The 9th row, Table – Products.OrderedInCarTrainingPackage, Index = INDX_DBA_UserID_OrderID shows a close ratio of 99%
- Drop Primary key and recreate from Non-Clustered to Clustered
- Promote identified Non-Clustered Index from Non-Clustered to Clustered
Please consult and per-use Jonathan Keyhayias’s find code to see which objects are using Indexes:
Jonathan Kehayias – Finding what queries in the plan cache use a specific index
- Troubleshooting Performance Problems in SQL Server 2008