SQL Server 2008：由于与 LOB 数据相关的幻影记录，表大小增加
生产环境如下表。它被大量更新（大量插入和删除）。该表包含 LOB 数据类型 - ntext 和 nvarchar(max)。 数据不断地被删除和插入到该表中。但总行数相当稳定，约为 150,000 行。
但由于未知原因，表大小仅增加了。这意味着删除数据的空间没有被释放。
例如，此时表中有150,000行，占用约60GB。如果我将此数据复制到新表（简单插入），那么我的数据将仅占用 10GB。
我尝试做的事情：
- 收缩文件或数据库对我没有帮助
- 索引重建对我没有帮助
- DBCC CLEANTABLE 对我没有帮助
这是表结构：
CREATE TABLE dbo.T_Test(
KeyHash nvarchar(50) NOT NULL,
SiteDomainId int NOT NULL,
srcFullUrl nvarchar(max) NOT NULL,
srcResponse ntext NOT NULL,
srcExpirationDate datetime NOT NULL,
srcKey nvarchar(max) NOT NULL,
srcCachePeriodInMinutes int NOT NULL,
srcNumOfHits int NOT NULL,
srcVital bit NOT NULL,
CONSTRAINT PK_T_Test_1 PRIMARY KEY NONCLUSTERED
(
KeyHash ASC,
SiteDomainId ASC
))
GO
CREATE CLUSTERED INDEX [IX_T_Test_srcExpirationDate_ppa] ON dbo.T_Test
(
srcExpirationDate ASC
)
GO
我确切地知道问题出在与 LOB 数据相关的幽灵记录中。 select * from sys.dm_db_index_physical_stats(db_id(), object_id('MyTable'), null, null, N'DETAILED') 返回以下内容：
index_type_desc alloc_unit_type_desc record_count ghost_record_count
CLUSTERED INDEX LOB_DATA 394996 2869376
但 Ghost 进程正常工作，即 Ghost 记录被删除对于聚集索引的IN_ROW_DATA。
目前我不知道如何删除幽灵记录并回收空间。 唯一的办法就是截断表并重新上传数据。
任何如何避免此问题的建议都是有价值的。谢谢。
我的环境配置是Microsoft SQL Server Web版（64位）10.0.2531.0
A have the following table on Production environment. It is heavily updated (lots of inserts and deletes). This table contains LOB data types - ntext and nvarchar(max).
Data is constantly removed and inserted in this table. But total row count is quite stable and is about 150,000.
But for unknown reason table size is only increased. It means that space of deleted data is not release.
For example, at this moment there are 150,000 rows in the table and it occupies about 60GB. If I copy this data to new table (simple insert into) then my data will occupy only 10GB.
What I tried to do:
- Shrink file or database is not helping me
- Index rebuild is not helping me
- DBCC CLEANTABLE is not helping me
Here's the table structure:
CREATE TABLE dbo.T_Test(
KeyHash nvarchar(50) NOT NULL,
SiteDomainId int NOT NULL,
srcFullUrl nvarchar(max) NOT NULL,
srcResponse ntext NOT NULL,
srcExpirationDate datetime NOT NULL,
srcKey nvarchar(max) NOT NULL,
srcCachePeriodInMinutes int NOT NULL,
srcNumOfHits int NOT NULL,
srcVital bit NOT NULL,
CONSTRAINT PK_T_Test_1 PRIMARY KEY NONCLUSTERED
(
KeyHash ASC,
SiteDomainId ASC
))
GO
CREATE CLUSTERED INDEX [IX_T_Test_srcExpirationDate_ppa] ON dbo.T_Test
(
srcExpirationDate ASC
)
GO
What I know exactly that the issue is in the ghost records related to LOB data.select * from sys.dm_db_index_physical_stats(db_id(), object_id('MyTable'), null, null, N'DETAILED') returned the following:
index_type_desc alloc_unit_type_desc record_count ghost_record_count
CLUSTERED INDEX LOB_DATA 394996 2869376
But ghost process is working normally, i.e. ghost records are removed for IN_ROW_DATA of clustered index.
At this moment I don't have idea how to delete ghost records and reclaim space.
The only way is to truncate table and upload data again.
Any suggestion how to avoid this issue are valuable. Thank you.
Configuration of my environment is Microsoft SQL Server Web Edition (64-bit) 10.0.2531.0
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
可能是幽灵清理永远赶不上删除（来自 Paul Randal 的 SF）特别考虑到您的使用模式。我记得在 SF 上看到过这个，但我从未遇到过这个问题，也从未尝试过 Paul 建议的修复，所以 YMMV 抱歉。
Could be the ghost clean up never catches up with DELETEs (on SF from Paul Randal) especially given your usage pattern. I remember seeing this on SF but I've never had this issue and have never tried Paul's suggested fix so YMMV sorry.
“可能是幽灵清理永远赶不上删除（在 Paul Randal 的 SF 上），特别是考虑到您的使用模式。我记得在 SF 上看到过这个，但我从未遇到过这个问题，也从未尝试过 Paul 建议的修复，所以 YMMV 抱歉。 ”
我读过保罗·兰德尔的方法。它确实有效，但仅适用于 IN_ROW_DATA。在我的例子中，IN_ROW_DATA 的幻影记录被清除，但 LOB 的幻影记录未被清除。我在生产循环上运行并不断进行表扫描。在这种情况下，IN_ROW_DATA 的幻影记录接近于零，但它并没有改变 LOB 幻影记录的行为。
"Could be the ghost clean up never catches up with DELETEs (on SF from Paul Randal) especially given your usage pattern. I remember seeing this on SF but I've never had this issue and have never tried Paul's suggested fix so YMMV sorry."
I have read Paul Randal approach. And it really works but only for IN_ROW_DATA. In my case ghost records for IN_ROW_DATA are cleaned, but ghost records for LOB are not cleaned. I ran on Production loop with constant table scan. Ghost records for IN_ROW_DATA are close to zero for this case, but it didn't change behaviour of LOB ghost records.
我已重新启动 sql server 进程，问题已解决。
I have restarted sql server process and the issue has been resolved.
请参阅 SQL 2008 R2 SP1 累积更新 4
http://support.microsoft.com/kb/2622823
See cumulative update 4 for SQL 2008 R2 SP1
http://support.microsoft.com/kb/2622823