May 6, 2015 at 7:16 am
Hi All,
Looking at an execution plan today I noticed something I've not seen before. The plan includes a non clustered index seek, followed by a RID lookup on the heap. The output list for the index seek contains the expected "Bmk" column (in this case "Bmk1473"), but also includes "IsBaseRow1475". This isn't a column from the table.
A Google search isn't finding anything - does anyone know what this is, and why its in the plan?
This isn't causing a problem or anything, just a matter of curiosity.
May 6, 2015 at 9:58 am
You're getting an RID lookup because the nonclustered index isn't covering and you don't have a clustered index. The BMK that you're referencing is the storage location within the heap that gets kept with the nonclustered index instead of the cluster key. The IsBaseRow is a marker used to keep track of where it came from. There's a cursor or some other temporary storage mechanism elsewhere in the plan that's forcing it to track the rows a little differently. It's not anything to worry about.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2015 at 1:30 am
Thanks Grant.
I've looked at the plan again, and the query is left joining to the table that has the index seek/lookup (not every row on the outer side of the query has a match in this table), and the IsBaseRow column seems to only be used as a PassThru argument (IsBaseRow IS NULL) to the loop join for the Lookup. The actual executions of the lookup are the same number as the number of matching rows.
So is the "IsBaseRow" column being used as a flag to perform the RID Lookup/skip it for a particular row? If so, it seems odd to include an extra column rather than doing a NULL check on the Bmk.
May 7, 2015 at 4:22 am
T. C. (5/7/2015)
Thanks Grant.I've looked at the plan again, and the query is left joining to the table that has the index seek/lookup (not every row on the outer side of the query has a match in this table), and the IsBaseRow column seems to only be used as a PassThru argument (IsBaseRow IS NULL) to the loop join for the Lookup. The actual executions of the lookup are the same number as the number of matching rows.
So is the "IsBaseRow" column being used as a flag to perform the RID Lookup/skip it for a particular row? If so, it seems odd to include an extra column rather than doing a NULL check on the Bmk.
I'm actually not sure. It's an internal marker used by the engine that surfaces in some execution plans. I haven't yet needed it answer a question about what the plan was doing. If it's checking the IsBaseRow, then it must be using it for the matches, but usually it does just match based on the heap identifier.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply