sql - SQL如何决定添加新的数据页

Question

我试图理解当我们向表中插入一些新记录时，SQL 如何分配新的数据页。

我在新数据库中创建了一个名为 Employee 的示例表 -

Create Database TestDb
GO
Use TestDb
GO
Create table Employee (
EmployeeName char(1000))   GO
Insert into Employee values ('Employee1')

DBCC IND('TestDb','dbo.Employee',-1)

运行 DBCC 后，我看到了 2 个页面。一个是 IAM 页面 (PageType =10 )，另一个是数据页面 (PageType = 1)，它保存实际数据。

后来我用DBCC Page验证了数据页的实际内容

DBCC TRACEON(3604)
DBCC PAGE('TestDb',1,298,3)

我看到了 SQL 如何计算 m_freecnt 字节数 -

= 1007 字节 RecordSize + 96 字节 header + 2 字节偏移量 = 1105 字节

即8K 页面 = 8192 字节 = 8192 - 1105 = 7087 字节可用。

现在，我继续向该表添加记录，以便了解该页面将容纳多少条记录，以及 SQL 何时分配一个新页面，同时牢记 m_FreeCnt。

(Record Size = 1007 & Offset Bytes = 2 )

添加了第二条记录 -

Insert into Employee values ('Employee2') GO

最后的免费计数 = 7087 即 7087 - 1007 - 2 = 6087 => m_FreeCnt = 6078

Insert into Employee values ('Employee3') GO

最后的免费计数 = 6078 即 6078 - 1007 - 2 = 5069 => m_FreeCnt = 5069

Insert into Employee values ('Employee4') GO

最后的免费计数 = 5069 即 5069 - 1007 - 2 = 4060 => m_FreeCnt = 4060

Insert into Employee values ('Employee5') GO

最后的免费计数 = 4060 即 4060 - 1007 - 2 = 3051 => m_FreeCnt = 3051

Insert into Employee values ('Employee6') GO

最后的免费计数 = 3051 即 3051 - 1007 - 2 = 2042 => m_FreeCnt = 2042

Insert into Employee values ('Employee7') GO

最后自由计数 = 2042 即 2042 - 1007 - 2 = 1033 => m_FreeCnt = 1033

到目前为止一切正常。现在该数据页中还剩下 1033 字节。一旦我添加第 8 条记录，理想情况下它不应创建另一个页面，因为可用字节数为 1033 字节，足以容纳第 8 条记录(1009 字节就足够了)。然而，SQL 确实创建了一个新的日期页来保存第 8 条记录。

我插入了第 8 条记录并运行 DBCC IND 来检查 -

Insert into Employee values ('Employee7') GO

DBCC IND('TestDb','dbo.Employee',-1)

现在它已经创建了一个新的数据页，PageNumber = 300。

这部分我没看懂。 SQL是否保留了除了[ header (96)+数据部分+每页偏移量2字节]之外的一些字节？

您可以尝试运行上述查询，如果我在这里遗漏了任何内容，请告诉我？或者我们是否不应该关心 SQL 的这些内存细节？

谢谢。

Answer 1

只需重新表述另一个答案即可使其清楚:
一旦达到页面填充的 81%，该页面的 PFS 记录将把前 2 位更改为 11，这意味着“页面已填充 95%”并且 SQL 将不允许插入即使有足够的空间(超过 5% 是可用的)，也可以有更多记录。

有两种方法可以解决这个问题:
简单:创建聚集索引 - 然后您可以根据需要将填充因子设置为 100%。
困难:如果页面上的可用空间超过 19%，则可以使用一条 INSERT 语句插入多条记录，以 100% 填充页面。
请参阅示例:

CREATE TABLE TestTable(F0 SMALLINT);
GO
INSERT INTO TestTable (F0) 
SELECT TOP 699 1 FROM sys.messages;
GO
BEGIN TRAN 
  INSERT INTO TestTable (F0) 
  SELECT TOP 37 2 FROM sys.messages;
  SELECT 
    (SELECT COUNT(*) FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('TestTable'), NULL, NULL, 'DETAILED') WHERE Page_type = 1) AS Pages,
    (SELECT COUNT(*) FROM TestTable) AS "Count";
ROLLBACK
GO
BEGIN TRAN 
  INSERT INTO TestTable (F0) VALUES (3)
  GO 2
  SELECT 
    (SELECT COUNT(*) FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('TestTable'), NULL, NULL, 'DETAILED') WHERE Page_type = 1) AS Pages,
    (SELECT COUNT(*) FROM TestTable) AS "Count";
ROLLBACK
GO
DROP TABLE TestTable
GO

达到 699 条记录后，您可以一次插入 37 条新记录，如果逐条记录插入，则只能插入一条记录。