Tuesday, January 22, 2008

Upgrade SQL2000 to SQL2005

Because tempdb is used for the version store, applications that make heavy use of triggers in SQL Server 2000 must be aware of potentially increased demands on tempdb after an upgrade to SQL Server 2005.
To maintain a production system using SI (Snapshot Isolation), you should allocate enough disk space for tempdb so that there is always at least 10 percent free space. If the free space falls below this threshold, system performance might suffer because SQL Server will expend more resources trying to reclaim space in the version store. The following formula can give you a rough estimate of the size required by version store. For long-running transactions, it might be useful to monitor the generation and cleanup rate using Performance Monitor, to estimate the maximum size needed. [size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

No comments: