If you don’t follow the advice given elsewhere on this website about dedicating your SQL Server’s to a physical server of their own, and you decide to run multiple applications on the same server (SQL Server plus others), you may be faced with more paging activity than you would normally experience on a dedicated SQL Sever (which has very minimal paging).
If this is the case, one way to help boost performance on your non-dedicated server is to spread the paging file over multiple disk or disk arrays. You can spread a paging file in NT Server 4.0 and Windows 2000 onto as many as 16 separate files. By spreading out the paging file among more than one physical drive, simultaneous I/O requests can occur, speeding up access to the paging file (similar to disk striping). If the paging file is located on a single drive (the default setting when NT or 2000 is installed), along with the operating system and other applications, then all these processes have to compete for I/O, which can produce bottlenecks. The more physical devices that the paging file can be distributed, the less potential for I/O bottlenecks, and performance is boosted.
Keep in mind that you must distribute the paging file over physical drives, not drive partitions on a single drive, for this to work. In fact, if you were to distribute a paging file over multiple partitions on the same physical drive, you would in fact reduce the performance of the paging file even more than if you had left it on one partition.
If your SQL Server has its own dedicated physical server, you don’t have to worry about spreading the paging file among multiple physical drives as there is not enough paging activity to make this effort worthwhile. [6.5, 7.0, 2000] Added 12-11-2001
]]>