Update: This post is participating in the 4th installment of the T-SQL Tuesday hosted this time by Mike Walsh.
One of the side affects of having Heaps in a system is the possibility of generating forwarded records. When reading data from a heap, Forwarded records (if they are present in a heap) can generate extra, random and potentially physical IOs when compared to a table with clustered index and this may hurt the performance if one has to read lot of data from that heap. Note that having a heap doesn’t mean it is guaranteed to have forwarded records. Enough has been said and blogged about the details of forwarded records already and in this post I am NOT going to rehash the details of forwarded records and how it affects the performance but will go over an example and a script that will help to find out if forwarded records were used to fetch the data in the heap. Armed with that information one can see the true cost of these extra IOs generated by forwarded records. Looking at sys.dm_db_index_physical_stats shows if forwarded records are present in the heap but it doesn’t necessarily tell if the workload is indeed affected by them, how much and how many times. In the script I am going to show at the end of this post, one can see how many times forwarded records have been used to retrieve the data and understand the total cost of *extra IOs* and the performance implications of forwarded records.
I will be borrowing Kalen’s example for this test and here is the script for the test data.
USE
AdventureWorks
GO
IF OBJECT_ID ('Details', 'U') IS NOT NULL
DROP TABLE Details;
GO
CREATE TABLE dbo.Details
([SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL);
GO
INSERT INTO dbo.Details
SELECT [SalesOrderID]
,[SalesOrderDetailID]
,[CarrierTrackingNumber]
,[OrderQty]
,[ProductID]
,[SpecialOfferID]
,[UnitPrice]
,[UnitPriceDiscount]
FROM [AdventureWorks].[Sales].[SalesOrderDetail];
GO
SELECT OBJECT_NAME(OBJECT_ID) AS Object
, page_count
, avg_page_space_used_in_percent
, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks'), OBJECT_ID ('dbo.Details'), NULL, NULL, 'DETAILED');
GO
SET STATISTICS IO ON
GO
-- A table scan takes as many reads as there are pages, i.e. 856 in this case
SELECT * FROM dbo.Details;
GO
SET STATISTICS IO OFF
GO
/*
Table 'Details'. Scan count 1, logical reads 856, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.*/
-- Now add a new fixed width column and note that this is a
-- metadata only change
-- The data pages are not modified
-- There is no change in the fullness of the pages
ALTER TABLE dbo.Details ADD notes CHAR(100);
GO
SELECT OBJECT_NAME(OBJECT_ID) AS Object
, page_count
, avg_page_space_used_in_percent
, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks'), OBJECT_ID ('dbo.Details'), NULL, NULL, 'DETAILED');
GO
-- The data pages are not affected until we run the following update.
-- Every row on every page will get an additional 100 bytes in the notes field
-- added to it
UPDATE dbo.Details
SET notes = 'notes';
GO
-- note there are LOTS of forwarded records now (84408),
-- and many more pages the table (2709)
SELECT OBJECT_NAME(OBJECT_ID) AS Object
, page_count
, avg_page_space_used_in_percent
, forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks'), OBJECT_ID ('dbo.Details'), NULL, NULL, 'DETAILED');
GO
SET STATISTICS IO ON
GO
-- The number of reads is not just the number of pages as we would expect for
-- a scan of a heap, but is equal to the
-- number of pages PLUS the number of forwarded records:
-- 84408 + 2709 = 87117
-- During a scan, the forwarded pointers are followed for EACH row, and then
-- SQL Server goes back to the original position to continue the scan
SELECT * FROM dbo.Details;
GO
SET STATISTICS IO OFF
GO
/*Table 'Details'. Scan count 1, logical reads 87117, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.*/
So far we have seen the existence of forwarded records using sys.dm_db_index_physical_stats and the number of logical reads go up using SET STATISTICS IO ON. The next query will tell you how many times forwarded records have been traversed to satisfy the queries against the heap.
SELECT
DB_NAME(database_id) AS database_name
, OBJECT_NAME(OBJECT_ID) AS OBJECT_NAME
, forwarded_fetch_count
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks'), OBJECT_ID('dbo.Details'), NULL, NULL)
In our example, our heap had 84408 forwarded records and so far the entire heap was traversed 2 times 84408 * 2 = 168816
/*
database_name object_name forwarded_fetch_count
AdventureWorks Details 168816
*/
Before we take off, lets take the forwarded records for a spin and see if the DMV sys.dm_db_index_operational_stats displays the true count of how many times these forwarded records are used.
SELECT * FROM dbo.Details;
GO 10
SELECT
DB_NAME(database_id) AS database_name
, OBJECT_NAME(OBJECT_ID) AS OBJECT_NAME
, forwarded_fetch_count
FROM sys.dm_db_index_operational_stats (DB_ID('AdventureWorks'), OBJECT_ID('dbo.Details'), NULL, NULL)
So far our heap was traversed 2 times and we spinned the table another 10 times above with a total of 12 * 84408 forwarded records = 1012896.
/*
database_name object_name forwarded_fetch_count
AdventureWorks Details 1012896
*/
If forwarded records are a new concept for you then I would urge you to look at these excellent posts by Kalen, Paul and Simon on this topic. This post is participating in the 4th installment of the T-SQL Tuesday hosted by Mike.
whats-worse-than-a-table-scan
fragmentation-and-forwarded-records-in-a-heap
Forwarding-and-forwarded-records-and-the-back-pointer-size
INSERT and UPDATE loading practice – The impact of forwarding pointers.
Sankar,
The trackback didn’t work because you forgot to link back to Mike’s post. Please make sure to get a link in there!
Adam,
Thats what I thought initially but looking at the post in wp, I can see the trackbacks going to Mike’s blog as well as several others I linked in the posts.