Compaq ProLiant 1000 Compaq Backup and Recovery for Microsoft SQL Server 6.X - Page 63

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 63 inaccessible, although it is actively accessed only in the first scenario. The two environments do however, result in quite different performance dynamics. The system that was used for this testing was a Proliant 5000 server with two (2) Pentium Pro/166 processors and 1 GB of RAM. The system had enough memory and spindles (disk drives) to allow for a maximum transactional throughput bound by the CPU's. Two 15/30-GB DLT drives were attached to the system's integrated Fast-Wide SCSI-2 controller. Windows NT 4.0 and SQL Server 6.5 were installed on the system and a large database was created and loaded. The SQL Server SMP Stat and Boost Priority parameters were both deactivated (set to 0) for these tests. Enabling either of these parameters increases the priority of SQL Server threads relative to other process's threads under Windows NT. We have found that performing online backups with user activity can cause CPU utilization to rise significantly, and increasing SQL Server priority can increase the risk of a system 'lock-up' should all CPU's reach 100% usage. Setting SMP Stat to 0 or 'n-1' (where n = # of CPU's) is especially important when using a third party application (such as ARCserve) to assist in the backup, or else the SQL Server threads can starve the backup application and halt the backup process82. Finally, we recommend that an SMP system with at least two CPU's be used if online backups with user activity are to be done. Table 3 and Table 4 display the results of dumping a database that is under user activity using SQL Server and ARCserve, respectively. The database was under enough activity to generate a load that is 1/3rd (33%) of the system's maximum83. Table 3 - Online Dump of Active Database with SQL Server Transactions Before Dump Transactions During Dump Dump Transaction Response CPU Rate: Time: Usage: Transaction Response CPU Rate: Time: Usage: Throughput: No User n/a Transactions ~ 0% No User n/a Transactions ~ 6% 9.1 GB/hour 33% of Maximum .12 sec ~32% 4% to 16% of Maximum 1.42 sec to ~64% 0.65 sec 8.9 GB/hour Table 4 - Online Dump of Active Database with ARCserve Transactions Before Dump Transactions During Dump Dump Transaction Response CPU Rate: Time: Usage: Transaction Response CPU Rate: Time: Usage: Throughput: No User n/a Transactions ~ 0% No User n/a Transactions ~60% 10.0 GB/hour 33% of Maximum .12 sec ~32% 3% to 12% of Maximum 1.91 sec to ~99% 0.87 sec 10.0 GB/hour 82 Setting SMP Stat to 0 still allows SQL Server threads to use all CPU's present in the system, but guarantees that one CPU will be available for other processes by moving SQL threads off that CPU if necessary. Refer to the SQL Server Administrator's Companion for more information on this parameter. 83 We do not recommend attempting a backup of a database that is under a much heavier user read/write load. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797