March 14, 2012 at 2:23 pm
Wondering whether anyone has any ideas on this. Been searching all day:
This is my environment:
Microsoft SQL Server 2005 – 9.00.3080.00 (Intel X86) Sep 6 2009 01:43:32 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I have enable PAE and AWE as well as Lock Pages in Memory privilege for my SQL Server service account. My error log has an entry that says
‘Address Windowing Extensions is enabled. This is an informational message only; no user action is required.’
but the agent log also has
‘[310] 8 processor(s) and 4096 MB RAM detected’
It’s seeing only 4GB of RAM though there is 16 GB in the server.
What could I be doing wrong?
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
March 14, 2012 at 4:18 pm
According to this, http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx#physical_memory_limits_windows_server_2003_r2, Windows Server 2003 R2 on 32 bit servers only supports 4GB ram.
SQL Server 2005 SE X32 supports what the OS supports, http://msdn.microsoft.com/en-us/library/ms143685(v=sql.90).aspx.
March 14, 2012 at 5:55 pm
The OS is enterprise edition sp2 and has 16 GB RAM installed and visible to the OS.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
March 14, 2012 at 8:41 pm
1. What is in your boot.ini? i.e. are you using the /PAE or /3GB switches?
2. Please post the results of this query run on your system:
SELECT CAST(physical_memory_in_bytes / (1024.0 * 1024.0 * 1024.0) AS DECIMAL(20, 2)) AS physical_memory_in_GB,
CAST(virtual_memory_in_bytes / (1024.0 * 1024 * 1024) AS DECIMAL(20, 2)) AS VAS_GB,
CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_committed_memory_in_GB,
CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_target_memory_in_GB,
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'min server memory (MB)'
) AS [min server memory (GB)],
(
SELECT CAST(CAST(value_in_use AS INT) / 1024.0 AS DECIMAL(20, 2))
FROM sys.configurations
WHERE name = 'max server memory (MB)'
) AS [max server memory (GB)]
FROM sys.dm_os_sys_info ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 15, 2012 at 2:37 am
Hi,
I am using the /PAE switch NOT the /3GB.
The results of the query are below:
physical_memory_in_GB-----------------------------16
VAS_GB------------------------------------------------2
buffer_pool_committed_memory_in_GB-----------1.62
buffer_pool_target_memory_in_GB----------------9.77
Min Server Memory (GB)---------------------------- 0
Max Server Memory (GB)----------------------------9.77
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
March 15, 2012 at 9:06 am
I think you're are OK in terms of the DB engine. SQL Server is reporting the memory as I would expect, noted by the 9.77GB figure for buffer_pool_target_memory_in_GB.
It's possible that SQL Agent is not equipped to report the news differently when AWE is enabled. By default 32-bit processes can only map 4GB of RAM, which is what it is showing, and that may be all it is capable of utilizing, i.e. SQL Agent may not be coded to use AWE to map memory.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 15, 2012 at 10:06 am
opc.three (3/15/2012)
I think you're are OK in terms of the DB engine. SQL Server is reporting the memory as I would expect, noted by the 9.77GB figure for buffer_pool_target_memory_in_GB.It's possible that SQL Agent is not equipped to report the news differently when AWE is enabled. By default 32-bit processes can only map 4GB of RAM, which is what it is showing, and that may be all it is capable of utilizing, i.e. SQL Agent may not be coded to use AWE to map memory.
Exactly, added RAM will be used to increase the buffer pool size only. It is not available for all the processes.
March 15, 2012 at 10:43 am
Thanks very much people. Just that from some people's posts on the Internet I was beginning to get the impression that I should see this value reflected in the startup.
Fine, only the buffer pool. This should reduce trips to disk and I/O bottlenecks significantly in OLTP setups right?
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
March 15, 2012 at 10:54 am
kennethigiri (3/15/2012)
Thanks very much people. Just that from some people's posts on the Internet I was beginning to get the impression that I should see this value reflected in the startup.Fine, only the buffer pool. This should reduce trips to disk and I/O bottlenecks significantly in OLTP setups right?
Absolutely. The more data you can hold in the buffer pool, the better chance the DB engine has of finding it there when it needs it. Interacting with only the buffer pool infinitely better than having to push something out of the buffer pool to make room to load the needed data from disk.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 15, 2012 at 10:57 am
kennethigiri (3/15/2012)
Thanks very much people. Just that from some people's posts on the Internet I was beginning to get the impression that I should see this value reflected in the startup.
One other clarification I realized that may be needed in case you were confusing the two, the SQL Server Agent startup log is the one showing 4GB of memory...this is a different animal than the DB Engine, i.e. the SQL Server Service, which is the one responsible for managing the buffer pool. In other words, the SQL Server Agent service will only map 4GB, while the SQL Server Service can map much more.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 20, 2012 at 9:08 am
Hi Again. I just test AWE extensively and I notice also a lot of page file usage as my buffer_pool_commited_memory_in_GB grew.
My buffer_pool_commited_memory_in_GB grew from about 0.55GB to 7.97GB and my page file usage grew from 2.76Gb to 11GB.
ANy explanation why the pagin fie is growing this uch. I wouldhave expected that having enough memory means using minimal paging.
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
March 20, 2012 at 10:32 pm
Since you have LPIM and AWE enabled it is most likely not SQL Server buffer pool pages being paged to disk, which is what we want on a server only hosting SQL Server. More likely the paging is for memory from other OS processes. Is there anything else running on the server besides SQL Server?
Since you said the page file grew significantly is it safe to assume you allow the OS to size the page file dynamically? Also important is the percent used. You can see this with the PerfMon counters Paging File/% Usage and % Usage Peak. What do your counters say now that the file is at 11GB?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2012 at 2:41 am
When you mention page file usage, are you checking this out in Task Manager? Because the "PF Usage" field on the Performance tab essentially tells you how much physical memory *plus* page file is in use--it's badly named. (They changed it to be more logical in Windows 2008 and later). So you would expect to see that value increasing as you use more physical RAM. If you want to see how much pagefile is *actually* in use, you need to use Performance Monitor.
March 22, 2012 at 5:18 pm
Thanks all. The Paging File Usage on Task Manager is indeed badly named. Its the sum of the page file and memory in use. The Counter Page File Usage shows a much smaller value.
I have another issue. I have able to prove that SQL Server can exceed memory usage with AWE but I have not been able to prove scientifically that this will make queries run faster. I am using a tool called SQLQueryStress and throwing a simply select * query a hundred times at the server. When I run the the query the second time, I expect it to be much faster since it should now be running from cache. Am I wrong?
If so, how else can I prove with figures that this increased buffer size will actually improve perfromance in prduction?
Br. Kenneth Igiri
https://kennethigiri.com
All nations come to my light, all kings to the brightness of my rising
March 22, 2012 at 6:01 pm
It's an interesting comment...scientifically prove. I take it as a given that if all data accessed does not fit in RAM allocated to uour buffer pool then expanding the size of the buffer pool will improve performance. I suspect there are some factors that may lead to no gains, or even performance losses. As with most things discussed on this site, it will depend, but generally speaking you should expect an improvement.
Some counters you could watch to prove more data is being served from cache are Cache Hit Ratio and Page Life Expectancy. Take a baseline while running a typical workload before increasing the size of the buffer pool, and then clear the buffer pool and take another baseline after increasing the size of the buffer pool. Both measurements should increase with a larger buffer pool and the same workload.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply