August 7, 2008 at 9:01 am
I have 8 gigs of ram on the server, 2005 64 bit standard edition.
5 gigs are set on max memory
task manager-
total is 8,215,840 --- this represent the 8 gigs of ram?
Available 5,240,800 -- this represents the max memory?
system cache 1,778,200 -- this is the amount the OS is using?
Perfmon -
Available bits 5,221,234 -- this is the max memory setting?
Target server memory -- 5,120,000 -- this the max sql server setting?
Total server memory -- 1,900,568 -- this is telling me that roughly 3 gigs is being used or this telling me the 1.9 gig is being used?
THANKS
August 7, 2008 at 9:22 am
Target Server memory is the max SQL Server will consume.
Total Server Memory is the amount that SQL Server IS using.
Are you running 32-bit or 64-bit? If you are running 32-bit you need to make changes the servers boot.ini in order for it to take advantage of the 8 gb of memory and setup SQL Server to use AWE as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 9:27 am
yes SQL 64 bit and 64 bit 2003 windows server OS....so I am unable to make any changes as far as awe and page locking...
sounds like I need to add more ram....
August 7, 2008 at 9:36 am
I am still not sure on this ?
Total server memory -- 1,900,568 -- this is telling me that roughly 3 gigs is being used or this telling me the 1.9 gig is being used?
Is sql using 1.9 gigs or is there 1.9 gigs left to be used, therefore the sql used 3 of the 5 gigs available to it?
Thanks
August 7, 2008 at 9:40 am
I'm not sure the issue is the amount of RAM as you are not using all that is available. You have 5gb available to the server and SQL is only grabbing 1.9 GB.
What are you getting for BufferCache Hit Ratio and Cache Hit Ratio from the plan cache. If you have a 98% + consistently then you just don't need to use more RAM and the performance issue is elsewhere.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 9:42 am
1.9 is being used and there is 3 being available.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 10:00 am
my buffer is is in the high 90% but and in the past I always read that 98-99 then there was not a problem. as far as memory.
but after so more reading this is the Average since the server has started....so it sounds like to me I need to reboot the server then track it.
We are running some heavy cursors SP and I trying to find out if its a memory issue or I/O although they go hand and hand..
CPU seems to be ok, avg about 35%
August 7, 2008 at 10:33 am
Your cache ratio is low. So finding out why SQL Server is not grabbing more memory will help as it appears you have is available, but if you are running a lot of cursors you probably are having IO issues with tempdb.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 7, 2008 at 11:17 am
I just went through a similar scenario
I need a little more info:
@@version
how many CPU?
exec sp_configure
go
Are you clustered?
have you ever run BPA for 2005?
what version of Windows 2003 are you on?
August 7, 2008 at 11:23 am
Two things...
1. Make sure the lock pages in memory local group policy is enabled for the SQL Server service account.
2. AWE still has an effect in the 64 bit environment. Turn it on.
There are two behaviors that the AWE option has in SQL Server.
1. In the 32-bit environment, it allows SQL Server to address the additional memory on the server.
2. In BOTHthe 32-bit and 64-bit environment, it will perform a hard-allocation of the memory (non-shared) directly at startup.
Its been my experience that things like virus scanners and other services running on the SQL Server will steal shared pages from the shared memory pool and do a hard page to disk for the SQL Server memory segment. This kills performance for your SQL Server. Sometimes you'll see an error message in your error log that a memory segment has been "paged out".
I know the Microsoft guidance on this says to only enable it when you see the message in the log, but its been my experience that SQL Server 64-bit just generally performs better in all cases with the option enabled.
Of course, test for yourself, blah blah blah.
August 7, 2008 at 11:35 am
We did not enable AWE in any of our 64-bit Enterprise version 9.0.3186 for SQLSvr2005 with 16 CPU but you might need to.
http://msdn.microsoft.com/en-us/library/ms190673.aspx
I hope this article helps
Windows Server 2003, Standard Edition supports physical memory up to 4 GB.
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.
Configuring Memory Options
SQL Server 2005 dynamically allocates AWE-mapped memory when running with any of the Windows Server 2003 operating system editions. In other words, the buffer pool can dynamically manage AWE-mapped memory (within the constraints of the min server memory and max server memory options) to balance SQL Server memory use with the overall system requirements.
When AWE is enabled, SQL Server 2005 always attempts to use AWE-mapped memory. This applies to all memory configurations, including computers configured to provide applications with less than 3 GB of user mode address space.
We recommend setting AWE as the default memory mode for SQL Server 2005 running under Windows Server 2003. The Hot-Add Memory feature requires AWE to be enabled during SQL Server startup. For information, see Hot Add Memory.
August 7, 2008 at 11:36 am
Windows Server 2003, Standard Edition supports physical memory up to 4 GB.
August 7, 2008 at 11:40 am
We did not enable AWE on our 64-bit 16 CPU Enterprise 9.0.3186 version using Windows 2003 sp2
we changed a few sp_configure parameters.
I am not sure if because you are using STANDARD you may have to go a different route.
See http://msdn.microsoft.com/en-us/library/ms190673.aspx
(extract from URL)
Windows Server 2003, Standard Edition supports physical memory up to 4 GB.
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.
Configuring Memory Options
SQL Server 2005 dynamically allocates AWE-mapped memory when running with any of the Windows Server 2003 operating system editions. In other words, the buffer pool can dynamically manage AWE-mapped memory (within the constraints of the min server memory and max server memory options) to balance SQL Server memory use with the overall system requirements.
When AWE is enabled, SQL Server 2005 always attempts to use AWE-mapped memory. This applies to all memory configurations, including computers configured to provide applications with less than 3 GB of user mode address space.
We recommend setting AWE as the default memory mode for SQL Server 2005 running under Windows Server 2003. The Hot-Add Memory feature requires AWE to be enabled during SQL Server startup. For information, see Hot Add Memory.
August 7, 2008 at 11:45 am
I can't find the link to this info but I pasted the entire document.
Hope this helps with the AWE. We went round and round about it.
Memory Use in SQL Server
SQL Server's caching behaviour is the reason for the substantial memory use. This is by design and is not a bug, memory leak nor incorrect configuration. Every time SQL Server needs to read a page from disk it caches the page in memory so that the slow disk operation will be unnecessary should SQL Server need that page again. As such, the memory allocated to SQL Server fills up, primarily, with recently read data pages. This section of memory is known as the buffer cache (in SQL Server 2000 Books Online) or sometimes the buffer pool (particularly in SQL Server 2005 Books Online).
The other main section of memory that SQL Server uses is the procedure cache. This contains all the query execution plans that SQL Server creates in order to manipulate the data, as requested by the user, in the most efficient way possible. Actually, that is not entire accurate, but it is close. When compiling execution plans, SQL Server usually comes up with several logically equivalent plans. The most efficient plan is added to the procedure cache, paired with that query string, and the remaining plans are discarded. The next time the same query string is submitted to SQL Server, it does not need to waste valuable CPU cycles recompiling execution plans because it already knows the most efficient one (compiling execution plans is computationally expensive).
Of course, there is only a finite amount of memory in a SQL box, both physical and virtual, so SQL Server cannot continue to cache data pages and query plans indefinitely. Therefore, it has a mechanism to age query plans and data pages so that, if memory runs low, the least recently used plans and data pages are removed from memory. This mechanism ensures that there is always enough memory for other applications should they request it. The ratio of memory allocated to the buffer cache versus the procedure cache is managed internally, according to an algorithm known only to Microsoft. However, if very few different queries are executed compared to the number of data pages manipulated then the buffer cache will be considerably larger than the procedure cache (and usually is). Conversely, if a large number of different statements manipulate very little data then the procedure cache will be comparatively larger.
SQL Server uses memory for other purposes too, such as the environment associated with each user connection, locks and index creation; however, the buffer cache and procedure cache comprise the majority of memory used by SQL Server. This is why memory is so important in a high-performance SQL server.
It is time to address the second question of how to configure the SQL Server memory settings given a certain amount of physical RAM in the box. In order to do this, however, we quickly need to cover a few basics of 32-bit addressing. (These 32-bit addressing issues go away with 64-bit architecture.) Every 32-bit application, be it SQL Server or something else, has a 4GB virtual address space (2^32 = 4GB). Windows assigns half of that 4GB to user code and the other half is reserved for kernel code. Note well that this is virtual address space we are talking about, and may be backed by either physical memory or the system paging file. A given page in the buffer cache (or procedure cache for that matter) may not necessarily reside in physical RAM; it may be on disk just like the database file(s). Since Windows 2000, the operating system has provided a feature to reduce the kernel mode memory to just 1GB, thereby allowing the user mode portion 3 of the 4GB in the virtual address space. Microsoft achieves this by the system-wide boot.ini /3GB switch and it applies to all 32-bit applications running on the box (not just SQL Server). Windows 2003 has introduced a new boot.ini switch, /USERVA (see http://support.microsoft.com/?kbid=316739), which essentially does the same thing as /3GB but allows finer tuning of exactly how much memory is allocated to the kernel code (between 1GB and 2GB).
Now, SQL Server is written to take advantage of physical address extensions (PAE). PAE is essentially a kludge that allows 32-bit applications to address memory above the 4GB address space. (64-bit addressing allows a theoretical memory space of 16 exabytes; 16,384 petabytes; 16,777,216 terabytes; since the current 64-bit operating systems limit physical memory much more than this, it does not look like we will reach this limit in the near future; for example, Windows 2003 R2 Datacentre x64 Edition supports up to 1TB of memory.)
In order to access the memory above the 2^32 boundary, an application must use a memory extension API such as Address Windowing Extensions (AWE), as SQL Server does. AWE memory is always backed by physical memory and cannot be swapped out to disk, so every memory page that is allocated to SQL Server remains allocated to SQL Server in physical memory; SQL Server will not release that page once it has been allocated. It may reuse that memory for a different data page, but SQL Server will not release it for use by another application. This is why the account under which the SQL Server service runs must be granted the 'Lock Pages In Memory' privilege. This behaviour has changed slightly with SQL Server 2005 when running on a Windows Server 2003 operating system. SQL Server 2005 Books Online says that SQL Server 2005 can manage AWE memory dynamically. However, it also says a few sentences later that this memory is non-paged memory implying it cannot be paged out once committed, which begs the question how this memory is dynamically managed.
Another thing to note about SQL Server and AWE memory is that SQL Server uses AWE memory, ie the memory above 4GB, only for the buffer cache. This allows a huge amount of data to be cached in memory, but does not affect the size of the procedure cache or other sections of memory that SQL Server uses. With AWE, SQL Server's buffer cache can access up to 64GB of physical memory, depending on the edition of 32-bit SQL Server. AWE is not relevant with 64-bit SQL since it is not bound by the same 2^32 byte limit; however, Microsoft still recommends granting the 'Lock Pages in Memory' privilege to the service account in order to avoid excessive paging.
The important settings to note when dealing with memory, with regard to SQL Server are:
/3GB or /USERVA (boot.ini)
/PAE (boot.ini)
'AWE enabled' (sp_configure)
'max server memory' (sp_configure)
'min server memory' (sp_configure)
/3GB and /USERVA, as already mentioned, limit the amount of the standard 4GB space that the kernel code can use to 1GB (or between 1GB and 2GB in the case of /USERVA in Windows 2003). This allows the user mode portion of applications to access up to 3GB of that first 4GB of memory.
/PAE enables the operating system to use physical address extensions thereby allowing access to physical memory in excess of 4GB.
'AWE enabled' turns on the AWE API code in SQL Server in order to access the memory above 4GB. If there is 4GB or less of physical memory in the box then SQL Server ignores the 'AWE enabled' setting.
'max server memory' defines the largest virtual address space that SQL Server is permitted to use. This is typically only used in conjunction with the 'AWE enabled' setting. SQL Server 2005 Books Online says this specifically relates to the buffer cache but SQL Server 2000 Books Online does not differentiate between the buffer cache and other memory sections with regard to the 'max server memory' setting and so the implication is that this limits overall memory use with SQL Server 2000.
'min server memory' defines the limit at which SQL Server will stop releasing memory dynamically. This setting is rarely used and usually unnecessary. It does not guarantee that SQL Server will be allocated at least this much memory. When SQL Server starts, it commits only as much memory as necessary, even if that is less than the 'min server memory' limit. As more data pages and query plans are cached, the memory that SQL Server commits increases. When this committed memory increases above the 'min server memory' limit SQL Server can free pages (down to that limit), as necessary, to keep the minimum free physical memory between 4MB and 10MB.
If 'min server memory' and 'max server memory' are configured to be equal then SQL Server does not manage memory dynamically. Memory is simply acquired gradually (as SQL Server's workload increases) up to that figure and then neither released nor further acquired. However, if 'min server memory' and/or 'max server memory' are assigned different values, such as the defaults of 0 and 2,147,483,647 respectively, then SQL Server will dynamically manage its memory allocation between those two figures. Memory allocation may grow up to the 'max server memory' limit and be reduced down to the 'min server memory' limit. This dynamic memory management is the more common scenario with SQL Server.
The easiest way to think about how these settings interact is with a matrix as follows:
RAM
/3GB
/PAE
'awe enabled'
'max server memory'
0-3
Not necessary
>3-4
*
Not necessary
>4-12
*
*
*
(Physical RAM) - (RAM needed for other applications & OS)
>12
*
*
(Physical RAM) - (RAM needed for other applications & OS)
(The RAM figures above are all GB; also, I apologise for the table formatting but it is very hard to do with this blogger template.)
These combinations are not the only ones possible but they represent the common scenarios. Did you notice that the /3GB boot.ini switch is not recommended over a certain memory size? This is because the kernel mode portion of that first 4GB of RAM is used to hold various system structures including the table that the operating system uses to map physical addresses over 4GB. By limiting the kernel mode space to 1GB you are also limiting the amount of physical memory that can be mapped over 4GB (since you are limiting the size of this memory-mapping table). Different experts define this threshold at different figures between 12GB and 16GB. To err on the side of caution I tend to use the lower figure as the boundary about which to remove the /3GB switch from boot.ini.
August 7, 2008 at 12:03 pm
These combinations are not the only ones possible but they represent the common scenarios. Did you notice that the /3GB boot.ini switch is not recommended over a certain memory size? This is because the kernel mode portion of that first 4GB of RAM is used to hold various system structures including the table that the operating system uses to map physical addresses over 4GB. By limiting the kernel mode space to 1GB you are also limiting the amount of physical memory that can be mapped over 4GB (since you are limiting the size of this memory-mapping table). Different experts define this threshold at different figures between 12GB and 16GB. To err on the side of caution I tend to use the lower figure as the boundary about which to remove the /3GB switch from boot.ini.
Ha! I never noticed that! Wow... Thanks for pointing that out.
I wonder what effect all this has with SQL Server 2008 and compressed databases... I think I need to pop back in the lab and play with the RTM now on our test server...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply