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

michael1013 2011-12-29 10:39:12
微软亚太区数据库技术支持组 官方博客

今天给大家分享一个”删除大量数据后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

[color=#FF0000]- 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%[/color]




原因

=========

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



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



解决方案

=========

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

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



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



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

参考信息

=========

使用 DELETE 删除行
http://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

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





结果

========

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








微软亚太区数据库技术支持组 官方博客
...全文
1106 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
movta 2012-01-06
  • 打赏
  • 举报
 回复
求详细解决方法中。。。
qiangorqiang 2012-01-06
  • 打赏
  • 举报
 回复
重建表试一下。
zjl8008 2011-12-30
  • 打赏
  • 举报
 回复
关注中。等待最优方案
nzperfect 2011-12-29
  • 打赏
  • 举报
 回复
hi michael1013，以后发贴子，前面加上【分享】，这样别人就知道不是提问了。
prcak47 2011-12-29
  • 打赏
  • 举报
 回复
做好数据库维护计划，定期重建索引可以解决问题。
忠臣 2011-12-29
  • 打赏
  • 举报
 回复
应该试着清空表内容而不是删除数据
黄_瓜 2011-12-29
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 perfectaction 的回复:]

引用 4 楼 michael1013 的回复:
因为本例中，最终要回到Heap的结构，可以通过添加聚集索引，然后去除聚集索引的方式，让数据页被重新组合。


SQL Server 2008，应该也可以用 ALTER TABLE tbName REBUILD来重组吧。
[/Quote]
好我去试试
黄_瓜 2011-12-29
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 michael1013 的回复:]

引用 2 楼 beirut 的回复:

这如果不是全部删除改怎么办？


因为本例中，最终要回到Heap的结构，可以通过添加聚集索引，然后去除聚集索引的方式，让数据页被重新组合。
[/Quote]
删除行之前，请对堆创建聚集索引。删除行之后，可以删除聚集索引。与先前的方法相比，此方法非常耗时，并且使用更多的临时资源。


没有更优秀的方法吗
nzperfect 2011-12-29
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 michael1013 的回复:]
因为本例中，最终要回到Heap的结构，可以通过添加聚集索引，然后去除聚集索引的方式，让数据页被重新组合。
[/Quote]

SQL Server 2008，应该也可以用 ALTER TABLE tbName REBUILD来重组吧。
michael1013 2011-12-29
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 xiaohai168 的回复:]

SQL code

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

是这个意思吗？ delete from TB with(tablock)
[/Quote]

对啊。
xiaohai168 2011-12-29
  • 打赏
  • 举报
 回复 


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



是这个意思吗？  delete from TB with(tablock)
michael1013 2011-12-29
  • 打赏
  • 举报
 回复
[Quote=引用 2 楼 beirut 的回复:]

这如果不是全部删除改怎么办？
[/Quote]

因为本例中，最终要回到Heap的结构，可以通过添加聚集索引，然后去除聚集索引的方式，让数据页被重新组合。
勿勿 2011-12-29
  • 打赏
  • 举报
 回复
嗯，是这样的哦！
黄_瓜 2011-12-29
  • 打赏
  • 举报
 回复
这如果不是全部删除改怎么办？
jmx123456789 2011-12-29
  • 打赏
  • 举报
 回复


顶...
--小F-- 2011-12-29
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 perfectaction 的回复:]
引用 4 楼 michael1013 的回复:
因为本例中，最终要回到Heap的结构，可以通过添加聚集索引，然后去除聚集索引的方式，让数据页被重新组合。


SQL Server 2008，应该也可以用 ALTER TABLE tbName REBUILD来重组吧。
[/Quote]
大叔 这个何解？
--小F-- 2011-12-29
  • 打赏
  • 举报
 回复
[Quote=引用 14 楼 fredrickhu 的回复:]
一定要用行锁？
[/Quote]

写错 表锁？
--小F-- 2011-12-29
  • 打赏
  • 举报
 回复
一定要用行锁？
michael1013 2011-12-29
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 perfectaction 的回复:]

hi michael1013，以后发贴子，前面加上【分享】，这样别人就知道不是提问了。
[/Quote]
好，了解了。多谢！
SQL Server 所依赖的磁盘已接近满,速度很慢该如何解决
本文描述了一种情况，SQL Server因磁盘空间不足导致性能下降。作者通过查询数据库大小，发现msdb数据库占用大量空间，主要由备份历史记录造成。通过执行存储过程`sp_delete_backuphistory`清理历史记录，将msdb大小显著减少。接着，作者创建了一个维护计划以定期清理历史，确保未来不再发生类似问题。
使用SQL Server删除大量数据
本文介绍了在SQL Server中删除大量数据的三种方法：使用DELETE语句按条件删除，使用TRUNCATE TABLE快速清空表，以及采用BATCH DELETE批量删除。同时，讨论了如何通过优化索引来提升删除操作的性能。
SQL SERVER 数据库日志文件收缩
本文详细介绍了 SQL Server 数据库日志文件收缩的方法，包括可视化操作和 T-SQL 代码方式。重点讲解了如何在简单恢复模式下收缩日志，并强调收缩前后恢复模式的切换及完整备份的重要性。还提供了多数据库批量收缩脚本以及生产环境中需要注意的问题。
43、SQL Server性能调优与对象实现指南
本文介绍了SQL Server性能调优的相关知识，涵盖常见问题解答、自测题解析、数据库对象创建与管理流程、恢复模型选择策略等内容。重点讲解了如何通过DMV诊断性能问题、使用服务器端跟踪分析查询、利用资源管理器控制会话资源、实施表分区优化大型数据处理，以及不同恢复模型的特点和应用场景。
SQL SERVER 2000压力测试
本文是SQL SERVER 2000的压力测试报告，针对其在大容量和大记录量情况下的性能进行测试。测试分两部分，分别在空数据库和插入大量数据后进行基本操作并记录时间。结果显示，在大容量和大记录量环境下，SQL SERVER 2000性能能保持较高水平，可满足普通企业级应用。
MS-SQL Server

34,876

社区成员

254,640

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章