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 , , :





0x00









0x01





50%





0x02





51 — 80%





0x03





81 — 95%





0x04





96 — 100%





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"








All Articles