February 19, 2013 at 7:14 pm
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
February 19, 2013 at 7:33 pm
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.
February 20, 2013 at 3:02 am
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:
February 20, 2013 at 7:08 am
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
February 20, 2013 at 1:16 pm
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
February 20, 2013 at 1:25 pm
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
February 20, 2013 at 2:21 pm
Here are the results.
Thank you for the guidance.
February 20, 2013 at 2:45 pm
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
February 20, 2013 at 7:00 pm
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.
February 20, 2013 at 9:16 pm
My pleasure. Thanks for the feedback.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply