CheckDB on Partitioned Table - Insufficient Memory in resource Pool default

Question

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!

Answer 1

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.

Answer 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 :-)