I'd like to learn about swap file configuration rules and
standards for a production environment that runs SQL-2000
on a Windows 2000 database enterprise server.
The database runs on a host server with 4 G of RAM memory.
I was of the impression that swap files typically need to be
2 to 2.5 times the size of RAM. In that case the size of swap
allotted to the database server should be 8 to 10 G. Is this
a correct estimation of swap file size for optimal performance.
There is much index maintenance that's done with this
database server. Index defragmentation jobs typically run
on tables with approximately 30 million rows.
Swap has currently been set to 4 G. Would that be sufficient
in this case ?
Thank you in advance
SQL Server knows how to efficiently allocate a server's RAM, which means you don't have to. If SQL Server needs more RAM, and it is available, SQL Server will take it. If it doesn't need additional RAM, it will give it up. This is done automatically and no human intervention is required. By allowing SQL Server to allocate memory dynamically, SQL Server will not need to use the operating system swap file to page data into and out of memory. This is important, as paging will hurt SQL Server's performance.