Link to home
Start Free TrialLog in
Avatar of Scott Milner
Scott MilnerFlag for United States of America

asked on

I'm looking for assistance with SQL server 2012 memory settings.

Good morning,

I have a SQL server in our environment that might possibly be contributing to poor performance from our ERP system (we're troubleshooting the performance issues from multiple directions).  The server is a Dell PE 720, running Server 2008 R2 and SQL 2012, with a single Xeon E5-2609 @ 2.4 GHz and 16 GB of RAM.

I was seeing consistent high cpu and memory utilization on the server.  I understand that this can be caused by poorly written queries, but that will require a significant consultation expense (we don't have the requisite SQL expertise in house), so we elected to upgrade hardware first to see if we can alleviate the issue.  Over the weekend, I added a second E5-2609 processor and an additional 32 GB of RAM.

I understand that there are memory configurations within SQL that will need to be tweaked to take advantage of the additional resources; I'm just not familiar enough with SQL to be confident making changes to them.  Can anyone offer assistance with what the best practices settings would be?

As I look at the SQL server properties, I see the following settings (still set as they were with the single proc and 16 GB of RAM):

SQL Version:  11.0.2218.0

MEMORY page

Server Memory Options
Minimum Server Memory (MB)    0
Maximum Server Memory  (MB)   6000

Other Memory Options
Index creation memory (in KB, 0 = dynamic)     0
Minimum memory per query (in KB)     1024

Configured values checked, not running values


Processors Page

Automatically set processor affinity mask for all processors     CHECKED
Automatically set I/O affinity mask for all processors     CHECKED

Maximum worker threads:     0

Boost SQL Server priority     UNCHECKED
Use Windows fibers (lightweight pooling)     UNCHECKED

Configured values checked, not running values


Any assistance you might be able to offer would be appreciated!

Scott
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Your SQL Server has only 6GB set for MAX memory.: "Maximum Server Memory  (MB)   6000"
This means that whatever RAM you add it won't use it since is limited to 6GB.

Use the following script to increase the maximum memory to 12GB (you can set any value that you want, just don't forget that the value is set in MB):
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max server memory', 12288;
GO
RECONFIGURE WITH OVERRIDE;
GO

Open in new window

SOLUTION
Avatar of Deepak Chauhan
Deepak Chauhan
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott Milner

ASKER

thank you both for your responses.  I should clarify that there is only SQL 2012 running (the other reference was to the OS, Server 2008 R2), and that I added 32 GB of ram, bringing the total to 48GB.

If I follow your thinking, deepak, that would mean that I leave 12 GB (25%) of the total allocation to the OS, and set my maximum memory setting to 32768?

Do I need to worry about index creation or minimum memory per query settings?

Scott
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
good to know, Vitor!

Does having SS Analysis Services running on the same server change your opinion of how much memory should be set for the SQL max memory?
That depends on the use of the SSAS. It will process the cubes and dimensions at the same time that the databases are heavily being accessed? Or is the opposite and it will only be used when the databases won't be accessed?
Do you also have SSIS and SSRS installed in the same box? SSIS is a memory eater!
ah.. good catch.

As far as I know, the cubes are supposed to be processed during off-hours (I'll need to figure out how to verify this).

SSIS is installed on this box as well.
SSIS is installed on this box as well.
I hope that the packages are also running during off-hours or you can have performance issues in the future since you can't configure memory usage for SSIS (it will use all the RAM from the server if necessary).

That's a reason why SQL Server engine should be in a dedicated box and that's for have dedicated resources. Sharing resources with other applications can kill a database performance.
that makes sense.  Unfortunately, my IT resources are rather limited at this point (I just took this job), but I will dig into it with my long-range plans.  I'll check with our ERP (we use MS Dynamics Axapta) developers to see if they know how much we are utilizing SSIS, and on the timing of it's process runs.

Unless you or Deepak have any other thoughts, I'll start with a 32GB allocation to maximum memory, and monitor the response.

Thank you both for your help!
I'll start with a 32GB allocation to maximum memory, and monitor the response.
Seems pretty good for me. Monitoring is what you really need to get the real memory usage from all processes.
Good luck in your new job. Cheers.
Thanks!  I'm learning, but it never seems to be fast enough!  :)

Have a great day!
One last question, Vitor...

does that script statement that you provided require a reboot?  I ran it, and the values were updated appropriately, but I also received a response that states I need to 'Run the RECONFIGURE statement to install.'

Can you assist me with the next step?
No reboot needed. Not even a SQL Server restart. The RECONFIGURE WITH OVERRIDE command should be enough to set the new configuration immediately.
perfect... thanks again!