Heap tables and forwarded records in SQL Server

In SQL Server, the smallest storage unit is an 8KB page with a 96-byte header that stores system information. 

Data in tables can be organized in two ways:

• Clustered index

  
  
  
  
  

B+ — . SQL Server .

• (heap)

  
  
  
  
  

— . - . . , . , .

, , SQL Server PFS- (Page Free Space). , . , ( — 64 ) . PFS , , :

forwarded-

, SQL Server - . , SQL Server Forwarded Record ( ). Forwarded Record forwarding- .

forwarded-.

Employees

CREATE DATABASE SQLShack;
GO
USE SQLShack;
GO
CREATE TABLE Employees
([EmpID]     INT IDENTITY(1, 1), 
 [Name]      NVARCHAR(100), 
 [BirthDate] DATETIME, 
 [Salary]    INT
);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

.

INSERT INTO Employees
VALUES('Rajendra', '1986-03-16', 50000);
GO 2000

INSERT INTO Employees
VALUES ('Sonu', '1980-11-29', 50000);
GO 1000
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

- sys.indexes



, index_id



. :

SELECT OBJECT_NAME(object_id) AS tablename, 
        index_id, 
        type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('Employees');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

( , , forwarded-) (Dynamic Management Function, DMF) sys.dm_db_index_physical_stats



. DETAILED- DMF:

SELECT
    OBJECT_NAME(DIPS.object_id) as DBTableName,
    DIPS.index_type_desc,
    DIPS.avg_fragmentation_in_percent,
    DIPS.forwarded_record_count,
    DIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), DEFAULT, DEFAULT, DEFAULT, 'DETAILED') AS DIPS
WHERE OBJECT_NAME(DIPS.object_id) = 'Employees' AND forwarded_record_count is NOT NULL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Employees



17 (page_count



) forwarded- (forwarded_record_count



).

. :

INSERT INTO Employees
VALUES ('Kusum', '1985-09-25', 60000);
GO 2000
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

DMF- forwarded-:

• 17 27.

  
  
  
  
  

• (avg_fragmentation_in_percent
  
  
  
  ) 33 50%.

  
  
  
  
  

• forwarded- .

  
  
  
  
  

[Name]



Employee



:

UPDATE Employees
SET [Name] = 'Rajendra Kumar Gupta'
WHERE [Name] = 'Rajendra';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

DMF forwarded-:

• 27 35.

  
  
  
  
  

• forwarded- 747.

  
  
  
  
  

, :

UPDATE Employees
SET [Name] = 'Kusum Kashish Agarwal'
WHERE [Name] = 'Kusum'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

:

• 35 46.

  
  
  
  
  

• forwarded- 747 1752.

  
  
  
  
  

SQL Server forwarded-, page_count



forwarded_record_count



.

: forwarded-?

, . [Name]



. STATISTICS IO



:

SET STATISTICS IO ON;
SELECT *
FROM dbo.Employees
WHERE name LIKE 'Rajendra%';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Table Scan :

, SQL Server IAM- . , , :

, . - 3 (Page 3), 2 (Page 2):

1798 :

, DML.

forwarded-

(staging tables). forwarded- — . , .

: . , .

- , — Forwarded Records, , . SQL Server 2008 ALTER TABLE ... REBUILD



. .

, Employee:

ALTER TABLE Employees REBUILD;

REBUILD

:

• REBUILD: 1798

  
  
  
  
  

• REBUILD: 40

  
  
  
  
  

ALTER TABLE ... REBUILD



forwarded-. forwarded- . :

• 46 40.

  
  
  
  
  

• forwarded- ( 1752).

  
  
  
  
  

• 33%.

  
  
  
  
  

, , , . - forwarded- heap-. - forwarded-   - . , .

---

MS SQL Server - : " SQL Server"