December 23, 2008 at 10:55 am
We have windows 2003 EE x64(SP2) and SQL Server 2005 EE x64(SP2). We have sharepoint databases in SQL Server.For this confihuration we have 16GB RAM.
Could you please advice whether this 16GB RAM is enough considering these 64 bit enterprise environment
December 23, 2008 at 11:08 am
Do you have 16GB in the server? Or is that what's allocated to SQL Server.
As for if this is enough for your Sharepoint instance, there's no way to know. 2GB could be enough, or 16GB not enough. It depends on the load being placed on the SQL Server.
December 23, 2008 at 11:10 am
You'll need to provide quite a bit more than what you've done so far for us to even be able to guess...
How big is your database, what type, OLTP or DW or somethign else?
What type of load? How many concurrent users etc...
For one of my db's 16GB is overkill, while for others a box with 16GB of RAM would be nowhere near enough...
As Always, It Depends and YMMV.
-Luke.
December 23, 2008 at 11:27 am
thank you
We 16GB in the server and total 16GB is allocated for SQL Server.Still we got couple of times the below error in last two months.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 38136, committed (KB): 105720, memory utilization: 36%.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 97848, committed (KB): 14700016, memory utilization: 0%.
December 23, 2008 at 11:46 am
Do you have "lock pages in memory" enabled on the SQL server? That's frequently a cause of that problem.
December 23, 2008 at 12:00 pm
No, we did not add the SQL Service account in lock pages in memory.
December 23, 2008 at 12:01 pm
madhu.arda (12/23/2008)
thank youWe 16GB in the server and total 16GB is allocated for SQL Server.Still we got couple of times the below error in last two months.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 38136, committed (KB): 105720, memory utilization: 36%.
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 97848, committed (KB): 14700016, memory utilization: 0%.
If you only have 16GB of memory available - you cannot allocate all of the system memory to SQL Server. The OS and other processes will need memory - and SQL Server may not give it back.
I would recommend setting the maximum to no more than 14GB (possibly even lower) and the minimum no higher than 12GB. DO NOT SET THE MIN/MAX to the same value - as that will cause other memory related problems.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 23, 2008 at 12:15 pm
madhu.arda (12/23/2008)
No, we did not add the SQL Service account in lock pages in memory.
Add the service account as an option, that will help out with the memory paging that you were seeing.
Also Jeffrey's recommendations as regards the memory usage are right on the money.
December 23, 2008 at 1:05 pm
Second for Nicholas recommendations.
December 23, 2008 at 2:47 pm
Hi,
Could you please advice me whether the following analysis would be fine or not To set Min and Max memeory values.please correct me if I miss anything.
Analysis:
In order to determine proper settings for Min Server Memory, Max Server Memory the following needs to occur:
System Monitor (perfmon) will record data no less than 48 hours to retrieve the following statistics:
SQLInstance:Memory Manager: Total Server Memory (KB)
To retrieve the amount of memory used by the SQL instance
A counter log will be recording this statistic between the peak hours defined
Memory: Available Mbytes
To retrieve the total available memory
Paging File: % Usage: _Total
To retrieve the percent of page file used
Paging File: % Peak: _Total
SQL configuration
SQL configuration is the implementation of memory restrictions based on server configuration.
MinServer Memory
Will be calculated by subtracting 10% from Average Server Memory
-Average sever memory was a statistic derived from the average memory usage of the SQL instance during its peak time. By removing 10% from that average, it gives us a ‘best guess’ to configure the lowest amount of memory we would want running for that particular instance.
Max Server Memory
Will be calculated by adding 10% on to Average Server Memory
-Average sever memory was a statistic derived from the average memory usage of the SQL instance during its peak time. By adding 10% from that average, it gives us a ‘best guess’ to configure the most amount of memory we would want running for that particular instance.
thanks
December 23, 2008 at 4:06 pm
I don't think you will get the kind of numbers you are looking for with this. SQL Server will take as much memory as it needs - in fact, it will use all of the memory on a system if allowed.
There are plenty of guidelines available (on this site, and others) that will give you an outline of what to set. However, what you end up setting the min/max values to will depend fully on your system, application and environment.
A general guideline for a system with 16GB of memory is to set the max memory at no more than 14GB and min memory no more than (MAX - 1GB). In other words, you should leave at least 1GB of memory available for SQL Server to manipulate.
This could change quite drastically if there are other processes running on the server. For example, if the server processes SSIS packages (e.g. ETL process) - it may be required that you set the min memory at 8GB and max no more than 10GB to allow the SSIS packages to process without hanging the system.
I have seen some systems/applications with 16GB of memory on the server set the max to 12GB and leave the minimum at the default. For those applications, they found that setting anything more than that would cause problems.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 24, 2008 at 11:21 am
Thanks,
I attached the Memory ananlysis excel sheet(taken at peak hours). based on these values could you please suggest me a guess for Min and Max server memory values and Page file size.
As I mentioned before:
Max memory:Average sever memory was a statistic derived from the average memory usage of the SQL instance during its peak time. By adding 10% from that average, it gives us a ‘best guess’ to configure the most amount of memory we would want running for that particular instance.
Min:calculated by subtracting 10% from Average Server Memory
The above method to calculate min and max is acceptable or do I need to do further analysis?
Thanks for your suggestions and help
December 24, 2008 at 12:15 pm
the following post
http://www.sqlservercentral.com/Forums/Topic529146-146-1.aspx
has a detailed answer from Gail for a similar scenario
and this post
http://www.sqlservercentral.com/Forums/Topic622415-146-1.aspx
has my previous answer
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply