December 7, 2006 at 2:48 pm
Hello!
I am running tests on 64 bit server, sql server 2003 standard, windows 2003 enterprise, 8GB of memory in total. Its a dedicated sql server machine. I set the min memory for sql server to be 5GB and 6GB and rebooted the machine. Now I am seeing thru task manager, sqlservr.ext is using 6.6GB memory and its growing, though slowly, available memory 972MB. Using perf mon, total server memory for sql server shows 6.29GB. Could somebody please explain why SQL server is using more than the max allocated memory? And what accounts for the difference between the perf mon counter and task manager counter?
Thanks in advance for any insights.
Kathleen
December 7, 2006 at 2:59 pm
You mean to say sql 2005 not 2003?
Check the max server memory configuration once again by running sp_configure proc..
I don't see any reason to use more memory than it is configured.
MohammedU
Microsoft SQL Server MVP
December 7, 2006 at 3:07 pm
Thanks for the response.
Sorry, its SQL SERVER 2005.
When I run sp_config, in sql server 2005, it does not show min/max memory options, as you see in sql server 2000. But I have double checked thru management studio, under memory table, it does show min 5gb and max 6gb.
December 7, 2006 at 3:16 pm
Actually after I enabled the 'show advanced options', I do see those configuration values:
max server memory (MB): 16(min) 2147483647(max) 6144(config_value) 6144(run_value)
min server memory (MB): 0(min) 2147483647(max) 5120(config_value) 5120(run_value)
Does it look like the configured values will be used?
December 7, 2006 at 8:55 pm
When you start SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is
between 4 to 10 MB. This behavior is normal and is an intended behavior of the SQL Server buffer pool. By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 - 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
See the SQL Server Books Online topic "Optimizing Server Performance Using Memory Configuration Options" for information about configuring memory.
December 8, 2006 at 7:10 am
Then what does setting max memory for sql server do?
December 8, 2006 at 7:18 am
i had a similar post a few months ago.
are you sure you have the latest service pack installed? i remember I had update sql server upto build 8.0.2187. previous builds have a known issue with managing memory when you customize it.
December 8, 2006 at 7:42 am
Its actually sql 2005 standard edition, sp1.
After having the server run throughout the night, looking from task manager, sqlserv.exe is now settled with 6.5GB memory (even though the max server memory is set to 6GB). So the question is what could account for the 0.5GB difference?
December 8, 2006 at 7:44 am
interesting.. did you install sp1?
December 8, 2006 at 7:46 am
I did not personally intalled it, but @@version says sp1.
December 8, 2006 at 7:53 am
Out of curiosity, why would you want to limit SQL Server to 6GB out of 8GB RAM on a "dedicated sql server machine"? SQL 2005 memory management is designed to grab all the free memory it can, but release it quickly when needed by the os or other applications. It is possible that something else on the system may suddenly allocate huge chunks of RAM faster than SQL can react, and cause a temporary increase in paging activity until SQL can checkpoint and release some buffer pages, but I would want to monitor the system and see a regularly occurring memory issue before I started lowering the max memory that much.
I was at the Seattle PASS conference last month and talked to some of the Microsoft people that worked on SQL 2005 memory management, and they went into great detail explaining how it works and why you shouldn't play with the max server memory setting. Of course in the "64-bit best practices" session other Microsoft people said you should always limit max server memory. So much for expert advice. But I don't think either of them would agree you should take 25% of you available RAM away from SQL Server, maybe 7GB would be a better compromise. (Unless this server isn't really dedicated to SQL, and you know something else that can't get along with SQL will need the memory.)
Whether or not SQL Server observes the max server memory setting is another issue, I don't know what might be causing that. I was just reacting to the idea of limiting it to 6GB.
December 8, 2006 at 9:58 am
Running SQL Server 2005 Standard Edition w/ SP1, I've encountered an issue that appears to have been resolved by limiting the amount of memory SQL Server can use. The issue was that SQL Server requested more memory than was available and got stuck in a thrashing cycle with the swapfile. There are 3 recommendations in the link below to work around this problem: 1) add more memory, 2) limit the amount of memory available to SQL Server, and 3) upgrade to Enterprise Edition.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=440746&SiteID=1
November 23, 2007 at 12:45 pm
I am experiencing a similar issue. I have an instance of SQL server that is configured for 5120MB Max Memory. When I look at Server Dashboard in SSMS, I see 5,227,072 KB Total Server Memory (KB), which when divided out, is 5104.5MB (less than the 5120MB setting). BUT! When I log into the computer and look in Task Manager, it reads currently with 5,437,496. This would divide out to 5310MB. That is nearly 200MB more than what it "thinks" it is using. Is there a good reason for this discrepency?
November 23, 2007 at 12:52 pm
Oh, I forgot to give system information:
Windows Server 2003 x64 with SP1
SQL Server 2005 Enterprise Edition x64 with SP2 (3 instances)
8GB System Ram (2 GB max on default instance, 5 GB max on second, and 1 GB max on third)
There are no other programs running, this is a dedicated SQL Server, but the SQL Components are:
SQL Database Engine
SQL Reporting Services
SQL Analysis Services
SQL Integrated Services
So, everything is installed on the machine. SSIS and RS are heavily used, where AS is not just yet...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply