January 18, 2011 at 10:16 am
Everyone,
Server setup is as follows
SS2005 Standard w/ SP3
Windows 2003
64 Bit
64GB of RAM
Questions:
1) Why can virtual address memeory (VAS) get larger than the max server memory I have allocated to the sql server?
2) Does anyone know of a good site on setting up baselines for Memory usuage, both Physical and Virtual for 64bit. Most of the stuff I find is regarding 32 bit and those discussions on 64 bit are usually more of a "You'll never hit the 8tb limit in 64bit". I am not hitting this, but we are having memory issues.
Fraggle
January 19, 2011 at 6:23 am
One artcile I know of is @ http://searchsqlserver.techtarget.com/tip/Configuring-SQL-Server-memory-settings, Denny's Blog also should have lotza info on this. You need resgistration to use this link though.
January 19, 2011 at 2:36 pm
1. Because VAS is assigned by the OS to the applications and then is mapped to the available physical memory or the page file. You are restriction the extent of this mapping using Max memory setting and hence the difference.
BTW what memory issues are you facing ?
Thank You,
Best regards,
SQLBuddy
January 20, 2011 at 6:51 am
One thing that is ofter overlooked is the CPU has to maps and control memory addressing. More System memory = more time the CPU needs to work it.
I notice you did not list the CPU's, disk space on the system drive, and the size of your page file.
These are very important items that determine how well your system can use the memory it has, and how well it can serve those resources to an application like SQL server.
January 20, 2011 at 7:24 am
sqlbuddy123 (1/19/2011)
1. Because VAS is assigned by the OS to the applications and then is mapped to the available physical memory or the page file. You are restriction the extent of this mapping using Max memory setting and hence the difference.BTW what memory issues are you facing ?
Thank You,
Best regards,
SQLBuddy
-- some of the errors/issues I have gotten since Monday morning at 4:30 am EST.
AppDomains being unloaded due to memory pressure
Memory Dumps inside of SQL Server.
Downgrading Backup log buffers from 1024 to 64k
There is insufficient system memory to run this query.
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576
January 20, 2011 at 7:28 am
SanDroid (1/20/2011)
One thing that is ofter overlooked is the CPU has to maps and control memory addressing. More System memory = more time the CPU needs to work it.I notice you did not list the CPU's, disk space on the system drive, and the size of your page file.
These are very important items that determine how well your system can use the memory it has, and how well it can serve those resources to an application like SQL server.
2.8Ghz processor with 8 cores
PageFile is 4.3GB on the C drive which is a RAID 10
TempDB is a logical drive off of the Primary C drive above.
.MDF files are on a RAID 5 disk setup with SCSI disk 15000 rpm - D drive.
.LDF files are on a RAID 10 disk setup with SCSI disk 15000 rpm - E Drive
Backups are written to a mirrored drive again with SCSI - F drive
January 20, 2011 at 7:33 am
Can you post the return of the "SELECT @@version" command
Also please post "exec sp_configure".
If you could also tell us the size of the Page file, #CPU's, size of system drive.
My first opinion is that you are at the wrong SQL server service pack version for running on 64-bit, or you might need a hot fix from your manufacturer to accesses all your memory. However it could be something is configured incorrectly.
Also make sure the account SQL server is running as has the create page in memory system policy.
January 20, 2011 at 7:43 am
Fraggle-805517 (1/20/2011)
2.8Ghz processor with 8 coresPageFile is 4.3GB on the C drive which is a RAID 10
TempDB is a logical drive off of the Primary C drive above.
.MDF files are on a RAID 5 disk setup with SCSI disk 15000 rpm - D drive.
.LDF files are on a RAID 10 disk setup with SCSI disk 15000 rpm - E Drive
Backups are written to a mirrored drive again with SCSI - F drive
Your total PageFile<s> should always be 3% larger than your total physical memory. It can be on more than one disk, but it needs to be the same, or at least three times more than RAM you have. You can increase the size online and it can be used imediately. If this is not possible configure your
SQL server to MAX memory of MAX page file size.
The TempDB is more active than any database or log file will every be. Get it out of the logical drive that is a sub part of the system drive. This will kill the I/O of any query you make increasing the amount of physical read and writes to x3 your logical read and writes to the TempDb. Also logical disks on a raid in Win 2003 do not allow for quick file growth. You have to have quick file Growth in your TempDB. An imediate solution before moving the TempDb would be to manually grow the file if possible.
January 20, 2011 at 8:10 am
SanDroid (1/20/2011)
Can you post the return of the "SELECT @@version" commandAlso please post "exec sp_configure".
If you could also tell us the size of the Page file, #CPU's, size of system drive.
My first opinion is that you are at the wrong SQL server service pack version for running on 64-bit, or you might need a hot fix from your manufacturer to accesses all your memory. However it could be something is configured incorrectly.
Also make sure the account SQL server is running as has the create page in memory system policy.
Something to note here is that this server setup is the exact same server setup on 2 other machines. All 3 of the are running the same hardware configuration and software configuration. This is the only one having issues. Granted, this machine is a bit more utilized than the other 2.
@@Version = Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
sp_configure information
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
Ad Hoc Distributed Queries 0 1 0 0
affinity I/O mask -2147483648 2147483647 0 0
affinity mask -2147483648 2147483647 0 0
affinity64 I/O mask -2147483648 2147483647 0 0
affinity64 mask -2147483648 2147483647 0 0
Agent XPs 0 1 1 1
allow updates 0 1 0 0
awe enabled 0 1 0 0
blocked process threshold 0 86400 0 0
c2 audit mode 0 1 0 0
clr enabled 0 1 1 1
cost threshold for parallelism 0 32767 120 120
cross db ownership chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
Database Mail XPs 0 1 1 1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
default trace enabled 0 1 1 1
disallow results from triggers 0 1 0 0
fill factor (%) 0 100 80 80
ft crawl bandwidth (max) 0 32767 100 100
ft crawl bandwidth (min) 0 32767 0 0
ft notify bandwidth (max) 0 32767 100 100
ft notify bandwidth (min) 0 32767 0 0
index create memory (KB) 704 2147483647 0 0
in-doubt xact resolution 0 2 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 64 4 4
max full-text crawl range 0 256 4 4
max server memory (MB) 16 2147483647 59392 59392
max text repl size (B) 0 2147483647 65536 65536
max worker threads 128 32767 0 0
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 1024 1024
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
Ole Automation Procedures 0 1 1 1
open objects 0 2147483647 0 0
PH timeout (s) 1 3600 60 60
precompute rank 0 1 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
Replication XPs 0 1 0 0
scan for startup procs 0 1 0 0
server trigger recursion 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
SMO and DMO XPs 0 1 1 1
SQL Mail XPs 0 1 0 0
transform noise words 0 1 0 0
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Web Assistant Procedures 0 1 0 0
xp_cmdshell 0 1 1 1
January 20, 2011 at 8:14 am
SanDroid (1/20/2011)
Your total PageFile<s> should always be 3% larger than your total physical memory. It can be on more than one disk, but it needs to be the same, or at least three times more than RAM you have. You can increase the size online and it can be used imediately. If this is not possible configure yourSQL server to MAX memory of MAX page file size.
The TempDB is more active than any database or log file will every be. Get it out of the logical drive that is a sub part of the system drive. This will kill the I/O of any query you make increasing the amount of physical read and writes to x3 your logical read and writes to the TempDb. Also logical disks on a raid in Win 2003 do not allow for quick file growth. You have to have quick file Growth in your TempDB. An imediate solution before moving the TempDb would be to manually grow the file if possible.
We monitor our TempDB drive pretty regularly as we use it A LOT. No disk queues, no growth in the file after the intial allocation of 40GB, < 5 ms read time on average from the disk with spikes to 15 ms a few times a day.
Fraggle
January 20, 2011 at 9:34 am
Fraggle-805517 (1/20/2011)
SanDroid (1/20/2011)
Can you post the return of the "SELECT @@version" commandAlso please post "exec sp_configure".
If you could also tell us the size of the Page file, #CPU's, size of system drive.
My first opinion is that you are at the wrong SQL server service pack version for running on 64-bit, or you might need a hot fix from your manufacturer to accesses all your memory. However it could be something is configured incorrectly.
Also make sure the account SQL server is running as has the create page in memory system policy.
Something to note here is that this server setup is the exact same server setup on 2 other machines. All 3 of the are running the same hardware configuration and software configuration. This is the only one having issues. Granted, this machine is a bit more utilized than the other 2.
That totally makes sense and I have seen this error before on several servers with more than 4GB of physical memory and the Max memory value set to anything over 4GB and the page file set to 4GB or less and load DB into RAM not enabled.
This would happen on any 2000/2005 SQL server configured this way if it was used enough.
FIX: Increase the Total Page file size and make it 5% more than systems physical memory or as large as possible. It can be on more than one disk. Reduce the Max Memory available to SQL serever to equal the size of your page file.
You can check for HotFixes or Service Packs from M$ or your system vendor that address this memory utilization issues for your OS and SQL server version. I would not be suprised if you did not find any. What is happening is SQL server is trying to use the system virtual memory to store data that was in RAM and nothing is left. At lease that was how it worked in the past. I honestly have never worked with your exact config, but had the same thing when a sys admin did default installs of Win 2003 on an 8x2core CPU server with 16GB of Ram. Another time it was a 4x4core CPU server with 8GB of RAM. Changing the page file size allowed SQL server to move data in Ram to page file for caching. There is a way to configure SQL server to stop doing this, but it is not recomended since it can cause a performance hit. I would also recomend on your servers setting the Minimum memory setting to something around half the size of the Page file.
January 20, 2011 at 12:31 pm
Page file size is extremely low. It should be atleast twice the size of the physical RAM on the server.
Also Run a perfmon counter log for 24 hours and collect the following the counters to confirm the Memory Pressures
Memory: Available Mbytes
SQLServer:Buffer Manager\Page life expectancy
SQLServer:Buffer Manager\Pages/sec
SQLServer:Memory Manager\Memory Grants Pending
SQLServer:Memory Manager\Target Server Memory (KB)
SQLServer:Memory Manager\Total Server Memory (KB)
PagingFile: %Usage
PagingFile: %Usage Peak
And please post the results.
Also to prevent the paging of SQL Server Pages, it's recommended to use Lock Pages in Memory privilege. But it Requires CU4 for SQL SErver 2005 Standard editon with SP3.
Thank You,
Best Regards,
SQLBuddy
January 20, 2011 at 12:37 pm
Also Is there any specific reason to use Max Degree of Parallelism of 4 ?
Thank You,
Best Regards,
SQLBuddy
January 20, 2011 at 12:51 pm
sqlbuddy123 (1/20/2011)
Also Is there any specific reason to use Max Degree of Parallelism of 4 ?Thank You,
Best Regards,
SQLBuddy
Yes, this was basically an agreement with my manager. I wanted to set it to one as this is an OLTP system. He wanted it left wide option. We agreed that if the Cost Threshold for the query plan was going to be over 120 seconds (2 minutes), then we could use Parallelism. However, we were going to limit to 4 instead of the entire CPU if it was available.
This was a result of some massive wait times in our system due to Parallism.
Fraggle
January 21, 2011 at 5:09 am
I experienced the "AppDomains being unloaded due to memory pressure" error recently so I will explain how I fixed that so you can see if it works for you. The other issues may or may not be related. I have included a bit of background to explain why I think my fix worked.
As you have already read 64 bit SQL practically cannot run out of VAS (8TB is huge), similarly it is fairly difficult to run out of virtual memory (usually only if the page file size is capped or the drive the page file is on runs out of space). One thing that can happen is that the Windows server cannot respond to virtual memory requests fast enough. This can occur if the page file can’t grow quickly enough (check instant file initialization is working),
I assume these aren’t happening in your scenario and it is physical memory you are running out of. Although virtual memory is only limited by the size of your page file, those programs present in virtual memory will at some point require physical memory, if there is too much demand on physical memory at once, specifically Windows cannot run all the applications it needs to in the 5GB you have left between 5GB SQL max server memory and 64GB physical limit, it will forcefully unload applications. SQL CLR is a .Net application and "AppDomains being unloaded due to memory pressure" is SQL notifying you Windows has unloaded the CLR .Net because it needs the physical memory and couldn’t wait for the garbage collector to release it.
SQL server allocates memory above the max memory setting for large memory allocations using Multi Page Allocator (MPA).. The max memory setting only applies to the buffer pool which can be used for 8kb page allocations. This means SQL is also fighting for the memory in the 5GB space which may be the source of your other memory errors. Multi_pages_kb column in sys.dm_os_memory_clerks shows what is allocated outside the buffer pool i.e. above your max memory setting
Check what other applications are running on your server (antivirus, SAN management software, RDP, backup programs, large network file copies, DR/mirroring software like double take, IIS, .Net apps etc) and work out how much memory they need. NB there are a load of bugs with large network file copies in Windows.
I would suggest reducing your max memory setting to 54GB and see what happens. If it resolves your problem you could try increasing it gradually. Once SQL is running okay again you can tackle what is eating your memory. My scenario was the SAN management software also installed mysql and apache so i reduced the max memory from 24/28 Gb to 20GB which immediately resolved all issues.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply