Flipping Bits

SQL Server Logical Disk Layout

A few weeks ago, I was talking to a meetup of SQL Server folks here in the Hartford area about storage and SQL Server. We were going through the ideal storage layout of SQL server, and someone in the group summarized it as "so I need a minimum of five disks (LUNs) for a SQL Server?" I'd never really thought of it in those terms, so let's get down to the thinking:

First, this has mostly to do with logical layout of data. Aside from separating logs and databases on different physical media, it has nothing to do with physical layout of data. Even if you have only a couple of disks to allocate to the server, you can still logically partition it so that it's easy to evaluate performance later on.

As with almost everything else related to SQL Server, it's going to depend on the workload. The key aspects are:

• Performance sensitivity - This is not necessarily a performance intensive workload – it could be nearly idle. The key question is "if performance of this application suffers, which business processes will suffer, and how many users will suffer?" If there's a chance that someone important is going to call you up and ask you to fix a performance problem with this SQL server, it's performance sensitive. Even if you're resource constrained, it would help to have things laid out so you can evaluate performance without reconfiguring the database (which in reality includes nearly as much effort as migrating to entirely new storage).
• Recoverability – This would be dictated by whether you'll need to be able to perform up to the minute recovery of the database. If you do, you'll need to consider the physical layout of the data so that the failure of a physical disk or RAID group doesn't take out the database and its transaction logs at the same time.

Here's a quick version of the rules:

1. If your database is neither performance sensitive and you do not need the ability to recover data up to the last transaction, then you only need one or two:
   1. OS/Apps
   2. Databases and transaction logs
