Pagingfile placement

Question

Pagingfile placement

Joel_sjoo

Old Hand

Points: 332
More actions
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

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

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

pjanil1 SSC Eights! Points: 883 More actions · Answer 1

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

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 2

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.

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 3

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 4

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

Carl B. SSCertifiable Points: 7931 More actions · Answer 5

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 6

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

Carl B. SSCertifiable Points: 7931 More actions · Answer 7

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

Carl B. SSCertifiable Points: 7931 More actions · Answer 8

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

Thanks

Carl

ThomasH SSCertifiable Points: 7437 More actions · Answer 9

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

Once you understand the BITs, all the pieces come together

Carl B. SSCertifiable Points: 7931 More actions · Answer 10

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 11

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

Carl B. SSCertifiable Points: 7931 More actions · Answer 12

Carl B.

SSCertifiable

Points: 7931

January 20, 2004 at 11:36 am

#490881

Ok thank's Thomas.

Best regards,

Carl

ThomasH SSCertifiable Points: 7437 More actions · Answer 13

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