0

Microsoft SQL Server 2016 (SP1) (KB3182545)

Server Memory : 61GB SQL

Server Max Memory : 56320 MB

enter image description here

I have three database (OneDB,TwoDB,ThreeDB) in the my production environment.

I running sp_BlitzCache on OneDB

Available memory %7-8

I create 'Blitz' db for FirstResponderKit, sp_WhoIsActive and so on dba tools.

I running sp_BlitzCache on Blitz database

Available memory %0

Differences between two databases:

enter image description here

SQL Server why exceeds the maximum memory limit? How can I resolve it?

4

Are your links supposed to show anything? Currently none works.

TT is right, that setting is just for the Buffer Pool and some memory clerks. See docs. There are other areas and even services part of SQL Server package that use memory.

max server memory controls the SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and clr memory (essentially any memory clerk found in sys.dm_os_memory_clerks). Memory for thread stacks, memory heaps, linked server providers other than SQL Server, and any memory allocated by a non SQL Server DLL are not controlled by max server memory.

Suggestion: You've most likely allocated too much memory to SQL. Dial it down - suggest going down to 40 GB to let also the OS breathe. If you have any signs of memory pressure then you might need to turn the knobs for more memory.

If you don't have any problems or memory pressure signs, then SQL Server it's doing its job.

Articles on the subject:

Later edit, after your reply:

You need first to establish what's the problem. Is it the report itself that there's no available memory? Do your applications/queries really suffer due to low memory? Is it just a training exercise?

You playing with database settings might just have caused the buffer pool to be flushed and now the reports look cool, but apps may suffer.

See what's in that server's memory by running:

And after you find out how your server's memory is populated, only then you'll find out where's it spent and if you have a problem.

  • I will try the suggestion but at 50 GB, If I set it to 40 gb, it could be memory pressure. – Yunus UYANIK Oct 11 '17 at 11:11
  • The problem is not resolved, the available memory is reduced when I run sp. – Yunus UYANIK Oct 12 '17 at 6:54
2

Because the maximum memory limit does not mean this is the maximum memory used by SQL Server.

It limits the use of CERTAIN memory - there are certain other buffers etc. that are not counted against this limit.

  • Do you have an article on the subject? And why is there no problem when I run stored procedure on OneDB? – Yunus UYANIK Oct 11 '17 at 8:23

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.