2. If the data in your database is critical enough that you'll want to be able to recover up to the latest transaction, you'll need three.
   1. OS
   2. Databases
   3. Transaction logs (you also need to make sure that these are physically separated from the databases, so that you don't lose data from both of them at the same time).
3. If your data is performance sensitive (regardless of whether the data is sensitive), you need a minimum of five, and possibly more:
   1. OS
   2. System databases (other than tempdb)
   3. User databases
   4. User database transaction logs
   5. One for tempdb and its logs

The principle behind this separation is the performance evaluation of these components independently of each other. If I have my user databases mixed with tempdb, and I'm having performance issues, I have no real way of telling which database is presenting the IO, and which database is starving. All I know is that performance stinks equally on both databases. More importantly for transaction logs, you want to both avoid contention between the IO that log flushes create and normal user IO, and you also want to make sure that transaction log disks get a write response time of well less than 10 milliseconds.

These five LUNs are a starting point – I often see systems with a dozen or even a couple dozen LUNs. What's the reason for adding LUNs above this five?

• Additional segregation of the unrelated workloads. I can put two different databases on two different LUNs.
• Segregation of related, but different workloads. For example, I could put my non-clustered indexes on different disk than my data files.
• Simply adding queues – each disk gets an additional queue
• Increase the granularity of restore options if you're using hardware-based snapshots, clones, or CDP. In this case, the management boundary is the LUN itself, so if you want to do rapid restore of a failed or corrupt data file using this method, then you would restore all the databases on that LUN together (whether they are corrupt or not). There are ways to do selective restore in this case, but it can take longer to perform the restore.

Remember that multiple LUNs can actually share the same physical disks, so your workloads can still step on each other if that's the case. However, it makes the troubleshooting process much easier. And if you're using the right technology, fixing the problems can be completely seamless.

Hartford SQL Server Meetup!

Chuck Boyce has created a SQL Server meetup.  He spun up the Philadelphia SSUG, and I’m hoping he can weave his magic for Hartford!

Our first event will be on January 24th at the EMC office in Rocky Hill at 7pm.  I’ll be presenting on storage basics for DBAs, including techniques to analyze and improve storage performance and availability, various techniques for disaster recovery and application failover.  We’ll have a few giveaways and some food.

Make sure to RSVP here at meetup if you’d like to attend so we can make sure you get information around how get there and into the building.  We look forward to seeing you there!

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.

Oracle StatsPack/AWR Data Collection

In the interest of keeping our performance data collection procedures in one place for both Oracle and Windows, Bill Gaynor, Saty Desai and I have created a set of pages outlining the procedures for generating Oracle Statspack and AWR reports.

You can check them out here.

Geographically Dispersed Clustering with Windows

Have you ever wanted to know how you can automate site to site failover with Windows failover clustering, but only have 3 minutes to spend?

Here's a video just for you:

 

Best Practices for Windows Mounts Points

Mount points – they’re understandably popular.  And although they’ve been around for quite a while, some people have questions around their implementation.  Yes, they’re really easy to set up, but you should follow some guidelines so you can take leverage advanced technologies down the road.

For the uninitiated, here’s an quick overview of mount points:

Traditionally, a windows volume (disk drive, LUN, etc) had to be mounted to a drive letter for Windows to access it.  This results in a limitation of 24 hard drives that can be mounted to a system.  To overcome this limitation, Microsoft introduced the ability to mount a drive on an empty directory within any NTFS filesystem.  You can try it yourself next time you format a USB stick.  It’s quite neat – now you can have a virtually unlimited number of drives attached to your system.  You know, like UNIX.  .  It also makes it easier to find stuff.

Here’s an example:  Let’s say I have a database with 2 data files, and 1 transaction log, and I want to keep them on separate drives for recovery and performance purposes.  Instead of mounting them to letters g:\, h:\ and i:\, I can do this:

• g:\dbname\dbfile01
• g:\dbname\dbfile02
• g:\dbname\tlog

Where dbfile01, dbfile02, and tlog are all empty directories on which a drive is mounted (mount point).  The directory structure is clear to anyone who looks at it, and if I need to add more drives to my database I just create a directory in dbname, and put a drive on it.  I try not to put the mount points on my system drive (c:\), although I’m allowed to.  The reason is that the mount point looks just like a directory – you have to know that it’s a mount point.  When I have it on another drive letter, it’s clear that there’s another drive there. 

So what’s a nested mount point?  As the name implies, it’s where you have a mount point within a mount point.  In our case, the dbname directory could be a mount point.

Are nested mount points categorically a bad idea?  Absolutely not – in fact it’s a pretty common practice.  The key is that there shouldn’t be unique data higher in the directory structure than a mount point.  For example, this is a bad idea:

• g:\dbname\dbfile
• g:\dbname\dbfile\tlog

Where both dbfile and tlog are both mount points.  First, it wouldn’t occur to another DBA that tlogs is actually a different drive.  It also has implications for backup and recovery if you’re trying to leverage a volume-based backup and recovery system.  The reason for this becomes clear when you start playing through a recovery scenario.  Let’s say I want to restore my database, but keep my transaction log around for replay.  VSS and the SQL Virtual Device Interface (VDI) backup and restore at the disk level rather than the file level.  So I’ve completely replaced not just g:\dbname\dbfile, but also g:\dbname\dbfile\tlog, where the transaction log lives.

I haven’t necessarily lost any data – I can just go find the tlog drive, mount it up, and continue my recovery.  But it clearly poses problems if I want to automate the process.

So this causes all sorts of confusion around whether nested mount points are supported.  In the case of Replication Manager, the answer is yes, nested mount points are supported, as long the volumes in the application set are not nested within each other.  To give examples, this is supported:

• g:\dbname\dbfile
• g:\dbname\tlog

Where dbname, dbfile, and tlog are all mount points, but only dbfile and tlog are part of the application set being protected.

On the other hand, this is not supported:

• g:\dbname\dbfile
• g:\dbname\dbfile\tlog

Where dbfile and tlog are mount points and both part of the application set being protected.

If you have any lessons learned about mount points you think are worth sharing, post a comment below!

Perfmon Counter of the Month–Disk Queue Length

OK, so maybe Perfmon Counter of the Week was a little optimistic.  Let’s say month, OK?

This one is another disk-related counter, but I like it because there’s so much myth around it.  It seems anytime I see a document around disk performance as it related to an application, I see references to “disk queue length”.  And it is either really vague, like “monitor the disk queue length – if it’s high, you may have problems with the disk subsystem,” or it’s really specific like “if your disk queue length is consistently higher than 2, you may have a problem with your disk”.

And neither piece of advice is quite wrong, nor is it quite right.

Here’s the thing with disk queue length, and any other queue-oriented counter:  Whether a queue length is “bad” or “good” depends on the number of resources servicing the queue. 

Imagine you’re in line at the grocery store.  There’s a single cashier, and there are nine people in front of you.  That’s a queue length of ten, and you’ll start wondering whether you really need the quinoa your wife told you to get.  Now imagine that there’s a single line with nine people in front of you, but there are 5 cashiers.  You still have a queue length of ten, but you’re going to be through it in a lot less time.  In fact, it’s better than having one person in front of you if there’s a single cashier, because that dude in front of you might have 63 coupons and a checkbook. If there are other cashiers, he’s only dominating one of multiple resources.

Unfortunately, that doesn’t make the quinoa look any more appetizing.

So does that mean that disk queue length is useless?  Not at all.  If you know how many resources there are servicing the LUN, then you can see whether it’s really a problem or not.  Typically the rule of thumb is 2 per disk.  So a queue length of 6 may indicate a bottleneck if you only have 2 disks in the RAID group servicing the LUN.  But it’s really not a problem if you have a 10-disk R1/0 RAID group.  The same goes for processor queue length.

The other aspect in which it is interesting is trending.  I’m a huge fan of measuring performance stats when application performance is good so that you have something to compare it to when things go downhill.  So it’s useful as a comparative value as well.  Even if you don’t have performance data from the “good times”, you can still visualize it with a time-series chart.

I/O Density and Exchange: A short history

Turns out that some people are dubious that Exchange 2010 and 7.2k drives are a good fit.   So I’m sometimes asked to justify spending less on storage for Exchange (a seemingly odd, but ultimately honest position to take).

Up until a short while ago, I just trotted out Microsoft’s graphs regarding the IO per “heavy use” mailbox they see in production.  It shows a 10x decrease in IO per mailbox between Exchange 2003 and Exchange 2010.

This can be unconvincing to some of the folks who fought Exchange 5.5, 2000, and 2003 in the trenches, where it was common knowledge that not only did you put Exchange on the fastest storage available, you often had to allocate more disks than you needed for capacity to meet the performance demands of the application.  A 10x reduction in IO workload doesn’t convincingly take you from “put it on the fastest storage you can afford” to “put it on the slowest storage you can buy”.

The story is actually two-fold – at the same time that IO per mailbox was being driven down, the size of the mailboxes were being driven up.  Since storage sizing is and always has been a balance between performance (how quickly you need to move data on and off the drives) and capacity (how much data you need to store on the drive), IO density (or IO per GB) is far more relevant than IO per mailbox.  In short, we don’t just look at how many IOs Exchange is driving per mailbox, we have to factor in how big those mailboxes are as well.  Typical mailboxes were only 50MB-100MB during the Exchange 2003 days.  Nowadays they’re between 10 and 100 times that.  When we do factor in mailbox sizes, it turns out that IO density isn’t 10 times lower with Exchange 2010, it’s orders of magnitude lower in most modern Exchange configurations.

I’ve come up with a graph to illustrate this notion. 

So while we saw “only” a 10x decrease in the IO per mailbox, we saw over a 400x decrease in IO per gigabyte.

Now let’s take a look at the IO density that spinning disks have offered throughout the years.

Clearly these are simple examples – the mailbox IO was far more variable in the 32-bit versions of Exchange, and the IO/GB for the disk drives doesn’t include RAID overheads, there’s no free space factored in and so forth.  But when we interpolate the IO/GB driven by mailboxes and the IO/GB provided by disks, it matches up pretty well with the typical disk topologies I recommend:

In the Exchange 5.5 to 2003 days, every IO counted, and you had allocate more disks to the Exchange workload than you needed for capacity.  Things got a lot better in Exchange 2007, where you could place your average mailboxes on either smaller 10k FC drives or larger 15k FC drives.  With Exchange 2010 and large mailboxes, the sweet spot for the typical deployment is somewhere between 1TB and 2TB drives.

Now this isn’t to say that 2TB or 1TB drives are appropriate for all Exchange deployments.  A large number of factors go into sizing storage for Exchange.  But if you’ve done your homework and factored in all the variables, and it looks like the slower drives are the way to go, then you should trust your sizing and save your organization some money.

Perfmon Counter of the Week: Logins/Sec

For the second in the series, I’m going to take a look at Logins/Sec.  You get this from SQLServer:General Statistics\Logins\sec.  It has impact on the user experience, as it takes time to set up a connection, and also has impact on the CPU consumption, as documented in Linchi Shea’s excellent blog post on the topic.  It’s important to remember that the stats reported by logins/sec do not include pooled connections

As you might imagine, there’s a direct correlation between logins/sec and transactions hitting the Master database as shown in the graph below.  I scaled them differently to show correlation, because it turns out they overlap otherwise (one login equals one transaction to Master).

You can find some great information on how to tell if connections are pooled or not in James Rowland-Jones blog post here.

There are a number of resources out there to help you figure out and solve issues around connection pooling.    Mohammed Mawla has a good post about the subject, but I think the best succinct advice is from Microsoft:

"To make the best use of connection pooling, explicitly close database connections as soon as possible. By default, a connection terminates after your script finishes execution. However, by explicitly closing a connection in your script after it is no longer needed, you reduce demand on the database server and make the connection available to other users."