Post reply

Drives, SQL server and pagefiles

  • June 14, 2011 at 5:18 am

    #251867

    Hi,

    I've been asked by my company to spec and set up a brand new SQL server cluster, and while I'm mostly there with the spec, I'm having a bit of an issue dealing with the pagefile.

    The problem I'm having is that I'm not entirely sure what to do with it.

    The reason is, the current system is using a very minimal amount of the pagefile (5% of a 2gb one and < 1% of a 10gb one), and while I could put it on the same physical RAID 1 array as the OS, there's obviously going to be a major write performance hit on it, and write bottlenecks are something I want to avoid as much as possible.

    And from a cost perspective, since the pagefile isn't being used that much, I really don't want to have to go back and add a bunch of high-speed disks just to handle something that's got a total size of under 1gb.

    My original thought was to put the page files on a local SSD raid 10 array along with the TempDB, but as you can't have TempDB on a local drive when part of a cluster (or so I'm led to believe, although this suggests otherwise), the main requirement for the SSD array evaporated, and now I'm really not sure what to do.

    Does anyone have any suggestions on what I could do re the pagefile, or perhaps does anyone have anything on whether you CAN have TempDB on local storage in a cluster (and specifically if there's any sort of performance or redundancy hit in doing this)?

    Thanks in advance.

  • June 14, 2011 at 9:09 am

    #1338455

    Scott H

    Hi,

    I've been asked by my company to spec and set up a brand new SQL server cluster, and while I'm mostly there with the spec, I'm having a bit of an issue dealing with the pagefile.

    The problem I'm having is that I'm not entirely sure what to do with it.

    The reason is, the current system is using a very minimal amount of the pagefile (5% of a 2gb one and < 1% of a 10gb one), and while I could put it on the same physical RAID 1 array as the OS, there's obviously going to be a major write performance hit on it, and write bottlenecks are something I want to avoid as much as possible.

    And from a cost perspective, since the pagefile isn't being used that much, I really don't want to have to go back and add a bunch of high-speed disks just to handle something that's got a total size of under 1gb.

    My original thought was to put the page files on a local SSD raid 10 array along with the TempDB, but as you can't have TempDB on a local drive when part of a cluster (or so I'm led to believe, although this suggests otherwise), the main requirement for the SSD array evaporated, and now I'm really not sure what to do.

    Does anyone have any suggestions on what I could do re the pagefile, or perhaps does anyone have anything on whether you CAN have TempDB on local storage in a cluster (and specifically if there's any sort of performance or redundancy hit in doing this)?

    Thanks in advance.

    If you have the time and the equipment why dont you test? I do not see that your going to get a lot of performance increase from moving the pagefile if it is not going to be used that much. Also what happens if you move the pagefile and that drive goes down OS might not work? There is a lot to consider when moving something the OS is using.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • June 15, 2011 at 3:06 am

    #1338822

    I attended a presentation on clustering by Allan Hirt at the last SQLBits in Brighton a earlier this year and Denali is going to support tempDB on local drives in clustered environments with full failover functionality which of course you would want to implement as SSD if possible to leverage the outstanding performance that they can provide.

    Hope this helps,
    Rich

    [p]
    [/p]

  • June 16, 2011 at 5:18 am

    #1339569

    Thanks for the responses,

    bopeavy - Unfortunately the kit we've got at the minute won't match the planned kit, so any testing we do will be void as the results won't be the same. As far as resiliency goes, as we're setting up a cluster with a hotspare RAID1 array, if the drive goes down, the system should flick over to the next node should there be any issues with the drives.

    RichardDouglas - Thanks for confirming that. I went to the Mission Critical DB Tech day a few weeks ago and it was mentioned, and it's been something I'm aware of, but as we're upgrading to SQL2008 R2 it's not something I'm going to be able to take advantage of I'm afraid.

    At the moment, the suggestion has been that if the pagefile is only being used very rarely, that we should take the performance hit and leave it on the RAID 1 array with the OS/Programs, and possibly upgrade the servers with SSD's when we come to look at upgrading to Denali.

    I know this isn't an ideal scenario, but if anyone has any other recommendations, I'm open to suggestions.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply