SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Max Server Memory


Max Server Memory

Author
Message
kim.sims
kim.sims
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
We just took over a small network with 2k3 running Sql 2005. We virtualized it, per customer requirement, and seeing somewhat of a lag in performance, that I believe a memory configuration change will solve. After reading different articles and trying to follow, I find that there is NO memory node in SSMS, where the change in memory allocation would occur, only general, language and security....
What am I missing???
Thanks
matak
matak
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 3948
I think this is what you are after.
In SSMS right click on the database server -> properties
There is a memory tab which allows you to modify min/max memory for the server.
kim.sims
kim.sims
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
Matak,
You were correct, that was it.
My issue was in the fact that I wasn't connecting to the database engine, I was connecting to the analysis engine, so there wasn't the option for memory configuration.
I read that SQL also likes lots of paging file space, so I increased it 3 fold, as well as setting the minimum memory in SQL to 2GB and max to 3GB.
Since SQL is all this box houses, I don't think I'll have an issue with SQL consuming such a large portion of the total 4GB. Hopefully!!!
Thank you for your assistance.
Whistling
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15097 Visits: 14396
SQL Server actually will not use the page file unless the server is under duress. Page file access is very slow compared to RAM access, so SQL Server aims to do everything in RAM.

Is it a 64-bit instance of SQL Server? If so, then what you did with the min and max memory is fine as a starting point. Just monitor the memory usage of the server to ensure there is always a few hundred megabytes free for the OS to use at its discretion.

If the instance is 32-bit then setting max memory to 3GB is not going to have much effect at all whereas setting a 2GB minimum could impact some things. If you could check the boot.ini file to see if the /3GB switch is being provided on the startup command as well as post the results of this select-query when run against your instance we can get to the bottom of it and get you a solid configuration:

-- memory overview
SELECT CAST(physical_memory_in_bytes / (1024.0 * 1024.0 * 1024.0) AS DECIMAL(20, 2)) AS PhysicalMemoryGB,
CAST(virtual_memory_in_bytes / (1024.0 * 1024 * 1024) AS DECIMAL(20, 2)) AS VasGB,
CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS BufferPoolCommittedMemoryGB,
CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS BufferPoolTargetMemoryGB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'min server memory (MB)'
) AS MinServerMemoryGB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
) AS MaxServerMemoryGB,
(
SELECT value_in_use
FROM sys.configurations
WHERE name = 'awe enabled'
) AS IsAweEnabled
FROM sys.dm_os_sys_info;



Please check out this book when you have some time: Troubleshooting SQL Server: A Guide for the Accidental DBA By Jonathan Kehayias and Ted Krueger. The whole book is fantastic but Chapter 4 is immediately relevant for you.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kim.sims
kim.sims
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
Opc.three,
Thank you for your assistance with this, as I'm pretty green with SQL.
Do I need to back up the databases prior to running the query? Which database do I perform the query against?
Can it be performed as a global query or does it need to be run against each?
Also, this is a 32 bit installation without the boot.ini 3GB switch.
Thank you
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15097 Visits: 14396
You can run the query against any database as it refers to System Views that are available in any database and they report instance-level data, not data for a specific database.

I like your sense of caution. That's a good disposition to carry around as a DBA, especially as a new DBA, but in thus case you do not have cause to take a backup before running the query as it only selects data.

Before we talk about the ideal memory settings I need to see the results of the query to know if you have AWE enabled.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kim.sims
kim.sims
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
Here are the results.
Thank you for the guidance.
Attachments
MemoryQueryResults.PNG (17 views, 6.00 KB)
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15097 Visits: 14396
OK, AWE is disabled (the default) so the largest your SQL Server buffer pool will ever see is somewhere between 1.5GB and 1.6GB. This is due to the 2GB limit on the user-mode VAS imposed by 32-bit versions of Windows minus some process and environment overhead that prevents SQL Server from being able to use all 2GB. Your screenshot shows that your buffer pool target is 1.55GB which is consistent with what I would expect.

Essentially what this means is that setting your max memory to 3GB is not going to help much since it can never rise above ~1.6GB due to those external limitations. Having a min server memory setting of 2GB could actually be detrimental to the system because it guarantees that SQL Server will never release memory it has claimed even if the system is under duress and that can cause some bad side-effects. You have a few long-term options here which I'll go into later, but for the near term I would recommend setting min server memory back to its default of 0. Like I said, the max server memory setting is not as important in your current scenario so you could safely leave it 3GB or possibly lower it as low as 1.6GB.

If you are having memory pressure issues you could look into adding the /3GB switch to your boot.ini, or you could look at enabling AWE. The former option will allow SQL Server to access up to 3GB of user-mode VAS, an increase of 1GB from what you have today, but that takes 1GB away from the Windows kernel so its a tradeoff. With a box dedicated to running SQL Server adding the 3GB switch is usually a pretty safe option and I have used it many times in the past. The other option is to skip adding the /3GB switch and enable AWE. Enabling AWE would allow SQL Server to access up to all 4GB of physical memory so in this scenario it is hyper-critical that you set max server memory conservatively, e.g. at 2.5GB to begin with) and then watch the available RAM on the server to make sure SQL Server is not starving Windows. Then you could slowly increase max server memory a bit at a time and continue monitoring.

Please read Chapter 4 of that book and post back if you have more questions.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
kim.sims
kim.sims
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
I took your advise and enabled AWE with a 2.5GB max memory limit.
I enabled AWE but, noticed that it didn't take effect until I restarted the SQL services.
The screenshot shows the new configuration.
I appreciate all the help.
Attachments
SQL.PNG (11 views, 6.00 KB)
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15097 Visits: 14396
My pleasure. Thanks for the feedback.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search