Post reply

Pagingfile placement

  • January 15, 2004 at 1:13 am

    #82133

    Hello. Is there any one who has an opinion abut the placement off the pagingfile on a SQL Server? Should I place it with the OS, databasefiles or the logfiles?

     

    Thanks from Joel

  • January 15, 2004 at 5:36 am

    #490173

    Here is the info from win 2k BOL

    "The location and size of the paging file can greatly affect performance. Putting the paging file on a separate, fast, low-use drive can boost performance. The size setting of the paging file is also critical to performance. A paging file that must constantly expand and shrink requires additional processor and I/O overhead. Usually, you should set the size of your paging file at two or two-and-a-half times the amount of installed physical memory if applications such as Microsoft SQL Server or Microsoft Exchange Server are present."

    i feel that u should not put it on the drive which contains sql program files/data/log files

    hope it is useful to u

    Anil

  • January 16, 2004 at 7:03 am

    #490411

    I agree with Anil, but the it is better to put the pagefile with the SQLServer program files or OS than with database files.  The OS programs and sqlserver program files will not be read that much.  They are used and therefore in memory or not used and therefore irrelevant.

    Joachim.

  • January 16, 2004 at 11:00 am

    #490491

    Honestly we tend to leave it on the OS drive (With SQL files) since our boxes are usually dedicated to SQL Server.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 16, 2004 at 11:49 am

    #490507

    I agree, but I think having it fixed sized is of greatest performance importance.

    I like to "fix size" as early in the PC installation process as possible so that the

    permanent, fixed size pagefile is contigous, and as close to the 1st object on the

    hard disk (physically fastest HD access). If system will have a HD that can be dedicated

    to the pagefile, and may be other "Temp" stuff, then I make it a FAT32 (less overhead than

    NTFS), and put the fixed size pagefile as the 1st object on the drive. FAT32 does not have

    the security featues of NTFS, so in these cases, I only put files on drive that either are very

    trasient, or can not be "comprehendedly" (my new word) read while system is running, such

    as the pagefile itself. Also I've heard that there my not be a performance advantage FAT32 vs

    NTFS any longer.... have not tested since Win2K early on.

    all above IMHO.



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 2:00 pm

    #490541

    Hello Listers,

    Why don't let the paging file at its default value at my sens a server on which SQL Server is installed should not swap at all.

    No?

    Thank's

    Carl

  • January 16, 2004 at 2:28 pm

    #490550

    Giro,

    The reason is, the O/S will recreate the pagefile when it is restarted, or dynamically size according to needs. Many, many O/S operations use this file, not only for "Virtual Memory / swap room" but for general temp data storage etc. reasons.

    When dynamically resized all the time, or recreated, the pagefile may be, or become, fragmented to the point of making your system crawl, especially when there is little disk space left. Also the pagefile will be on a "slower" portion of the HD itself, getting even slower as your disk fills up, since O/S - HD systems usually write data to the fastest portion of the disk first.

    At a minimum, Defrag your disk, set pagefile to fixed size.

    Note: Since data is written to the HD in a "first data gets fastest HD location" method, putting the pagefile on anything but the 1st partition on the disk will be slower than the same fixed size pagefile on the 1st partition. This is because the 1st partition will be on the "fastest" part of the HD, while each subsequent partition will have slower performance, since they start at a position at the "end" of the 1st partition's capacity.

     



    Once you understand the BITs, all the pieces come together

  • January 16, 2004 at 3:45 pm

    #490561

    Thomas,

    I wasn't aware of that:

    "The reason is, the O/S will recreate the pagefile when it is restarted, or dynamically size according to needs. Many, many O/S operations use this file, not only for "Virtual Memory / swap room" but for general temp data storage etc. reasons."

    I will read more about that.

    Thank's a lot

    Carl

  • January 20, 2004 at 10:00 am

    #490851

    How (and where) can it be set at the os level that the pagefile is not recreated each time the server restart?

    Thanks

    Carl

  • January 20, 2004 at 10:53 am

    #490865

    Win2k - (RightClick) My Computer - Properties - Advanced - Performance Options

     



    Once you understand the BITs, all the pieces come together

  • January 20, 2004 at 10:58 am

    #490868

    Yes but how to tell the os to not recreate it each time the server restart?

    Is it by just giving it an initial size and max size (equals)?

    Thank's

    Carl

  • January 20, 2004 at 11:21 am

    #490877

    Yes, same min/max, as far as I can remember.... (been a while, and getting older )



    Once you understand the BITs, all the pieces come together

  • January 20, 2004 at 11:36 am

    #490881

    Ok thank's Thomas.

    Best regards,

    Carl

  • January 20, 2004 at 11:51 am

    #490883

    NP, just post any results of what you do if you see any significant performance gains / losses depending on what all you're seting up or testing.

    Thanks



    Once you understand the BITs, all the pieces come together

Viewing 14 posts - 1 through 13 (of 13 total)

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