4

UPDATE 9/12/19 - See my comment below for a status update.

I am writing to see if anyone can help me out with a problem that I just can't seem to figure out. This one is going to be a doosey and I am going to try and list out as much of the important info that I can but if I miss anything please let me know and I would be happy to provide whatever info you might need to be able to assist if you're willing?

The symptom that I am experiencing is that I have dbcc checkdb locking up when running against a VLDB (approx 1TB) as one of the tasks in a Maintenance Plan and the error log is reporting the error: Failed to allocate BUFs: FAIL_BUFFER_ALLOCATION 7 (sometimes 8) and then the error log starts filling up with memory charts (best way I can describe it, see attached screenshot) about physical and virtual memory.

So here is the scenario. We are currently beginning testing of our NEWSERVER before we migrate off our OLDSERVER. Everything is working as expected on our OLDSERVER. The problem is occurring on our NEWSERVER in the PROD instance during our nightly Maintenance Plan routine. There are multiple db's present in the instance but the one that we are concerned with is DB1. DB1 is made up of 2 data files and 1 log file. On the OLDSERVER the .mdf (519 GB) is located on H:, the .ndf (200 GB) is located on E:, and the .ldf (313 GB) on D:. On the NEWSERVER both data files are on E: and the log file is on D:. Note: I was not involved in the configuration of the database having 2 data files or their location, or the setup/configuration of either of the servers.

On the OLDSERVER the maintenance plan (consisting of a Check Database Integrity task, Full Database Backup, and a Maintenance Cleanup Task and is configured to run against DB1 only) completes nightly with no issues. On the NEWSERVER the maintenance plan (set up exactly the same way) will sometimes complete but is mostly slowing to a snail's crawl (or find something even slower than a snail) and will eventually fail during the Check Database Integrity task .

I can run the DBCC CHECKDB manually and sometimes it will complete in a very timely fashion but other times even the manual run will exhibit the same behavior. Not that I know if either of these settings are directly applicable, but I have tried with Lock Pages in Memory both turned on and off, no difference, and I have tried with Instant File Initialization turned both on and off, no difference.

Here are the details of the PHYSICAL server that we'll call NEWSERVER.

  • OS: Windows Server 2016 Standard - 6.3 (14393)
  • Processor Count: 32
  • Memory: 384 GB (382 GB usable)
  • Drives (All SSD configured with) : OS (C:) -181 GB free of 243 GB | Logs (D:) - 117 GB free of 488 GB | Data (E:) - 2.86 TB free of 3.81 TB | Backup (F:) - 1.44 TB free of 1.9 TB
  • SQL Server 2016 Standard - SP2 CU3 (13.0.5216.0)
  • Number of instances: 4 (PROD, DEV, TEST, TRAIN)
  • Max Memory Configuration per Instance: PROD (131072 MB) | DEV (65536 MB) | TEST (65536 MB) | TRAIN (32768 MB)

Here are the details of the PHYSICAL server that we'll call OLDSERVER

  • OS: Windows Server 2016 Standard - 6.3 (14393)
  • Processor Count: 24
  • Memory: 384 GB
  • Drives (Spindle & SSD Mix): OS (C: SSD) -163 GB free of 249 GB | Logs (D: 15k Spindle) - 197 GB free of 557 GB | ProdData (E: SSD) - 604 GB free of 865 GB | Backup (F: 10k Spindle) - 1.46 TB free of 2.18 TB | NonProdData (G: SSD) - 591 GB free of 1.08 TB | ProdData2 (H: SSD) - 231 GB free of 743 GB
  • SQL Server 2016 Standard - SP2 CU2 (13.0.5153.0)
  • Number of instances: 5 (PROD, DEV, TEST, TRAIN, PROD2)
  • Max Memory Configuration per Instance: PROD (131072 MB) | DEV (65536 MB) | TEST (65536 MB) | TRAIN (32768 MB) | PROD2 (32768 MB)

Attached are the first (almost 50) lines of the error log (I can provide more if needed. enter image description here

Any help or ideas are much appreciated!

  • 1
    Well it turns out after a couple monthly of working with Microsoft on this issue they have determined that it is now a "known issue" with DBCC CHECKDB and VLDB's and that a fix will be coming in SQL 2016 SP3. When I asked if the issue was present in SQL 2017 they said that it was and that they will most likely release in a service pack there too. That one was interesting because I thought they weren't doing service packs any more but she said that this was a big enough issue that they might have to do it in a service pack... so now I wait... – TattooedDBA Sep 12 at 13:11
  • 1
    You should make this comment an answer. – James Jenkins Sep 12 at 18:13
3

While I can't tell from the OLDSERVER data, it looks like NEWSERVER has at least 2 NUMA Nodes whereas I'm not sure if OLDSERVER has multiple NUMA Nodes or a single node.

Assuming, for the moment, that the OLDERSERVER only had a single NUMA Node, it does seem you're hitting a known issue which was fixed in SQL Server 2016 SP2 CU5, whereas you're currently on SQL server 2016 SP2 CU3.

https://support.microsoft.com/en-us/help/4470916/fix-out-of-memory-error-occurs-when-database-node-memory-kb-drops-belo

An "Out of Memory" error can occur when a Database Node Memory (KB) becomes less than 2 percent of the target size, and it cannot discard database pages on the node anymore to get free pages.

If you look at the MEMORYSTATUS output, it does fall into this with about 1.59% of pages available, at least on NUMA Node 0.

I'd apply, at a minimum, SP2 CU5 and run the load again.

  • when I run select * from sys.dm_os_sys_info on both PROD instances the final column shows 2 Numa Nodes for each (I'll admit, I am still not too familiar with Numa Nodes being just over a year into my DBA career). That being said, I will bring it up to CU5 and let it run overnight and will check in tomorrow morning with the results! Thanks for the response! – TattooedDBA Jun 25 at 23:41
  • 1
    I actually updated to CU6 and let the maintenance plan run it's nightly schedule and the job finished without any issues! I am going to let it run its course over the next few days and see if we can get some consistent successes. So far so good! Thanks for the help! – TattooedDBA Jun 26 at 16:41
  • Unfortunately the problem still persists... I was able to get a couple of successful runs but now we are back to the errors. Any other ideas anyone? – TattooedDBA Jul 1 at 15:10
1

Well it turns out after a couple of months of working with Microsoft on this issue they have determined that it is now a "known issue" with DBCC CHECKDB and VLDB's and that a fix will be coming in SQL 2016 SP3. When I asked if the issue was present in SQL 2017 they said that it was and that they will most likely release in a service pack there too. That one was interesting because I thought they weren't doing service packs any more but she said that this was a big enough issue that they might have to do it in a service pack... so now I wait...

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.