February 22, 2015 at 11:15 pm
Hi all,
I have an issue with my production server regarding memory usage (Memory Utilization is above 95%.). Memory is : 12 GB
and the service that is consuming the majority of memory 88%/10.5GB is sqlserver.exe. So it would appear that MSSQL is not set to restrict the amount of memory it uses ?
How much should I set for min and max memory ? the defauld is min memory : 0 and max : 2 TB
The server is principal and has 1 mirror server (active passive)
Thanks ... Much appreciate for any comments
February 23, 2015 at 1:36 am
Have a read through Chapter 3 of http://www.red-gate.com/community/books/accidental-dba
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2015 at 4:25 am
It's generally a good idea to have SQL Server run on a machine by itself and not share the server with other processes. SQL Server only consuming 85% sounds like there are processes sharing the resources with your SQL Server instance. You might want to look into isolating it if you can.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2015 at 4:10 pm
Hi,
Thanks for the reply. Much appreciate it !
I found something interesting from that book as below
"One of the popular questions on online SQL Server forums is: "What is the proper max
server memory setting for a SQL Server with N gigabytes of installed RAM?" Unfortunately
there is no single setting that applies to every environment for this option; different
servers have different processes running and what is best for one server will not be the
same for a different server. The best answer is to set the initial value low enough to ensure
that the operating system doesn't have memory pressure issues and then monitor the
Memory\Available Mbytes performance counter on the server (along, possibly, with
counters like Total Server Memory and Target Server Memory, covered later) to
determine the value for max server memory that leaves at least 150–300 MB of memory
available at all times for the memory requirements of non-buffer pool and non-SQL
memory allocations, for Windows and other applications.
As a general base configuration, for a dedicated SQL Server machine, reserve 1 GB of RAM
for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8
GB RAM installed above 16 GB RAM. This means that, for a server with 64 GB RAM, the
starting point for max server memory should be in the 54 GB range, and then tuned
higher based on monitoring the Memory\Available Mbytes performance counter. "
I am still confused with the using of performance counters and dont understand about how to set the proper max memory setting based on those values .
Moreover my server is principal and has 1 mirror server ..( not a dedicated server )
Would you please guide me regarding this ?
thanks so much!
February 23, 2015 at 4:45 pm
Here's one of the simplest[/url] most straight forward formula's for setting your memory. Follow the recommendations here and you'll be fine in almost all situations.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2015 at 9:47 pm
Thank you! Appreciate it !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply