Question

Angelo AA on Tue, 23 Apr 2013 03:58:57


Hi Guys,  i hope i can get some guidance on this.

We have an SQL 2008 instance running on Windows 2008 R2.  This server is a Virtual Machine and is also connected to a SAN for disk space.  We have 4 drives configured for use on this server.  

Local C:\ drive (System) - 60GB

E:\ (used for storage of SQL databases.) - 300GB

F:\ (used for SnapMirror Info) - 150GB

G:\ (exclusive use for Page File) - 100GB!  PageFile settings manually set to 100GB

Memory - 8GB RAM

CPU - 4 x vCPU

pagefile settings

What i would like to know is whether it is necessary to have a page file this large?  I have heard that normal settings should be 1.5 x the amount of installed RAM.  We are currently only running one database instance using 10 databases.  

We have noticed, when using PerfMon, that there are alot of PageFaults per/sec.  The application utilising this database server is always crashing but we are unsure why.  If i lower the page file size, will it affect the application using this database?

If anyone can give me some advice it would be greatly appreciated.


Sponsored



Replies

Andrew Bainbridge on Tue, 23 Apr 2013 14:53:07


Hi,

Is it x86 or x64?

100GB is very excessive for your server. The recommendation of 1.5 X the amount of RAM installed is typically to accommodate crash dumps, but for a server your size, 1.5 x is a setting I'd start with.  You can then use perfmon to monitor page file usage and adjust from there.

Anyway, if your page file's getting hammered, you should buy more RAM.


Angelo AA on Wed, 24 Apr 2013 01:23:47


Hi Andrew,

The server is x64, is a VM and is hosted on ESXi 5.1 and VMware 5.1.  Being an SQL server, would a page file this size cause performance issue?  I have read that SQL servers should have minimal paging requests overall.  Is this accurate?

Regards,

Andrew Bainbridge on Wed, 24 Apr 2013 09:20:15


Hi,

I don't believe a huge page file hurts performance, though you're obviously depriving yourself of 85ish GB of space that could be used for other things.  You'd probably be better off having that drive exclusively for your TempDB, depending on your disk layout.

SQL server will use the page file just like everything else in Windows if there's not enough RAM.  However, the goal is to keep it to as close to 0 as possible.

Angelo AA on Fri, 26 Apr 2013 03:30:34


Thanks for the input.  Two more questions though...

Firstly, is it good practice to keep tempdb.mdf and associated logfiles on a seperate drive?  Preferably one that is not being used to store page files?

Secondly, any idea why i cannot choose your answers as solutions?  There is only Reply and Quote.

Thanks again.

Andrew Bainbridge on Fri, 26 Apr 2013 14:30:25


Hi,

http://msdn.microsoft.com/en-gb/library/ms175527(v=sql.105).aspx

Yes, best to have TempDB on a separate drive.

I think it's because you've create a discussion thread rather than a question.

Thanks,

Daniel_Steiner on Fri, 26 Apr 2013 19:39:36


hi Angelo

the pagefile size is definitively incorrectly configured compared to the 8GB Virtual RAM.

Are there any other application (-services) running on this specific VM or is it a dedicated SQL Server setup?

Are you logging into this VM to use SSMS or is any SQL maintenance done remotely ?

you're writing about 10 database but how are those databases used, eg. how many transaction per seconds, using stored procedures, using CLR etc. is important as well to known.

and the most important question - have you configured the SQL Server max memory setting or did you leave the default settings ? in that case you should change it to less than 6GB such that enough memory is available to the OS.

You should configure your system such that pagefile would not really be used as everything fits into memory.

Angelo AA on Mon, 29 Apr 2013 00:46:35


Thanks for both of your answers Andrew and Daniel.  

Daniel, I login to the SQL server usually via Remote Desktop but have access via the VM.  The SQL server is a dedicated SQL server which runs databases for only one application.  It is a document management system that spans multiple databases called Redmap.

As for database specific questions, im not entirely sure how many transactions per second are completed.  I think it uses stored procedures.

Server Max Memory settings are default and are currently the following.  No minimum memory settings and the maximum server setting is ridiculously massive with a value of 2147483647MB!  Im not sure where this value even came from?!  If the server is only given 16Gb RAM should i adjust the value to about 13Gb for Max Server memory as stated in the link supplied by Andrew?  What about the Index memory and min memory per query settings?  Am i able to change these for database performance increases?  They are currently 0Mb for Index creation memory and 1024Mb for min memory per query.

Memory settings in Task Manager are:  Total 16Gb, Avail. 7.8Gb, Free 6.6Gb

Finally, regarding the page file settings.  If C:\ has a system managed page file and the E:\ is custom set, does that mean that there are two page files currently?  Can i set it so that the only page file is the one on the E:\?

Thanks again.



Andrew Bainbridge on Mon, 29 Apr 2013 15:35:36


Hi,

13GB would seem fair enough to start.  The value you currently have set (2147483647MB) is the default value set by SQL Server out of the box.  I would use performance monitor to find out how much memory SQL Server's using, and how much memory it wants.

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

I've never tweaked the index memory settings, so I think you can leave it as it is.

With your page file split across multiple disks, depending on how your disk subsystem is configured, you may speed up access times.  There's no point having multiple page files if they're on the same disk.  I seem to recollect that if you don't have any page file on C:, you lose the ability to capture crash dumps.

Angelo AA on Tue, 30 Apr 2013 06:44:01


thanks for the information.  I will follow the articles and your advice and tune up my SQL server.

FYI - The Page Life Expectancy is currently at approx 38000 and climbing every second.