![]() 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. 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). What does Max Server memory control, though? According to the documentation, it’s the buffer pool, compilation memory, all the caches including the plan cache, and a bunch of other stuff. This value should not be left at the default 2.1 petabytes, because at that value SQL Server will use all the memory you allow it (plus a little bit more if it needs to), and once it has it, it doesn’t release it back to the operating system without a fight. On all editions of SQL Server (Enterprise, Standard, Web, Express), there is a configuration setting called Max Server Memory. For the sake of this post, I am talking specifically about SQL Server 2012 and higher. ![]() Joey D’Antoni was quick to remind me that this only applies to SQL Server 2012 and higher, which I gratefully acknowledge. The RAM limit Standard Edition can use *is* just for the buffer pool. Reminder: Max Server Memory is *not* just for the buffer pool. Last week, I posted this statement on Twitter, along with a screen capture of the official Microsoft documentation:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |