Share via

Can insufficent memory cause allocation & extent errors?

Frank Garcia 41 Reputation points
2022-01-24T17:12:51.52+00:00

SQL Server 2014 SP3 GDR

Can insufficient memory cause allocation & extent errors?

Let's say you have 168 databases on this instance. One of them is 2.4TB in size and four others 1TB +

Just barely 1TB of memory.

Hybrid environment, lots of loading, bulk inserts while many reads are going on. Searches come from the application which are most parallel and bad in general. Developers throwing their own monsters into the mix.

The disk array is about 5 to 10 years old. DBA doesn't get to look and evaluate the VMS or host.

Outside consulting firm came in for an audit and deemed the servers configured correctly except under resourced. Recommended splitting out some of those VLDB's and adding 1TB of memory. They also recommended changing maxdop from 8 to 4. However since then we have had memory dumps. I agree that we should split out these VLDBS, increase memory and update firmware especially on disk.

A dbcc checkdb cleared the allocation and extent errors.

I am telling my manager that we need to increase memory so we can handle the size of these databases until we get resources to split them off. However he does not believe me that memory is the issue here.

Can a guru here help me make my case? Thank you for helping your fellow DBA's.

SQL Server | Other
0 comments
Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-01-24T22:59:49.467+00:00

    You don't get allocation errors and extent errors just because the system is overloaded. You get it because some piece of your hardware is faulty. Hardware here includes the I/O subsystem, and there can be issues with drivers etc.

    It could however be that the load takes more strain on the machinery, that you are more likely to provoke these errors to happen.

    Getting inconsistency warnings is a big warning sign. This time DBCC might have been able to fix them. Next time, if may not be fixable without data loss - or restoring a backup.

    As for the patch level, it certainly does not hurt to be on CU4. Note that to get there, you will first need to uninstall the current GDR and then apply CU4. And the apply the corresponding GDR for CU4.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,771 Reputation points
    2022-01-24T19:21:50.28+00:00

    The first thing you should do is install the current patch level and see if the problem goes away.
    https://learn.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level#sql-server-2014

    If you are getting memory dumps from SQL Server, that is not normal and you would have to contact MS Support for resolution. These are almost always issues in the SQL Server code. But since you are on an unsupported version, you may not get a fix. They will likely tell you to upgrade to 2019.

    RAM size is not in any way related to database size. It is mostly used for cache for performance.

    0 comments
  2. Frank Garcia 41 Reputation points
    2022-01-24T19:33:19.91+00:00

    Thank you, Tom.

    We are at the highest level of patching for SQL Server 2014.

    I agree that we should upgrade to 2019 to see if this resolves the issue.

    I was under the impression that the largest table should fit in RAM on a SQL Server. If we have 985GB allocated and that table is 1.8TB then it is going to spill to disk. A slow disk with outdated firmware etc.

    Perhaps not the cause of the memory dumps as you say. Thanks for replying - kindly appreciate your support.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.