0

We are currently running SQL servers 2012 on machine having 64 GB memory. We have a SQL server that is currently using up all the memory allocated (45GB)to it, after consuming all the allocated memory We have to restart the SQL server services because accessing the database (SQL server management Studio) and application is not possible or very slow.

2
  • 2
    Can you edit your question to clarify if you have multiple instances of SQL Server installed and running on the same box? – Erik Darling Jun 4 at 11:15
  • Does your tsql code use a lot of OLE automation? Does SSMS and "application" run acceptably from a different machine? – SMor Jun 4 at 13:42
1

It is normal for a SQL Server instance to consume all of the memory you allocate to it, this is by design from Microsoft, and is stated in their documentation on Monitor memory usage - Configuring SQL Server max memory:

By default, a SQL Server instance may over time consume most of the available Windows operating system memory in the server. Once the memory is acquired, it will not be released unless memory pressure is detected. This is by design and does not indicate a memory leak in the SQL Server process.

Brent Ozar discusses this extensively in A Sysadmin’s Guide to Microsoft SQL Server Memory as well. Best practice recommendation is to not run other applications on the same server as your SQL Server instance, as SQL Server's can be resource intensive, by design, such that the instance can run with utmost performance.

If you do need to run other applications on the server, then you'll need to provision your server accordingly. While ~19 GB of memory leftover isn't too bad, you do need to account for about 4 GB being consumed by the OS itself, leaving you with about 15 GB for your applications. If your other applications are a bit memory demanding, then you'll need to increase the amount of memory provisioned on your server. But keep in mind you'll still be sharing CPU and possibly I/O resources with your SQL Server instance, and should ensure those are provisioned appropriately as well.


Side note: You shouldn't restart the SQL Server Services to clear the memory usage of your instance, and in general you shouldn't clear its memory any other way. Again it's normal and necessary for it to consume memory to perform most optimally, because it caches a multitude of things in memory (such as data pages, execution plans, etc). When you do clear the memory, your SQL Server instance will likely be slower for a short term until it rebuilds all the caches by re-consuming all the memory you just forced it to release.

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.