Freigeben über

Delete大量数据后SQL Server性能下降

  • Artikel

今天给大家分享一个”删除大量数据后SQL Server性能下降”的案例。一般而言，数据库数据减少后，应该有助于提高SQL server的整体性能。可是在这个案例中，情况恰恰相反。

 

症状

=========

-          删除大量数据后SQL Server性能下降

-          一些存储过程之前运行20分钟左右，现在需要运行2-3个小时。

 

背景信息

=========

-          大量数据通过DELETE语句而删除

-          数据删除后，客户进行了相关的维护工作 : 重建索引和更新统计数据

-          性能变慢的存储过程会对一些表做很多的”DELETE”，”INSERT”和”SELECT”操作。

 

调查

=========

-          相关的表都是堆( heap table)

 

-          这些表中并没有大量数据

 

-          DBCC CHECKCONTIG 结果显示表很大，但其页的密度 (Page Density) 却相当小。

 

DBCC SHOWCONTIG scanning 'tblA' table...

Table: 'tblA' (322816212); index ID: 0, database ID: 14

TABLE level scan performed.

- Pages Scanned................................: 1779939 à 13.6GB

- Extents Scanned..............................: 223475

- Extent Switches..............................: 223474

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 99.56% [222493:223475]

- Extent Scan Fragmentation ...................: 23.65%

- Avg. Bytes Free per Page.....................: 8059.1

- Avg. Page Density (full).....................: 0.43%

 

DBCC SHOWCONTIG scanning 'tblB' table...

Table: 'tblB' (1005246636); index ID: 0, database ID: 14

TABLE level scan performed.

- Pages Scanned................................: 215600 à 1.6GB

- Extents Scanned..............................: 27269

- Extent Switches..............................: 27268

- Avg. Pages per Extent........................: 7.9

- Scan Density [Best Count:Actual Count].......: 98.83% [26950:27269]

- Extent Scan Fragmentation ...................: 38.87%

- Avg. Bytes Free per Page.....................: 7998.3

- Avg. Page Density (full).....................: 1.18%

 

 

原因

=========

-          当表上有聚集索引时，删除操作会释放空页。然而，从堆中删除行时，数据库引擎可以使用行锁定或页锁定进行操作。结果，删除操作导致的空页将继续分配给堆。未释放空页时，数据库中的其他对象将无法重用关联的空间。

-          虽然表中没有大量数据，但是它们拥有大量的几乎为空的数据页。扫描表因此变得十分花时间。

 

解决方案

=========

- 若要删除堆中的行并释放页，我们可以使用下列方法之一。

· 在 DELETE 语句中指定 TABLOCK 提示。使用 TABLOCK 提示会导致删除操作获取表的共享锁，而不是行锁或页锁。这将允许释放页。

· 如果要从表中删除所有行，请使用 TRUNCATE TABLE。

· 删除行之前，请对堆创建聚集索引。删除行之后，可以删除聚集索引。与先前的方法相比，此方法非常耗时，并且使用更多的临时资源。

参考信息

=========

使用 DELETE 删除行
https://msdn.microsoft.com/zh-cn/library/ms189245.aspx

 

Space that a table uses is not completely released after you use a DELETE statement to delete data from the table in SQL Server

https://support.microsoft.com/kb/913399

 

 

结果

========

-          客户的应用逻辑允许将表清空。因此我们选择了TRUCATE TABLE的方法。完成后，出问题的存储过程的性能都恢复到了从前。

Comments

  • Anonymous
    December 06, 2011
    sql server 2008 中的 ALTER TABLE REBUILD是否也可以解决这个堆表的问题？
  • Anonymous
    February 19, 2012
    只有ALTER INDEX REBUILD的命令,用来重建索引,如果是堆表的话,是没有聚集索引的,必修先建立一个(即方法3)
  • Anonymous
    January 18, 2013
    最后还是在原表先建立一个聚集索引，当删除了大量数据之后，发现性能下降，就重建索引这样才可以释放空闲的页面空间博主的下面的两个方法感觉不靠谱，因为我之前看过徐海蔚老师的《SQLSERVER企业级管理实践》跟博主所述的有很大出入·         在 DELETE 语句中指定 TABLOCK 提示。使用 TABLOCK 提示会导致删除操作获取表的共享锁，而不是行锁或页锁。这将允许释放页。·         如果要从表中删除所有行，请使用 TRUNCATE TABLE。