SQL Server – Index – “Bookmark Lookup” tallying

Background

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
http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

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.

Code

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




Sample Invocation


    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

Index - BookmarkLookupPercentile

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:

Index - BookmarkLookupPercentile (later)

The Non-Clustered Index (user-seeks) and Clustered Index (User-lookups) are exactly the same @ 143951069.

Explanation

  • 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…

INDX_AdvPartnerAssignedZipcodes_ZIP

To correct review the queries and possibly

  1. Extend the Index to be a covering index and thus ensure that the Clustered Index will not be touched
  2. 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%

INDX_DBA_UserID_OrderID

 

To correct:

  1. Drop Primary key and recreate from Non-Clustered to Clustered
  2. Promote identified Non-Clustered Index from Non-Clustered to Clustered

Commendation

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
https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s