Reduce your taxes installment 3 – The SQL Server Snapshot Tax

In the first of this series I propose that some organizations are over-provisioning their server resources in order to balance competiting performance and data protection SLAs. In part 2 we examined the “backup tax”. Now let’s look in detail at another example of this, “the snapshot tax” for establishing on-disk recovery points by doing native SQL Server snapshots.

Challenge: The Snapshot Tax – The following graph shows the results of testing snapshot processing on a transactional (TPC-C style) workload running in SQL server. It illustrates a 40% tax associated with establishing on-disk recovery points with the native SQL Server snapshot capability.

There are several observations from this data that should cause concern to those of you who would like to reduce your IT taxes:

  • Creating native recovery points impacts performance – average response times and transactions per second see a significant negative impact.
  • Creating a recovery point takes a long time – in this scenario about 10 minutes. So if you want to keep one recovery point per hour (RPO), around 1/6th of that hour will be spent with degraded performance.
  • The overall impact becomes cumulatively worse as more recovery points are kept.

Conclusion: Attempting to meet service level agreements for performance and data protection by establishing and keeping 5 hourly recovery points with native SQL server snapshots requires at least 40% over-provisioning of database server resources. This is an exorbitant 40% tax rate that you shouldn’t have to pay.

For more information on the testing that was done please refer to:  http://dell.to/w35fsv

Let’s hear from you. What is your RTO? Is one hour reasonable for recovery points for SQL Server transactional databases? What rate do you pay for snapshot taxes?

Advertisements

About Bob Ganley

Enterprise Information Technology Executive, Family Man, Athlete @ganleybob on twitter
This entry was posted in SQL Server and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s