Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Blog about SQL Server and related technologies.
Browse by Tags · View All
SQL Server 20
Database Administration 12
Denali 10
SQLServer Denali 9
SQL Server 2012 8
#SQLServer 7
SQLServer 2012 6
PowerShell 4
Performance 4

Archive · View All
August 2011 6
September 2011 4
October 2011 3
November 2011 3
February 2013 2
January 2013 2
December 2011 2
January 2012 2
April 2013 1
November 2012 1


Nov 10 2011 12:00AM by Ana   

Yesterday I run into interesting situation with DBCC CHECKDB WITH ESTIMATEONLY command. Later I find out that it’s a known bug in SQL Server 2008 R2, but I experienced it for the first time and I think that it’s a good topic for my post. Here is the story.
Yesterday, after I restored database from the backup I decided to run DBCC CHECKDB on it. Data file size of tempdb database was 2.5GB. I know that DBCC CHECKDB is a resource-intensive task and it also require space in tempdb. I wanted to be sure that tempdb size is appropriate to accommodate the CHECKDB requirements so I run DBCC CHECKDB WITH ESTIMATEONLY.


Here is the output:
Estimated TEMPDB space needed for CHECKALLOC (KB): 40443
Estimated TEMPDB space needed for CHECKTABLES (KB): 13619

It’s ~ 53MB. I expected larger values because database occupies 28GB of space. But I thought that tempdb with 2.5GB of data file size is enough because there will be no other workload on the server.

I run DBCC CHECKDB(DatabaseName) and command completed successfully in about 11 minutes. Later I noticed that the size of tempdb data file is 3GB and data file auto growth occurred around the time of the DBCC CHECKDB execution.
That was very strange. Estimated tempdb space was about 53MB but it used more than 2.5GB of tempdb space. It is almost 50 times more than estimated space needed and it can lead to problem especially on very large databases. I decided to run DBCC CHECKDB command again and see usage of tempdb database.
Here is the script:


SELECT  @read = SUM(fs.num_of_bytes_read),
@write = SUM(fs.num_of_bytes_written)
FROM    tempdb.sys.database_files AS df
INNER JOIN    sys.dm_io_virtual_file_stats(2, NULL) AS fs
ON fs.file_id = df.file_id
WHERE df.type = 0

DBCC CHECKDB(DatabaseName)

SELECT  (SUM(fs.num_of_bytes_read) - @read) / 1024. / 1024. AS tempdb_read_MB,
(SUM(fs.num_of_bytes_written) - @write) / 1024. / 1024. AS tempdb_write_MB,
(SELECT  internal_objects_alloc_page_count * 8. / 1024
FROM    sys.dm_db_task_space_usage
WHERE   session_id = @@SPID) AS internal_objects_alloc_size_MB
FROM    tempdb.sys.database_files AS df
JOIN    sys.dm_io_virtual_file_stats(2, NULL) AS fs
ON fs.file_id = df.file_id
WHERE df.type = 0

tempdb_read_MB         tempdb_write_MB        internal_objects_alloc_size_MB
3192.21093750000       3192.60156250000       3194.562500

From results, we can see that more than 3.1GB of data are written and read during DBCC CHECKDB execution.
I executed few more tests with different databases to see a difference between estimated tempdb space and the real usage during CHECKDB operation and only first database had issue with it.

I googled and found the excellent blog post by Paul Randal (blog|twitter): How does DBCC CHECKDB WITH ESTIMATEONLY work? At the end of the post Paul wrote:

 ”PS Beware also that I’ve heard of several cases of a bug in SQL Server 2008 R2 where the output is incorrectly very low. The dev team is aware of this issue and are working on it.”

Be careful if you need to rely on DBCC CHECKDB WITH ESTIMATEONLY output.  You can be surprised, especially if you are working with large or very large databases.

Republished from DBA Journey [55 clicks].  Read the original version here [32134 clicks].

121 · 1% · 430


Your Comment

Sign Up or Login to post a comment.

"DBCC CHECKDB WITH ESTIMATEONLY: Do you trust it?" rated 5 out of 5 by 1 readers
DBCC CHECKDB WITH ESTIMATEONLY: Do you trust it? , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]