Flipping Bits

Please stop using SQLIOSim to model SQL workloads

Every few months I get a (sometimes panicked) call or email about SQLIOSim that goes like this: 

“We’re doing some testing of a new disk array.  SQL Server’s going to be one of the workloads, so we downloaded SQLIOSim and are running it, but the disk latency is terrible.  Can you help?”

Well, the disk latency is terrible because SQLIOSim is designed to test IO stability and functional correctness, not to model a particular workload.  SQLIOSim actually attempts to break the storage (create page corruption or stale reads) so as to expose configuration or hardware problems that can cause data corruption.  It doesn’t really try to create a workload that “looks” like, say, an OLTP or DW workload.

So you can use SQLIOSim to make sure your storage is healthy, but keep in mind:

• It is a pass/fail test.  Either you have stale reads/corrupt pages or you don’t.
• Performance data like queue length and latency are irrelevant during SQLIOSim runs
• You cannot compare the performance results of one SQLIOSim run to another
• Be careful about other workloads on the array while you’re running SQLIOSim

For more discussion on SQLIOSim, there are a bunch of good links referenced here.

You might wonder how you actually see how a new storage environment is going to perform under a particular workload.  There are several options, and I’ll list them in terms of accuracy of the model.

Use SQL Profiler to capture and replay traces

This is by far and away the most accurate way of seeing how a given storage configuration will perform under a production workload.  After all, it is precisely the same workload as production.  You can also use something like Benchmark Factory to modify the workload to anticipate growth and so forth.

Use Perfmon to gather data and Iometer to create the workload

This is somewhat less accurate than traces, but can be easier to set up.  You gather all the information needed to create iometer workloads using perfmon and educated guesses.

• Disk Reads & Disk Writes/sec – use that to figure the r:w ratio
• Data file sizes – use that to figure the working set
• Disk Bytes/Read & Write – use that to figure the IO sizes
• Random vs Sequential – there is no way to determine randomness of the workload from perfmon. Logs will always be 100% sequential, DB files will be a mix. Think about the workload a bit to determine the mix (100% random will usually be safe).

Aside from the educated guesses around randomness and thread counts, there are two other big issues with iometer:  It goes as fast as it can.  There’s no way to configure Iometer to only issue a specific number of IOs/second.  So it will reflect the workload type, but not the workload rate.  The other thing it will not approximate is the skew.  Most database environments will have certain tables or parts of the dataset that are more active than others.  In some cases, over 90% of the IO can be directed at less than 5% of the total data set (low skew).  Consider an order/inventory OLTP database that has decades of data in it – nearly all the IO will be directed at the latest data – this is a low skew environment.  If you believe you have a low skew environment, you can set the file size to what you estimate the total skew to be, and create a fully random worker (but preferably use a trace instead of iometer).

Use the SQLIO Disk Subsystem Benchmark Tool

This is actually my least favorite method.  SQLIO is indeed extremely simply to use, but there is no way to make this look like a production workload.  You choose one workload type – all reads, or all writes.  One IO size.  Random or sequential.  Although it can expose some of the limits around a disk configuration, there is no way to make it look like a “real” SQL workload.

At the end of the day, the only workload generator that will create a workload that looks like yours is SQL Profiler and traces.  And it’s more important than ever to capture and recreate the workload accurately, including such features of the workload such as skew, since they directly impact the effectiveness of modern storage techniques such as extended cache and storage auto-tiering.

Background Database Maintenance in Exchange 2010

Database size is a critical aspect to consider in Exchange 2010 designs where DAG is in play. This is regardless of the disk backend you choose. JBOD, DAS RAID, and SAN designs all need planning where small passive databases are in play. Since Microsoft offers no configurability of BDM on passive databases, there is nothing you can do but be aware of and plan for the workload (aside from going with a standalone configuration).

