Forwarded Fetches and Bookmark Lookups

Last Updated 6 years ago

Base Table

When you choose to forgo putting a clustered index on your table, you may find your queries utilizing forwarded fetches — SQL Server’s little change of address form for rows that don’t fit on the page anymore.

This typically isn’t a good thing, though. All that jumping around means extra reads and CPU that can be really confusing to troubleshoot.

All sorts of things might get blamed, like parameter sniffing, out of date stats, eye tax frog man station, and more common bogeypersons that take the blame for all SQL Server performance issues.

Dummo

CREATE TABLE el_heapo 
( 
	id INT IDENTITY, 
	date_fudge DATE, 
	stuffing VARCHAR(3000)
);

INSERT dbo.el_heapo WITH (TABLOCKX) 
      ( date_fudge, stuffing )
SELECT DATEADD(HOUR, x.n, GETDATE()), REPLICATE('a', 1000)
FROM (
SELECT TOP (1000 * 1000)
ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.messages AS m
CROSS JOIN sys.messages AS m2
) AS x (n)

CREATE NONCLUSTERED INDEX ix_heapo ON dbo.el_heapo (date_fudge);

CREATE TABLE el_heapo

(

id INT IDENTITY,

date_fudge DATE,

stuffing VARCHAR(3000)

);

INSERT dbo.el_heapo WITH (TABLOCKX)

( date_fudge, stuffing )

SELECT DATEADD(HOUR, x.n, GETDATE()), REPLICATE('a', 1000)

FROM (

SELECT TOP (1000 * 1000)

ROW_NUMBER() OVER (ORDER BY @@SPID)

FROM sys.messages AS m

CROSS JOIN sys.messages AS m2

) AS x (n)

CREATE NONCLUSTERED INDEX ix_heapo ON dbo.el_heapo (date_fudge);

Here’s a heap. It’s not very special. When we examine it with sp_BlitzIndex, there’s not much going on.

EXEC master.dbo.sp_BlitzIndex @DatabaseName = N'Crap', 
                              @SchemaName = 'dbo', 
			      @TableName = 'el_heapo';

EXEC master.dbo.sp_BlitzIndex @DatabaseName = N'Crap',

@SchemaName = 'dbo',

@TableName = 'el_heapo';

Right in the middle of a swamp.

When we run a simple query against it, we get a plan with a bookmark lookup.

SELECT *
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
AND 1 = (SELECT 1)
OPTION(MAXDOP 1);

SELECT *

FROM dbo.el_heapo AS eh

WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'

AND 1 = (SELECT 1)

OPTION(MAXDOP 1);

The MAXDOP 1 hint is just to make reading the tea leaves easier.

When we update the table, the fun starts. By fun I mean terribleness.

UPDATE eh
SET eh.stuffing = REPLICATE('z', 3000)
FROM dbo.el_heapo AS eh
WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'
OPTION(MAXDOP 1)

UPDATE eh

SET eh.stuffing = REPLICATE('z', 3000)

FROM dbo.el_heapo AS eh

WHERE eh.date_fudge BETWEEN '2018-09-01' AND '2019-09-01'

OPTION(MAXDOP 1)

Now sp_BlitzIndex shows up something new!

Ehhh

We racked up ~6300 forwarded fetches just during the update.

If we re-run our original select query, that number doubles.

Ach.

If we run Profiler to capture some query metrics, because it’s late and I’d rather enjoy this wine than use XE, the issues show themselves a bit more.

I think the change in metrics from before and after the update speak for themselves.

This is all on a relatively small heap, with queries that touch a small number of rows.

My select only returns a little under 9000 records, but it takes ~6000 extra reads to get them with the fetches involved.

CPU doesn’t do much, but it does show up where it hadn’t before.

Fear Of Endings

Heaps have their uses. Some of my favorite people love heaps.

But you have to be really careful when choosing which tables you’re going to leave clustered indexes off of.

Typically, if any part of the workload involves updates or deletes, a heap is a bad idea.

Thanks for reading!

Going to Summit? Here’s a Calendar Invite for My Session.

[Video] Office Hours 2018/9/5 (With Transcriptions)

4 Comments. Leave new

Rob
September 7, 2018 12:34 pm

Yes, deletes on heaps without doing a table lock is bad, it doesn’t de-allocate the space in the table, so new rows will not get added there. You have to rebuild the table or temporarily add a clustered index to the table to do so. We had tables with gigs of unused space in them.

Reply
- Brent Ozar
  September 8, 2018 8:19 am
  
  Rob – when you say “new rows will not get added there,” I assume you mean new rows from *other* indexes or tables? The space is available for reuse for the same object. You can prove it by creating a heap, loading it with 1GB of rows, delete without a table lock, and then insert another 1GB of data. The heap won’t be 2GB in size. (Just clarifying to make sure we’re on the same page.)
  
  Reply
El Vino
September 11, 2018 5:21 am

“eye tax frog man station” ?
Wypo (Wine typo), or just an old person’s reference that I don’t get cause I’m too darn young? 😛

Reply
- Brent Ozar
  September 11, 2018 5:29 am
  
  Heh heh heh, yeah, deep cut there – it’s kinda like Cockney rhyming slang for index fragmentation.
  
  Reply