次の方法で共有

如何大幅提高DBCC CHECKDB/DBCC CHECKTABLE的性能

  • [アーティクル]

 

随着时间的推移，数据库变的越来越大，几百个GB甚至几个TB大小的数据库越来越多。为了检查数据库的完整性，定期运行DBCC CHECKDB/CHECKTABLE是最佳实践。但是随着数据库的增大，如何缩短DBCC CHECKDB/CHECKTABLE的运行时间是DBA常常需要面对的一个挑战。本短文介绍一些方法，可以大幅缩短常规CHECKDB/CHECKTALE 的运行时间。

 

正常情况下，CHECKDB/CHECKTABLE的运行不会对数据库使用排它锁，而是使用内部数据库快照(internal database snapshot)。 这个内部数据库快照实质就是Sparse Filestream, 它使用sparse file，COPY-ON-WRITE技术。详细的工作原理可以参考如下的文档：

 

数据库快照的工作方式

https://msdn.microsoft.com/en-us/library/ms187054(v=SQL.105).aspx

简单说，对数据库快照的读操作如下图所示：

 

如果你想观察DBCC CHECKDB/CHECKTABLE运行时的快照，你可以使用streams.exe工具。我使用它观测到如下的结果：

上图中的 “MSSQL_DBCC10:$DATA”就是附加在testdb.mdf后面的Sparse Filestream。后面的那串数字是数据库加上stream的总的大小，这个和下面的语句观察到的size_on_disk_bytes是一致的：

 

select * from sys.dm_io_virtual_file_stats(5,1)

 

 

但是要注意到，因为sparse filestream并不实际占有磁盘大小，上面的大小只是一个空间的保留，并不是磁盘上真的有这么多的数据存在，并不真正占有磁盘的这么大的空间。

 

言归正传，上面介绍的internal snapshot （也就是sparse filestream）有什么关系呢？

 

先做个实验，运行CHECKDB几次看看运行时间：

 

DBCC CHECKDB(TESTDB)

 

这个运行了大概50秒的时间。然后我使用TABLOCK选项测试几次：

 

DBCC CHECKDB(TESTDB)

withTABLOCK

 

天啊，它只需要大概5秒的时间就跑完了，整整快了10倍！读到这里，你知道了第一个大幅缩短CHECKDB/CHECKTABLE的办法，就是使用TABLOCK。这个hint 告诉SQL server 使用锁来进行检查，但也影响了数据库用户的使用。比如在检查某个table 的时候，就可能无法对这个table进行修改。 那么有没有更好的不影响用户的办法呢？有的，就是使用snapshot 数据库。

 

首先建立一个snapshot 数据库：

 

createdatabase myTESTDB_snapshot

on

( name =TESTDB_Data ,filename='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TESTDB_Data.ss')

asSNAPSHOTOF TESTDB

 

然后对这个snapshot数据库进行CHECKDB：

 

DBCC CHECKDB(myTESTDB_snapshot)

 

结果令人惊讶，它也仅仅使用了5秒，和使用TABLOCK一样的性能！ 太令人兴奋了，不影响用户的情况下比原来的CHECKDB快了10倍。你发现了什么问题吗？恩，对，我是对myTESTDB_snapshot进行CHECKDB，而不是TESTDB。这样可以么？可以的， 原因自己思考。可以参考snapshot的工作原理来考虑。

 

那么对CHECKTABLE有什么不一样么？ 我测试的结果类似。 就是说， 使用snapshot 数据库来进行CHECKTABLE的性能和使用TABLOCK的性能类似，都能大幅缩短检查的时间。测试的结果如下：

 

DBCC CHECKDB

40-50 seconds

DBCC CHECKDB with TABLOCK

5 seconds

DBCC CHECKDB on snapshot database

5 seconds

DBCC CHECKTABLE Batch

8-12 minutes

DBCC CHECKTABLE Batch with TABLOCK

18 seconds

DBCC CHECKTABLE Batch on Snapshot database

20 seconds

Comments

  • Anonymous
    January 12, 2013
    你好，我试验了下，如果开启一个会话Session后use 数据库，会保持一个S锁在DB上然后运行DBCC Checkdb with tablock 会对DB先加载X锁成功，再释放X锁，保留DB级别的S锁，然后进行check，并且会提示“DBCC CHECKDB 将不检查 SQL Server 目录或 Service Broker 一致性，因为无法创建数据库快照或指定了 WITH TABLOCK。”也就是说加上with tablock实际不会产生内部快照。是这样么？
  • Anonymous
    February 06, 2013
    WITH TABLOCK就不需要快照了，因为快照的产生是为了保证事务一致性。而WITH TABLOCK就可以实现了。
  • Anonymous
    November 27, 2013
    The comment has been removed
  • Anonymous
    October 19, 2015
    测试了一下，对于一个18gb的数据库进行测试，不管是否添加with tablock,还是创建数据库快照，与默认的方式相比，没有任何性能提高，几乎是一样的