Many Exchange administrators are used to having small databases (100-200GB) so that ESE maintenance tasks and restore SLAs are easier to address.  I find that this can trip up otherwise solid storage architectures.  BDM can lead to problems, primarily because admins aren’t necessarily aware that BDM schedules that they set via EMC or powershell applies only to the active copy of the database.

Microsoft calls background database maintenance “online database scanning” or “database checksumming” or “background database maintenance”.  It can be associated with page zeroing.  It’s a googlicious challenge – and a tagging nightmare for bloggers. 

Let’s see what Technet says about BDM (aka “checksumming”):

Background database maintenance I/O is sequential database file I/O associated with checksumming both active and passive database copies. Background database maintenance has the following characteristics:

• On active databases, it can be configured to run either 24 × 7 or during the online maintenance window. Background database maintenance (Checksum) runs against passive database copies 24 × 7. For more information, see "Online Database Scanning" in the New Exchange Core Store Functionality topic.
• Reads approximately 5 MB per second for each actively scanning database (both active and passive copies). The I/O is 100 percent sequential, so the storage subsystem can process the I/Os efficiently.
• Stops scanning the database if the checksum pass completes in less than 24 hours.
• Issues a warning event if the scan doesn't complete within three days (not configurable).

Let’s reiterate interesting bit:

Background database maintenance (Checksum) runs against passive database copies 24 × 7.

Now let’s look at what another technet page says about this:

Exchange scans the database no more than once per day. This read I/O is 100 percent sequential (which makes it easy on the disk) and equates to a scanning rate of about 5 megabytes (MB)/sec on most systems.

The important thing to remember is that when you set a maintenance schedule for a database as described on this technet page, the setting applies only to the active copy of the database.

Now, how do you plan for this workload?  Frequently, you don’t have to worry about it at all.  Let’s say you have 5000 users with 2GB mailboxes on a server.  That’s 10 TB of mailbox data.  If you have 2 TB mailbox databases, you have about 5 databases, or about 25 MB/s in BDM workload.  Not a problem.

However, if you limit your databases to 100GB, that can present a problem.  That 5000 users translates to 100 databases, each of which can launch a 5MB/s read workload (or more) at any given time.  Aggregated over a whole single mailbox server, 500MB/s is nothing to sneeze at, especially if it’s mixed with user workload on the same disk. 

What you can do to limit the impact of BDM:

1. Use larger databases (1-2TB in size).  Although it will increase the amount of time required to maintain the databases, remember that with a DAG configuration, it’s often more expedient to reseed than it is to do ESE maintenance. 
2. If you are concerned about restore times, use an array that can act as a VSS provider and store your first-tier backups in snapshots, clones, or CDP bookmarks.  In these scenarios, restore times are largely uniform whether you have a 1TB database or a 100GB database (log replay notwithstanding).
3. If after considering hardware-assisted VSS, you STILL can’t leverage larger databases, confer with your storage vendor to architect for what will turn out to be large block reads at unpredictable rates and unpredictable intervals.  To the best of your ability, isolate the passive from active databases so that BDM doesn’t impact user mailboxes.
4. Ask Microsoft why passive database maintenance is not configurable.  As it is today, a completely passive Exchange server can generate over 500MB/s in read bandwidth.  Let’s be crystal clear about this: 500+ MB/s is data warehouse territory, and it’s absurd to think that this can occur with no active users on it, and no backups running against it.  It makes little sense, especially in configurations where the silent corruption they’re looking for is detected by other techniques.

Now, I have a couple questions I haven’t been able to get an answer to:

1. What happens to the schedule when checksum pass completes in less than 24 hours?  Does it start again in 24 hours?  Is that from the start of the prior run, or from the end of the prior run?  An authoritative answer would be helpful for those who have to plan for this workload.
2. What factors can make the scanning run faster or slower than “most” systems?  Does the system issue a 256KB every XX ms?  Does that depend on the processor clock rate?  Can we thus expect the read rate to increase with clock rate?  The difference between 5-8 MB/s doesn’t really matter on a single database, but it really does matter when you’re talking about hundreds or thousands of databases on a single disk array.