0

It seems that even in SQL Server 2012, the maximum memory usage settings is not a hard limit. I've read some SQLOS Team blogs about this, and those as well as several MSDN articles seem to state that the maximum memory usage is just that - a maximum. But that doesn't appear to be accurate. I have a QA environment and the app and SQL are on the same server (this is typical for our application). I've tried setting maximum memory usage to both 8GB and 4GB, but eventually, usually within 24 hours, SQL is utilizing about 13GB (all available) of memory.

Is there an actual way to hard limit SQL Server to not use more than x GB of memory?

Edit: is it possible that the databases themselves being in SQL Server 2008 compatibility mode is causing this?

Target Memory (Performance Counter) is 100% of the 4GB maximum memory value I gave it. Now, of course, it's currently lower because I had to restart SQL Server services to allow anything else to work properly.

select * from sys.dm_os_process_memory:

enter image description here

enter image description here

  • 1
    That is correct, even in SQL Server 2012 this is not covering all of the memory SQL Server can potentially use. The prevailing wisdom is to leave some room for memory above and beyond the buffer pool, so if you want SQL Server to use 8 GB total, set max sever memory to something lower (and to measure accurately - where exactly are you seeing 13GB?). How SQL Server could use 13 GB when you've set 4 GB max (you're restarted the service, right?) could only be explained by understanding how you're using SQL Server. And I seriously doubt this has anything to do with compatibility mode. – Aaron Bertrand Aug 14 '15 at 14:52
  • 1
    (Though, are you keeping your databases in 2008 compatibility mode on purpose? Why?) – Aaron Bertrand Aug 14 '15 at 14:53
  • I'm seeing the 13 in task manager, and while I know it's not the most accurate portrayal, SQL is definitely using the memory. This came up due to a change in an IIS Host Service in our application. A new change to it causes the host services to be unavailable if there's less than 5% available memory in the system as a protection. Nothing besides SQL is using even 1GB of memory, and there's only Windows, SQL, and our app. – Jesse Williams Aug 14 '15 at 14:54
  • The databases are generated via DacPac and we still support SQL 2008. It appears someone has set it to generate with 2008 compatibility mode. – Jesse Williams Aug 14 '15 at 14:56
  • Stop even opening Task Manager. Seriously. – Aaron Bertrand Aug 14 '15 at 15:01