3

We are experiencing an issue on one of our servers where CheckDB is blowing the resource pool 'default' ... let me elaborate..

Windows 2012 R2 Standard - 16GB RAM SQL Server 2012 Enterprise [11.0.5532.0] MAX memory 14GB RAM, MIN memory 0GB This is a dedicated SQL Server running 1 default instance of Microsoft SQL Server. Vanilla install with no special requirements or configuration.

We can CheckDB all the other databases (max size of 3GB per database, 9 user databases) however when we CheckDB the 5GB database it fails after 10 minutes reporting

"There is insufficient system memory in resource pool 'default' to run this query. [SQLSTATE 42000] (Error 50000) DBCC execution completed. If DBCC printed error messages, contact your system administrator."

This is a vendor database and cannot be altered .. it consists of 2 filegroups, both filegroups contain 248 tables that each contain 1,000 partitions.

I have tried DBCC CheckDB with combinations of ALL_ERRORMSGS NO_INFOMSGS in case that made a difference to memory requirements but there was no change in behaviour. We cannot use TABLOCK as we will not be able to lock tables. I have tried DBCC CheckFileGroup and this returns the same error message. We can try running PhysicalOnly for a lightweight check but we want to do a full CheckDB once a week.

The total disk space used (not allocated) to all the databases is 8,797 MB .. the RAM allocation to SQL Server and available is 14GB [14,336 MB]

My question is .... the entire 5GB database should fit into memory (and I have checked that more than 5GB of RAM is available..... would this volume of partitions and partitioned tables in a database using 1 or 2 filegroups cause excessive memory consumption? I cannot find any Microsoft documentation that says partitioned tables require more memory...only that partitioned tables over many filegroups can help reduce CheckDB time/resource requirements for VLDB.

I am running DBCC CheckDB WITH ESTIMATEONLY now and will update once I have the results. UPDATE: ESTIMATEONLY returns the following messages:

DBCC results for ''. CHECKDB found 0 allocation errors and 0 consistency errors in database ''. Msg 701, Level 17, State 123, Line 1 There is insufficient system memory in resource pool 'default' to run this query.

Any input will be greatly appreciated!

  • Can you also add the portion of errorlog which gets dumped when this error comes. You have to runsp_readerrorlog and look for error message related to checkdb failing. Its quite possible you would have dbcc memorystatus output, if you see that add in the question. – Shanky Nov 9 '15 at 11:31
  • You tagged question to 2008 r2 but you have described about 2012. What is exact version ? – Shanky Nov 9 '15 at 11:34
  • Thank you Shanky .. I have changed the tag. I will get that information from the error log.... DBCC CheckAlloc completes successfully (1st part of CheckDB) ... I'll go through the next bits manually and see if any individual command fails. – David Warner Nov 9 '15 at 11:42
  • The error log only documents the results from the Messages tab.. Error: 701, Severity: 17, State: 123. There is insufficient system memory in resource pool 'default' to run this query. – David Warner Nov 9 '15 at 11:43
  • We have increased the RAM in this server to 32GB and assigned 28GB to SQL... it takes 23GB of RAM to CheckDB a 5GB database ... can anybody answer why it takes 4+1/2 times the RAM to CheckDB this database with lots of partitioned tables? – David Warner Nov 9 '15 at 13:59
2

Paul's explanation might answer your question , you can take a look here http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-why-would-checkdb-run-out-of-space/. I have copy pasted a snippet from his post related to your question:

There’s another reason that DBCC CHECKDB may stop with an out-of-space error. DBCC CHECKDB has to validate the contents of the whole database. As such it needs to store info about things it has seen at one point during database scanning so that it can match that info against things it sees at later points during database scanning. These bits of info are stored in an in-memory worktable. In many cases though, the amount of info being stored exceeds the available memory and the worktable needs to spill over to tempdb. (For example, running DBCC CHECKDB against a 1TB database on a machine with 16GB of memory – the amount of info that DBCC CHECKDB needs to store the intermediate pieces of info will likely exceed the memory available to SQL Server). So – if tempdb isn’t big enough to store the worktable, it has to grow. If it can’t grow, then DBCC CHECKDB will fail because it needs the worktable to operate.

  • Thank you for the information Firasath. I don't think this is the answer as the article refers to CheckDB spilling over into TempDB and then TempDB running out of disk space ... the CheckDB process isn't spilling into TempDB as TempDB is 2GB and it's empty during the CheckDB process. – David Warner Nov 9 '15 at 15:23
  • 1
    I think the issue is something to do with hashing and sorting during the CheckDB process stealing pages from the buffer pool ... if I look in sys.dm_os_performance_counters during the CheckDB I can see that Free Memory (KB) drops to 2.3GB and Stolen Server Memory (KB) increases to around 15.6GB – David Warner Nov 9 '15 at 15:25
2

We believe the problem is that there are a large volume of partitions the sys.sysrscols grows very large due and in SQL Server there appears to be an interesting issue with the costing model for CheckDB as per this article https://www.sqlskills.com/blogs/jonathan/dbcc-checkdb-execution-memory-grants-not-quite-what-you-expect/. We think that the CheckDB makes a large memory request and this steals memory from the buffer pool which becomes exhausted when performing the CheckDB. We have increased memory to 24GB and the CheckDB on the 5GB table runs in 11 minutes, however reduce the Max Memory to 15GB and it takes over an hour, reduce it to 14GB and the buffer pool becomes exhausted. I hope this helps anybody else in the future ..... we think that partitioning can lead to higher memory requests from CheckDB due to the larger size of the sys.sysrscols table ... however I am happy to be proven wrong :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.