White Papers

BP1014 Enhancing SQL Server Protection using Dell EqualLogic Snapshot Smart Copies
14
snapshot creation, while retaining multiple snapshots. Differences between this test and the previous
one:
 During creation of all snapshots in the series we ran a constant TPC-C style workload on the
system, simulating 4000 users.
 We did not drop any previous snapshots during the test sequence.
The results of this test are presented in Figure 6.
Figure 6 – Time to create SQL Server snapshots (previous snapshots retained)
Based on the results of these tests, it is clear that with varying load and number of snapshots kept, the
time to take a snapshot is variable and hence difficult to predict. Minimizing number of snapshots
retained would decrease the time it takes to complete the snapshot operation.
3.3.3 Impact on system and database performance while creating SQL Server snapshots
During the cumulative snapshot creation test in the previous section we also measured system CPU
utilization, database transactions per second (TPS) and average database response time. The results we
gathered are shown in Figure 7. The top chart in Figure 7 shows CPU utilization during the time when
we created the five SQL Server® snapshots discussed in the previous section. The bottom part of
Figure 7 shows the transient impact on database transactions per second (TPS) and average response
time during the creation of the snapshot. From the results we can conclude that:
 Retaining multiple SQL Server® database snapshots will incrementally increase the workload
on the system. By the time the fifth snapshot was created, average CPU utilization had
increased by ~30% over what it was before the first snapshot was created.
 Creating SQL Server® snapshots causes significant short term impact on database
performance. As shown in Figure 7, we observed a large increase in average response time and
a large decrease in TPS during creation of the snapshot.