July 13, 2012 at 8:21 am
I'm reaching out again to the only professionals I trust for SQL information.
I have a virtual x64 Win Svr 2K8 R2 with SQL Svr 2K8 R2. It has 32GB of RAM. I want to make some changes to the Page File because I'm experiencing some performance problems. I'm changing some other things on the DB such as maintenance plans and indexing.
Here's what I'm recommending:
C drive - OS Drive - 8GB (currently 4GB)
D drive - SQL Drive - System Managed (currently NONE)
E drive - Log Drive - 4GB (currently 4GB)
Would you recommend something different?
July 13, 2012 at 8:46 am
Yes. OS drive only. Why do you want extra IO on your log drive?
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
July 13, 2012 at 8:51 am
I have a drive for Page file, a drive for tempdb, a drive for logs and a drive for data.
July 13, 2012 at 8:54 am
I inherited the server and responsibility this week. I didn't spend much time on the log drive yet (but I'll start looking into it). I'm curious about the OS drive only comment you made. I don't need Page File on the SQL drive? Sorry, I've always had Page File (usually System Managed) on my SQL drives. What do you know that I don't (lol)? Why wouldn't I need a Page file on that drive?
July 13, 2012 at 8:57 am
Why would you need a page file on that drive?
Page file is just 'extra' memory for when the OS has too many demands and too little resources. Nothing to do with the specific drive.
Page file on the Log drive means extra IO load on the drive that contains what's likely to be the file that needs the best IO response.
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
July 13, 2012 at 9:15 am
I understand.
So what I learned here is, page file only the OS drive.
So I might recommend:
C - Currently 4GB - Change to "X"
D - Currently none - leave
E - Currently 4GB - change to none.
For the OS drive, I read an article stating on a 32GB memory x64 bit system it would be advisable to manually set 8GB pagefile on the Windows drive (min max both set to 8GB). This would then provide plenty of space for paging and plus allows a kernel only dump to be produced (in all but the worse case scenarios).
Would you recommend only 8GB?
July 13, 2012 at 10:10 am
On our serves we have a dedicated local drive just for the page file. You want some (I'm told a minimum of 800MB) on the system drive for memory dumps, and the rest on a drive just for paging, so there's no competition for IO with other processes.
July 13, 2012 at 10:16 am
SQL_Enthusiast (7/13/2012)
For the OS drive, I read an article stating on a 32GB memory x64 bit system it would be advisable to manually set 8GB pagefile on the Windows drive (min max both set to 8GB). This would then provide plenty of space for paging and plus allows a kernel only dump to be produced (in all but the worse case scenarios).
It sounds within reason.
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
July 13, 2012 at 10:22 am
dan-572483 (7/13/2012)
You want some (I'm told a minimum of 800GB) on the system drive for memory dumps
800GB for memory dumps? Seriously?
Unless you're sitting with a TB of physical memory, that's multiple full memory dumps. If your server is dumping memory (and full dumps at that) so often that you need to keep multiple, well there's something seriously strange going on.
Most of the time when dumps are needed they're mini-dumps. Core OS-related memory and little more. Not that much point in dumping out the entire data cache on a SQL Server machine.
What exactly is the logic and reasoning for wanting 800 or more GB free on the system drive? (that's many times larger than many system drives I've seen)
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
July 13, 2012 at 10:35 am
Should be 800MB - Typo fixed.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply