SQL Storage Compress

Performance Testing for SQL Storage Compress

The demands on IT are growing constantly. As more and more technology comes online, the amount of information being processed is growing right alongside. This means more and more disk drives have to be put to work managing this ever increasing information load. But purchasing more disks isn't always possible. Sometimes your hardware is maxed out with the number of controllers you can put into it. Cabinets can be filled with drives. You may simply not have the budget to add more disks. What can you do then?

SQL Storage Compress is exactly what you need in such situations. It can save you vast amounts of space on your existing hardware. You may be asking yourself "just how much space can I save", but more likely, "just what is the impact on my system?" That's a fair question. You can read any number of testimonials, or look at certifications, but the real test is on your machines.

This guide will walk you through the processes of testing your systems. We'll use a Microsoft provided I/O stress utility called SQLIO, available online for free. This tool measures the capacity of your disk drives, and nothing more, but we can use it to measure your disk drives with SQL Storage Compress running. Once we've done that, we'll take advantage of another free utility from Microsoft, the RML Utilities (available here, and here for x86 systems). The RML Utilities can take a server-side trace from one system and play it back on another system. You can then record another server-side trace and compare the two, in order to see which query calls, if any, have improved. With these two testing methods, you can compare the performance of your system before and after you install SQL Storage Compress, to tell what impact it might have on your current systems. It's also worth noting that once you're comfortable using these testing mechanisms you can test almost any piece of software or code change with your own SQL Server systems and load. It's a handy skill set to possess.

General I/O System Testing

To compare how your system is behaving before and after installing SQL Storage Compress you should establish a performance baseline. The basic process is as follows:

• Configure your storage system and server
• Install SQLIO
• Start monitoring the system
• Run the SQLIO tests
• Stop monitoring and store the data collected
• Install SQL Storage Compress
• Configure SQL Storage Compress
• Start the monitoring system
• Run the SQLIO tests
• Stop monitoring and store the data collected
• Compare the results

This should provide you with most of what you need to be able to confirm the behavior of SQL Storage Compress on your own systems. There is one caveat. SQLIO will read from the file you designate, so you can get very accurate measures on how reads will be on your system with realistic files. Unfortunately, SQLIO writes empty sets. This means that you will see unrealistic performance on compression. Because of this, while the SQLIO tests are very good, the best tests for your system will be realized by using the playback capabilities of RML.

Baseline Test

Installing SQLIO is well documented by Microsoft, so I won't replicate it here. You should install, configure, and test your machine with SQLIO prior to installing SQL Storage Compress. This way you will be able to compare the performance of a clean system without any added processing load, to the system's performance after the install of SQL Storage Compress. There is a complete set of documentation on SQLIO that I recommend reading through prior to using it.

After installing SQLIO, instead of generating a file as you are told to do in the documentation, copy one of your own database files to the drive that you intend to test. Use that with SQLIO. This will make the reads more realistic for the type of data you have stored on your system.

Once you're ready, here is a sample test run:

This runs the designated database file through a series of tests with 8 threads and 16 threads. The tests are run first for reads and then for writes, so that the reads are from the original file. The tests are run with a variety of block sizes. This is a general test and might need tweaks for your system. Specifically, you will need to select an appropriately sized database file, so that it puts more stress on your disk cache. The batch file for calling this is attached to this article, in the PDF download. Each of these tests runs for 5 minutes, so the entire test takes 100 minutes.

DO NOT RUN THIS AGAINST AN ACTIVE PRODUCTION SYSTEM.

Jonathan Kehayias (blog|twitter) has a PowerShell parsing script that will take the output from SQLIO and put it into an Excel spreadsheet. You should use this to get a clean view of the information collected through SQLIO. It's not attached to the article, but is available for free download here.

The output.txt file will show you the I/O per second and the megabytes per second on the drive system being tested. There also some other measures there, but these are the most important two. But to see how the system behaves, it's important to gather basic system metrics. If you already have a monitoring suite, use that. If not, you can gather performance monitor counters. Here is a suggested list of counters:

• \\Memory\\Available Mbytes
• \\Memory\\Pages/sec
• \\PhysicalDisk(*)\\% Disk Time
• \\PhysicalDisk(*)\\Current Disk Queue Length
• \\PhysicalDisk(*)\\Disk Transfers/sec
• \\PhysicalDisk(*)\\Disk Bytes/sec
• \\Processor(*)\\% Privileged Time
• \\Processor(*)\\% Processor Time
• \\System\\Processor Queue Length
• \\System\\Context Switches/sec

An XML definition file for a Data Collector Set is attached to the article, in the PDF download. This is a minimal set of counters meant to establish a physical baseline on the system. I run them with a 30 second collection interval and get a small, but reasonably complete picture of how the system behaves. You should adjust this list of counters and the collection interval to meet with your own standards.

Once all this is in place and you have the Data Collector Set running, you're ready to start the test. Just execute the batch file from the command line and wait. You'll get a full set of data. Stop the Data Collector Set when the test is complete and run the output.txt files through Jonathan Kehayias' script. You'll now have all the data you need for how your system is currently performing.

If you are only performing an I/O stress test, you can proceed to the next section. If you want to also perform application specific testing on this same system, you should skip down to the Application Specific testing and run the Baseline Test there.

SQL Storage Compress Test

Now install SQL Storage Compress. It's already configured to work with SQLIO, so there's nothing you need to do to configure it. You should configure monitoring using the same counters as before. You will need a compressed database file, which you can create by following the instructions for SQL Storage Compress using either T-SQL or the UI and wizard. Once the file is compressed, you can either detach it from SQL Server and edit your batch to point to it, or you can copy or move the file to the appropriate location and then edit the batch file, because the extension will be .mdfx. Once the appropriate file is in place, run the SQLIO process.

What you should expect to see is, at minimum, the same numbers for I/O and megabytes per second as you saw before, but with somewhat higher CPU queues in evidence, as the storage is being compressed.

Application Specific Testing

The real strength of data storage compression lies in two basic areas, a reduction in storage space needed, and an improvement in speed, as fewer pages are read from and written to the disk. As far as seeing compression in action goes testing the I/OPS using SQLIO simply shows whether performance in and around the disk storage system is affected. It doesn't show how data storage, and retrieval of your data through SQL Server, are affected. That requires a different kind of testing entirely. For this test, you'll need to download and install the RML Utilities (available here, and here for x86 systems). We'll capture a set of events using a server-side trace and then play them back using the RML Utilities. This is the best mechanism for testing the overall performance of your system, your data, and their interaction with SQL Storage Compress.

Setup

In general, you need to have a system where you can perform tests, separate from your production system. It can be a dedicated test machine or a development box, but you'll want to be able to run tests on it multiple times without affecting other systems, especially your normal production environment. The first thing I do is plan on a point in time recovery from my production system or take a COPY ONLY backup of the production system, timed for just before the beginning of the trace.

Set up a server-side trace and collect data for a reasonable period, preferably at the height of your work day. A two hour set of data should be sufficient. For details on how to set up a server-side trace read this article. The specific trace to be run for the RML Utilities is defined in the appendix of the RML Utilities documentation. To save time formatting, I've attached a file with that trace specified. Once you have gathered your data and closed the trace you need to convert the trace into RML files. This is done using the trace read command. Here is an example:

readtrace -I"d:\perf\rm.trc" -o"d:\perf\rm_out" -a

This command disables the load of the data into your database and instead concentrates on creating the RML files for the playback. At this point you are ready to run a playback.

Playback Testing

Playback testing is especially noteworthy because it will give you everything you need to validate how your application performs while running with and without SQL Storage Compress specifically. Just as with SQLIO testing, it's best to run these tests before and after the installation of the Red Gate products. For your 'before' test, you can capture a server-side trace and performance counters, and compare these results to the replay after the install. Set these up ahead of running the replay.

To run the replay, you must make a call to the replay mechanism supplied with the RML utilities. Here is a sample command line.

Ostress.exe -c"d:\perf\control.ini" -mreplay -T88 -i"d:\perf\rm_out\*.rml" -o"d:\perf\output" -S"Server\Instance" -E

This will run as fast as possible on your machine, showing what kind of stress and load your standard app calls can put on the machine if network transit time and user waits are taken out of the equation. There's any number of permutations that you can add to this type of playback, but this is a simple and straightforward mechanism to get a good playback going on most systems.

Once the baseline run is complete, install SQL Storage Compress. You will need to create a compressed database by restoring a backup using SQL Storage Compress. Run the tests a second time, with data collection enabled as before. You should be able to capture and record enough data to compare performance before and after SQL Storage Compress was configured on your system. It's not absolutely necessary to run these tests before installing SQL Storage Compress, but it does make them more complete. You will see exactly what your system looks like without any of our resources running at all, and with all our resources running and your database operating under storage compression.