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

Table 5 - Online Dump of Inactive Database with SQL Server

Get Compaq ProLiant 1000 PDF manuals and user guides View all Compaq ProLiant 1000 manuals
Add to My Manuals
Save this manual to your list of manuals

Page 64 highlights

Compaq Backup and Recovery for Microsoft SQL Server 6.x Page 64 When dumping an active database with SQL Server, the user transaction throughput significantly decreases during the dump operation: From 33% of the maximum throughput before the dump, it fell to 4% of the max at the start of the dump then gradually rose to about 16% of the max near the end of the dump. Average transaction response times also increased by about 12 times (start of dump) to 5 times (end of dump). As explained earlier, any transaction updating a page that has not already been backed up is placed on an internal queue, where it waits until SQL Server checks this queue, determines which page needs to be dumped next in order to "release" this transaction, dumps this page, and then allows the transaction to complete. This is believed to be the bottleneck seen in our testing. When using ARCserve to back up an online database, the ARCserve SQL Server Backup Agent actually interfaces with the native SQL Server dump process to retrieve the data. Thus, we can expect the affect on user transactions to be more or less the same as it was with the SQL Server dump alone. Table 4 however, when compared to Table 3, shows us that the transaction rate and response times were affected more by the ARCserve assisted dump than by the SQL dump. The reason for this is because the ARCserve dump has encountered another bottleneck: the system CPU's, which are saturated at 99%. As discussed earlier in the paper, the ARCserve backup process demands more CPU time than the native SQL Server backup, thereby having a potentially greater affect on other system activity. When we added two more CPU's to our system and re-ran the ARCserve test, the affect on user transactions was about the same as with the SQL dump (these results are not shown in Table 4). Finally, as for the throughput of the dump operation, it fell slightly from 9.1 GB/hr (maximum dump throughput achieved with 2 DLT drives with no user activity) to 8.9 GB/hr during the native SQL Server dump with user activity, and was unaffected during the ARCserve dump with user activity. In general though, it is the user transaction performance and not so much the dump performance that is affected during a backup of an active database84. For this scenario, a second database was created on the system. This database was placed on its own disk array separate from the first database, so that I/O from activity on one database would not interfere with processes on the other database. While user transactions were simulated on the first database, the dump process was initiated on the second database. Table 5 and Table 6 display the results of dumping a database while another database on the same SQL Server is under user activity, using SQL Server and ARCserve, respectively. The database was placed under variable activity to generate a load that was first 1/4th (25%) of the system's maximum, and then a load that was 3/4th (75%) of the system's maximum, to simulate both moderate and high use conditions. Table 5 - Online Dump of Inactive 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 25% of Maximum .12 sec ~26% 25% of Maximum 0.12 sec ~32% 9.1 GB/hour 84 This is true for an OLTP-type environment with numerous read/write transactions. A Decision Support-type environment involving large read-only queries may behave differently since reads are not affected by the backup process.

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
Compaq Backup and Recovery for Microsoft SQL Server 6.x
Page
64
When dumping an active database with SQL Server, the
user transaction throughput significantly
decreases during the dump operation: From 33% of the maximum throughput before the dump, it fell to
4% of the max at the start of the dump then gradually rose to about 16% of the max near the end of the
dump.
Average transaction response times also increased by about 12 times (start of dump) to 5 times
(end of dump).
As explained earlier, any transaction updating a page that has not already been backed
up is placed on an internal queue, where it waits until SQL Server checks this queue, determines which
page needs to be dumped next in order to “release” this transaction, dumps this page, and then allows
the transaction to complete.
This is believed to be the bottleneck seen in our testing.
When using ARCserve to back up an online database, the ARCserve SQL Server Backup Agent
actually interfaces with the native SQL Server dump process to retrieve the data.
Thus, we can expect
the affect on user transactions to be more or less the same as it was with the SQL Server dump alone.
Table 4 however, when compared to Table 3, shows us that the transaction rate and response times
were affected more by the ARCserve assisted dump than by the SQL dump.
The reason for this is
because the ARCserve dump has encountered another bottleneck: the system CPU’s, which are
saturated at 99%.
As discussed earlier in the paper, the ARCserve backup process demands more CPU
time than the native SQL Server backup, thereby having a potentially greater affect on other system
activity.
When we added two more CPU’s to our system and re-ran the ARCserve test, the affect on
user transactions was about the same as with the SQL dump (these results are not shown in Table 4).
Finally, as for the throughput of the dump operation, it fell slightly from 9.1 GB/hr (maximum dump
throughput achieved with 2 DLT drives with no user activity) to 8.9 GB/hr during the native SQL
Server dump with user activity, and was unaffected during the ARCserve dump with user activity.
In
general though,
it is the user transaction performance and not so much the dump performance
that is affected
during a backup of an active database
84
.
For this scenario, a second database was created on the system.
This database was placed on its own
disk array separate from the first database, so that I/O from activity on one database would not interfere
with processes on the other database.
While user transactions were simulated on the first database, the
dump process was initiated on the second database.
Table 5 and Table 6 display the results of dumping a database while another database on the same SQL
Server is under user activity, using SQL Server and ARCserve, respectively.
The database was placed
under variable activity to generate a load that was first 1/4
th
(25%) of the system’s maximum, and then
a load that was 3/4
th
(75%) of the system’s maximum, to simulate both moderate and high use
conditions.
Table 5 - Online Dump of Inactive Database with SQL Server
Transactions Before Dump
Transactions During Dump
Dump
Transaction
Rate:
Response
Time:
CPU
Usage:
Transaction
Rate:
Response
Time:
CPU
Usage:
Throughput:
No User
Transactions
n/a
~ 0%
No User
Transactions
n/a
~ 6%
9.1 GB/hour
25% of
Maximum
.12 sec
~26%
25% of
Maximum
0.12 sec
~32%
9.1 GB/hour
84
This is true for an OLTP-type environment with numerous read/write transactions.
A Decision Support-type
environment involving large read-only queries may behave differently since reads are not affected by the
backup process.