September 28, 2011 at 2:01 pm
I have a SQL 2008R2 64 bit server running on VMWare and it is allocated 24 GB of memory allocated to the server. (There is 48GB available on the hardware, but we don't want to use it all, yet).. My buffer cache ratio is 99-100%
While looking at the "Resource Monitor" I noticed that sqlservr.exe has IO on the pagefile.sys files that I have. Why is this? Shouldn't sqlservr page to its own LDF's?
The disks that I have the mdf and ldf files on are fast/high IO disks, the pagefile is on slightly slower disks...
I do not have a max memory setting for SQL yet, thought I did read online somewhere that "they" recommended reserving about 4GB for the OS and other processes. True?
Speaking of other processes, all that I have running on this box other that sqlservr are: SSRS, DoubleTake (replication software), and Symantec A/V.
Any insight on this would be greatly appreciated. Thank you!
September 28, 2011 at 2:30 pm
jgoodier (9/28/2011)
While looking at the "Resource Monitor" I noticed that sqlservr.exe has IO on the pagefile.sys files that I have. Why is this? Shouldn't sqlservr page to its own LDF's?
Errr, page files and log files aren't the same thing, aren't anything close. Page file is disk pretending to be memory. Log file is SQL's record of all permanent database changes to ensure consistency and durability of all changes.
I do not have a max memory setting for SQL yet, thought I did read online somewhere that "they" recommended reserving about 4GB for the OS and other processes. True?
You absolutely must set max memory to a sensible figure, or SQL will take everything available. What you're probably seeing is SQL taking too much memory and the OS forcefully paging it out to ensure it has enough memory to keep working.
4GB? No, that's certainly not a hard figure. The more memory on the server, the more should be reserved for the OS. At 24GB and assuming nothing else on the server I'd probably set SQL's max memory to 21GB. If there's other software then that needs to be reduced by however much the other software uses.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply