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

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 71 After restoring the data pages during a load, SQL Server will reinitialize any unused pages remaining in the database. Depending on what percentage of the database remains unused, this process can consume a significant amount of the overall load time, and generates much I/O in the form of 16KB writes to the data device. When re-creating a database for the purpose of restoring it from a dump, use the FOR LOAD option in the CREATE DATABASE statement (new for SQL Server 6.0), in order to skip the initialization of data pages. Since unused data pages are initialized as part of the load process anyway, skipping this step during database creation can save time and I/O. Also, SQL Server 6.x will now perform the I/O to create the database in large (up to 64KB size) units, thus improving the speed. During a load, SQL Server reads data from the dump device (disk or tape) in 64KB chunks88, and into the in-memory buffer. From there, they are written out to the disk in 16KB extents (new for SQL Server 6.0), which are units of 8 contiguous data pages. Thus, roughly 4 writes to the data volume should occur for every read request from the dump volume or tape drive. If you are loading one database at a time from a single device, performance of the backup device will generally be the limiting factor. If you are loading multiple databases concurrently, or are loading a database from multiple, striped devices, then the performance of 16KB writes to the data volume may become the limiting factor. This is largely dependent upon the type of fault tolerance implemented on the data array, and the number of spindles in the data array. Performance during loads will be negatively affected by implementing a fault tolerance of RAID-5 (striping with parity) on the database volume. This is because when writing data back to a RAID-5 disk array, 4 physical I/Os (2 reads and 2 writes) must normally occur for every logical write request so that the parity information may be re-created. The impact of this is partially offset by the Smart-2 controller's ability to detect a sequential write stream and thereby minimize the amount of parity information re-created. Other important considerations can be found in the Microsoft SQL Server manuals and the Microsoft SQL Server, Backup and Recovery Guidelines document, available from Microsoft. The purpose of this section is to provide information on the database load performance, which can then be compared to database dump performance from previous sections. Measuring the performance of a database load is more complicated than for a dump. This is because, as mentioned above, the load process actually consists of two phases: 1. Reading the data from the backup media while restoring it to the database on disk, and 2. Reinitializing all the pages in the database which remain unfilled. Thus, the performance of both of these I/O intensive operations must be measured separately in order to gauge the overall database load throughput for a system, and thereby accurately predict load time. Furthermore, database load performance is more dependent upon the nature of the disk volume housing the database, than is database dump performance - especially when restoring from multiple, striped devices. Therefore, the type of data array in the system is an important consideration during both phases of the load process, as it may very well determine the performance bottleneck. For our analysis of load performance we use two charts: one for each phase of the load process. For each set of tests we also vary the number of spindles (disk drives) and RAID level used on the data array. All loads were done from a stripe set of eight 15/30-GB DLT tape drives using the native SQL Server load functionality. The system was a Proliant 5000 with a single Smart-2 SCSI array controller. 88 The 64KB read from a tape device occurs in blocks based on the corresponding tape block size. 1997 Compaq Computer Corporation, All Rights Reserved Doc No 444A/0797