Query Tuning Quick wins: Bookmark lookups – Kill Bill
Frequently a quick win in query tuning is to remove a bookmark, or key lookup. A key lookup occurs when we have to resolve another column that wasn’t part of the index used to satisfy the query. Frequently, Sql will avoid calling a bookmark lookup and just brute force its way down the clustered index instead. In the 2008 toolset, we might also throw up the missingIndexes element in the xml plan.
If they’re called many, many times, they are absolutely worth resolving. Fortunately, they can be detected from the sqlPLan xml – the following shows a Book Mark Lookup and the corresponding (collapsed) xml:
And the following xQuery (expressed as a table valued function) will allow you to return the include columns (the ColumnOutputs) required to remove the BML:
| SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO CREATEFUNCTION [dbo].[fnFindBookMarkLookup](@sqlPlan xml) RETURNSTABLE ASRETURN ( with XMLNAMESPACES(default‘https://schemas.microsoft.com/sqlserver/2004/07/showplan’), CTE as ( SELECT @sqlPlan as SqlPlan, RelOp.value(‘(child::RelOp[1]/IndexScan/Object/@Database)[1]‘, ‘varchar(max)’)as InnerDb, RelOp.value(‘(child::RelOp[1]/IndexScan/Object/@Schema)[1]‘, ‘varchar(max)’)as InnerSchema, RelOp.value(‘(child::RelOp[1]/IndexScan/Object/@Table)[1]‘, ‘varchar(max)’)as InnerTable, RelOp.value(‘(child::RelOp[1]/IndexScan/Object/@Index)[1]‘, ‘varchar(max)’)as InnerIndex, RelOp.value(‘(child::RelOp[2]/IndexScan/Object/@Database)[1]‘, ‘varchar(max)’)as OuterDb, RelOp.value(‘(child::RelOp[2]/IndexScan/Object/@Schema)[1]‘, ‘varchar(max)’)as OuterSchema, RelOp.value(‘(child::RelOp[2]/IndexScan/Object/@Table)[1]‘, ‘varchar(max)’)as OuterTable, RelOp.value(‘(child::RelOp[2]/IndexScan/Object/@Index)[1]‘, ‘varchar(max)’)as OuterIndex, RelOp.query(‘child::RelOp[2]/OutputList’)as ColumnOutputs FROM @sqlPlan.nodes(‘//NestedLoops’)AS NestedLoop(RelOp) where RelOp.exist(‘child::RelOp[2]/.[@PhysicalOp="Clustered Index Seek"]‘)= 1 ) select*from CTE where InnerSchema = OuterSchema and InnerTable = outerTable ) |
In order to apply the output from the function take the original innerIndex definition, and add the ColumnOutputs to the include switch, so something like:
| CREATE INDEX <<yourIndex>> on <<YOUR TABLE>> on (<<original columns>>) INCLUDE(<<outputColumns>> |
This will work in 2005 onwards. If you’re still plugging away with Sql 2000 (out of support) then you’ll have to move the clustered index or provide a covering index.
Now, if you load a profiler trace with the showplanXml statistics event, and create the primary and path indexes (this can take a long time, and a lot of space – over an hour on my system) you can get some nifty results fairly quickly. You may also spot that occasionally, you get a missing index hint (missingIndexes element in the plan) which the 2008 toolset will show to you.
This, isn’t however the end of it. Occasionally you can get a bookmark lookup where there is nothing listed as an output column. This is because the column that isn’t being used as part of an index is actually being used as a filter, so something like:
| Select pkValue from tableName where indexedColumn = 0 and nonIndexedColumn=1; |
I’ve not written the extract for that yet. Some of you may have noticed this function is written in preparation for another tuning prospect – the index intersection. The current query works by returning any nested loop join where the second operator is a clustered index scan, and the join is between two indexes from the same table. Nested loop joins can be cpu intensive for large rows (remember, they have a low startup cost, but a long running cost and can sometimes mean we’ve either not got the resource to run a hash join, or the stats are *way* off). Index intersection will join between two indexes defined against the same table, and their impact can be reduced by including a covering index in their place. I’ll leave that as an exercise for the reader.
Cheers,
Ryan.
Original post by Ryan Simpson on 31/03/11 here: https://rionisimpsoni.wordpress.com/2011/03/31/query-tuning-quick-wins-bookmark-lookups-%e2%80%93-kill-bill-2/