How often are SQL Server database index rebuilds required on XtremIO?

A question came up recently from Shridhar Deuskar (@ioguru on twitter) one of my colleagues who works in XtremIO engineering. We were discussing best practice for SQL Server on an XtremIO array and we got on to talking about if SQL Server index rebuilds arenecessary on XtremIO. My first thought was yes they are still necessary, but there will be fewer use cases for index rebuilds than on traditional spinning disk arrays; so I decided to test the hypothesis. Using our lab environment I devised a number of tests introducing fragmentation to database indexes and measuring the impact on performance and array capacity. Based on the testing scenarios used even at high levels of fragmentation there was little impact on performance (1%~2.7%) and disk capacity impact was negated with XtremIO inline deduplication and compression. In this blog post I share the results and explain the following:

 

  • Why index rebuilds are required for SQL Server
  • How to determine when they are required
  • What is different about XtremIO
  • Finally the technical details of how the tests in the lab were carried out.

 

 

Why perform index rebuilds in SQL Server?

A good way to think about index rebuilds is to think about getting your car serviced. When a car has been driven for a while it requires servicing and during this time the driver is inconvenienced. Servicing has advantages. The car will last longer, depreciate less and run smoothly. But conversely if a car is serviced too often you could just be wasting money and unnecessarily inconveniencing the driver. Scale this up to a global fleet of cars run by an enterprise the figures become alarming. Databases are similar, once they have been in use for a while on traditional arrays performance can decrease and more capacity may be required than strictly necessary. This is because data activity can cause fragmentation within the database. Specifically in SQL Server there are two types of fragmentation:

 

  • Internal fragmentation; wasted space within database pages resulting in increased capacity
  • Logical fragmentation; the pages are physically stored out of order to a tables index which impacts performance

 

Index rebuilds service the database by compacting wasted space and sorting the data pages, matching the physical order to the logical order. During the process, users are inconvenienced as the operation competes for resources such as CPU cycles,memory and storage limiting the performance to the database. It is important to make intelligent decisions about when to run index rebuilds otherwise you may be wasting company resources, time and inconveniencing users

 

 

Why SQL Server on EMC XtremIO is different

  • Capacity – XtremIO inline deduplication and compression works on database pages before they are written to disk,helping to mitigate the wasted space caused by internal fragmentation at the disk level.

1 (2)

 

Testing SQL Server on XtremIO, a data load of 54GB was inserted to the target database generating ~17,000 page splits per SQL Server batch request. This resulted in internal fragmentation averaging 32% wasted space per data page as well as additional writes to the transaction log.  XtremIO inline compression and deduplication negated this by achieving a42% disk space saving over the logical space allocated.

 

*A page split occurs when a new row needs to be added to a data page that is full. SQL Server moves around half of the existing page rows to a new page to free up space

 

  • Performance – For testing on XtremIO with SQL Server, even at high levels of logical fragmentation, query response time for both DSS and OLTP workloads show similar results to the same query after removing index fragmentation with an index rebuild.

 

Before Duration (Seconds or TPS)

After Duration (Seconds or TPS)

% Performance Difference

% (MAX) Fragmentation

Query Test 1 (Run a DSS query before and after a shrinkfile operation)

348

357

2.7%

93

Query Test 2 (Data load with high page split ratio. Run DSS query before and after index rebuild)

50

48

2.0%

75

Query Test 3 Complex Query (data load with high page split ratio. Run complex  DSS query before and after index rebuild)

344

334

2.6%

75

OLTP Workload Test  (ran before and after a shrinkfile operation)

18163 (TPS)

17971

1.1%

99

On EMC XtremIO how should we approach index maintenance?

Microsoft has documented an approach based on the level of fragmentation the details of which you can read here: Reorganize and Rebuild Indexes and Microsoft SQL Server 2000 Index Defragmentation Best Practices

A more applicable test for XtremIO is to take a backup of your database while it is fragmented, then use it as a baseline in a test environment to determine if the current index maintenance strategy for your application workload is improving performance and increasing capacity on XtremIO.

Depending on business requirement’s testing should incorporate a number of iterations to determine the best maintenance plan for example:

 

Performance Impact Capacity Impact
Baseline – fragmented indexes (record level of fragmentation)

Index Rebuilds

Index Reorganise

Statistics Update Only

 

Just keep in mind that statistics maintenance is an important consideration and will have to be considered in scenarios where index rebuilds are no longer required or where index reorganise is implemented.

 

How was testing carried out?

Using an Industry standard DSS benchmark database; data loading the main table using a random cluster key. We ran queries using these conditions:

  • Index order
  • Allocation order
  • Rebuild index to remove fragmentation.

 

Taking an industry standard OLTP benchmark database and running workloads before and after shrinking the SQL Server data files.

 

Methodology

  • For each test we captured data from XtremIO, perfmon, ETW and extended events.
  • The databases ranged in size from 250GB to 1TB.
  • The data loads ranged in size from 4GB – 75GB
  • The disk space savings were measured using XtremIO show-clusters command
  • For each query the SQL Server transaction log and the buffer pool were cleared prior to running the test using the command CHECKPOINT and DBCC DROPCLEANBUFFERS
  • Tested on:
  • SQL Server 2014 Enterprise Edition CU4
  • XtremIO Version: 3.0.0 build 44

 

 

Using SQL Server and Windows event tracing a repeatable pattern observed during testing was the workload IO characteristics

  • With fragmented indexes SQL Server generated large numbers of small I/O requests
  • With defragmented indexes SQL Server generated small numbers of large sized I/O.

2 (1)Thanks to sub millisecond latency XtremIO performed equally well servicing both I/O types

 

 

 

3

Conclusion: Testing showed that SQL server on an XtremIO array may require less index rebuilds increasing the availability of the array for the end user. Even for heavily fragmented indexes testing demonstrated XtremIO’s unique capabilities of sub-millisecond latency combined with inline compression and deduplication resulted in none of the performance or capacity penalties which are normally associated with fragmentation in SQL Server databases. Before implementing an expensive index maintenance plan on an XtremIO array it is worth taking the time to understand if or when the criteria for your workload requires it.

Advertisements

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