July 18, 2007 at 4:13 pm
-SQL Server 2005 Enterprise Edition
-Windows 2003 Enterprise Edition
-Cluster (active/passive)
-AWE is set, results of sp_configure show AWE is 0,1,1,1
-Pages are locked in memory for the SQL Server service account
-8 Procs total of 16 GB available
-Small database (2.6 GB)
We monitored the server and found that "SQL Server: Buffer Manager" / counters "Target pages" and "Total pages", provides the following type of information. Target pages indicates the number of pages that SQL Server considers ideal given the workload. Total pages indicates the total number of pages in the buffer pool.
Our information shows us that per 'Total pages', we are only using 4 GB of memory. 'Target Pages' is showing almost 14 GB. This seems to indicate that SQL Server WANTS to use 14 GB but is being limited to 4 GB.
Why? Or am I misunderstanding the information? Might it be that the database is smaller than 4 GB so it doesn't need more memory (the whole db fits into the 4 GB of memory)? If so, then why is the Target showing 14 GB?
-SQLBill
July 19, 2007 at 12:32 am
SQL Server uses the Windows concepts of Memory Management.
Reserved/Committed/Free.
The 14GB of Target Pages is the reserved amount of virtual memory that SQL Server has used so that other processes in the box cannot use them.
In the event of memory pressure from other processes SQL is going to release it.
Reserved usage guarantees that SQL Server is going to use this amount of memory in future and would commit once the Procedure Cache and Data Caches needs memory.
Once Reserved SQL Server can COMMIT these pages when it needs it and thats what is shown in Buffer Pool. In 32 bit Systems the buffer pool size is limited to < 2 GB of User Address Space. Out of the 2 GB MTL : Memory to Leave Region (holds linked server,threads and dlls). Rest is Buffer Pool ( which is Data & Proc).It is a bit different in 64 bit systems.
The rest of the Area is mapped to AWE region which makes room for anything larger than 2 GB of RAM.
So if you are seeing this should be perfectly normal unless & untill you encounter the following scenarios
1> Huge degradation of Performance
2> Lots of SP:CacheRemove Events in Trace due to the fact that Memory is not enough.
3> Very low Available M Bytes in the Process Counter (order of < 200 MB) which may result in Paging.
4> High amount of Paging .... Pages/sec
July 19, 2007 at 2:27 am
is /PAE switch added to your boot.ini file for 16GB or more memory you have to add the switch to have memory managements process.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 19, 2007 at 3:01 am
Generally when awe is enabled sql server will grab all the memory, I assume you've set max and min memory ? I've never been able to run a small database on lots of memory, always the other way around.
I figure it's not a problem
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 19, 2007 at 3:22 am
Generally its a good idea to specify the RAM max when using more than 16GB and using the memory options. Colin has pointed that out correctly.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 20, 2007 at 1:22 pm
Yes, /PAE is set and we did adjust the min/max memory (100 MB & 2,147,483,500 MB). We always set it a bit higher than the default min and a bit lower than the default max.
All of our tools show that every query maxes out at 4 GB and never uses any memory over that.
-SQLBill
July 23, 2007 at 1:19 am
Are you sure every query uses 4GB of memory. 4GB for each query is too high which can';t be accepted. Check if the value is right and post again.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply