Control memory usage on Microsoft SQL

article #1276, updated 1792 days ago

By default Microsoft SQL instances are all over the place, sometimes you’ll see them take 1.5G on a 32G server, sometimes they’re trying to eat everything alive and the servers slow to a crawl. To control them, try the following. Performance gains are likely.

  1. Make sure SQL Management Studio is installed of the correct version.
  2. Right-click the SQL Management Studio icon, and Run as Administrator. This is a great workaround for many SQL authentication issues. If you still can’t move forward after this, you’ll have to create a new SQL sysadmin user in SQL single user mode, which is a different set of steps!
  3. Connect to the database.
  4. Right-click on the server name. Choose Properties.
  5. Look at Memory, and start your tweaking. Also go to Processors if you like, and choose “Boost SQL Server priority”.
  6. You can also do this by SQL. Create a new query, containing the below if you want 10G max and 2G min, and execute the query. See results in Task Manager. Amazing!
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 10240;
GO
RECONFIGURE;
GO
sp_configure 'min server memory', 2048;
GO
RECONFIGURE;
GO

At least one reason this can do a lot of good, is the default is 2147483647 megabytes (2,147 terabytes) for “Maximum server memory”. Which means (a) SQL is going to try to take all of the RAM that there is if it imagines it might need it, and (b) even if it doesn’t, it’s going to calculate memory sometimes in terms far larger than necessary. Thus far, leaving 4 or 8 gigabytes for the OS and giving SQL the rest for a maximum has worked very well in many cases, though if you have other applications running on the server you’ll want to divvy up carefully.

Categories: