Flipping Bits

Perfcollect video series

Perfcollect is really easy to use, but generates a bunch of interesting data which can be bewildering to a new user of the tool.  So I’ve started a little video series on the topic, which perhaps will blossom into a series on Windows performance analysis and triage in general.

I’ve got three up now, all linked off the perfcollect page.  The videos are hosted on EMC’s Everything Microsoft at EMC site – you don’t need to have a login to watch the videos.

Right now, here's what's up there:

• Running Perfcollect
• Troubleshooting Perfcollect
• Evaluating Perfcollect data
• Using Perfcollect with the PAL tool

 

Enterprise Flash Drives: not just performance

I often encounter the misconception that EFDs are not beneficial unless you need to either reduce latencies below what traditional disks can get you, or you’re short-stroking your disk in order to maintain performance.  So I figured I’d go through the three general use cases I talk about with EFDs:

1. Do more stuff
2. Do the same stuff faster
3. Do the same stuff, but with less gear

These are not mutually exclusive.  In most cases, EFDs allow people to do more stuff, faster, with less gear.  But your goals for EFDs will certainly flavor how to best deploy them.

Do more stuff (increase scale)

Let’s use an entirely contrived order processing system (like a trading desk). Let’s say this system can support 1,000 trades a minute. But during peak trading times, you’re getting more trades than you can process.

The business case for EFDs here would be that you can increase revenue by processing more orders.  Here’s an example where six EFDs supported seven times the transactions of six traditional fibre channel drives.  And this is a perfect example of how increased scale and reduced latency are not mutually exclusive – the response times on the EFD drives were 7 times lower than the response times on the spinning disks.

source

Note that both of the cases thus far really depend on how much your EFDs cost, and how much productivity improvement you’re going to see from their deployment.  That’s significantly different than this one:

Do the same stuff faster (reduce latency):

Let’s take a large manual order-entry system, where user wait time for a query is 5 seconds, and users do about a one query per minute. Let’s say the performance gate in this scenario is storage and it’s getting about 5-7 ms latency (about as good as you can get with a performance HDD at scale due to rotational latency).

The business case for EFDs here would be that employees in this role spend about 8% of their time waiting on the database. If you can reduce that to 1.6%, you realize massive productivity improvements.

Here’s some data: Note the graphs are not on the same scale.

source

Do the same stuff, but with less gear (decrease footprint): 

Let’s say that you’ve got an application that’s fat and happy residing on ninety 10k performance HDDs.  You’re not short-stroking them too badly, but it’s still taking about $6,000 a year to power them, $2,000 a year to cool them, about 18U of rack space to store them, not to mention the maintenance cost associated with them.  But the fact is that in most environments, only part of the data set is actually frequently accessed.  An example of this might be an order processing or inventory system that may go back years or even decades.  The old data isn’t accessed very frequently at all, while the newer data is getting hammered.

Using either manual or automated tiering, you put the older data on cheaper, denser, and more power efficient nl-SAS drives, while the most frequently accessed data can reside on faster, less dense (but still more power efficient) EFDs.

Here’s some supporting data.  Now, the performance was slightly increased, but the bulk of the savings came in the form of footprint – acquisition, power, management, and so forth.  Had the goal been to increased scale or reduce response time, then the deployment method would have differed.

  

source

Three SQL Server 2012 Game Changers you might not know about

SQL 2012 has a lot of changes for storage – naturally people focus on Availability Groups and all the flexibility that they bring in terms of workload isolation, high availability, and disaster recovery.  Here are a couple of nuggets you might not know about:

• Support for SMB/CIFS shares:  For at least a decade (if not longer) Oracle DBAs running in *nix environments have enjoyed the possibility of accessing their data files over NFS.  It allows a lot of flexibility, opens new possibilities for disaster recovery, and greatly simplifies storage provisioning, maintenance and expansion.  Now SQL admins have access to the same storage technique as their Oracle counterparts.  There are some considerations – particularly for those who choose to achieve high availability with shared-storage clusters.  The current version of SMB doesn’t support transparent failover, but that will be fixed with the new version of SMB (arriving with Windows Server 8).
• Placement of tempdb on local disk in clustered environments:  This is probably not a big deal for folks who don’t do geographically dispersed clustering.  But in past versions of SQL server, tempdb had to be placed on a clustered disk on which the SQL resource group was dependent.  In geographically dispersed environments, this meant that tempdb had to be replicated (something that’s not necessary if you’re just replicating the data and not trying to stretch the cluster).  There have always been methods to achieve it, but the methods required going outside the context of SQL Server.  Reduced bandwidth utilization and faster failover FTW!
• Multi-subnet clustering:  Microsoft made a lot of improvements in their clustering model with Windows 2008.  One of those improvements allowed administrators to have nodes in a cluster reside in separate subnets.  Unfortunately, SQL Server 2008 didn’t support it, but SQL Server 2012 does.

Max Worker Threads in upgraded SQL instances

The default Max Worker Threads setting is 255 in SQL 2000, and 0 in SQL 2005 and later.  It’s not actually zero, but setting it to zero allows SQL to determine the setting based on the server configuration (whether it’s 32 or 64 bit, and number of processors).

Microsoft is pretty clear that the setting should only be changed under their guidance:

“If you configure a number of worker threads to a value that is greater than the default, it is almost always counterproductive and slows performance because of scheduling and resource overhead. Only increase this setting under very unusual circumstances and when rigorous methodical testing demonstrates that it is useful to do so.”  (Emphasis theirs.  Link)

So I was surprised when a customer found it set to 255 on their SQL08 instance, and asked our advice on changing it.  They said nobody had changed it from the default.

It turns out that when you upgrade an instance, the setting is inherited from previous version.

So if you’ve got an ancient install that’s been upgraded, it’s worth your time to check it out and change it if necessary.

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.

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!

Finding busy SQL databases and files

Bet you though this blog was all about messaging and Exchange.  Well, it’s not really.  Seriously.  I’m not kidding.  I do think about other stuff.

Ever since I’ve started working with Enterprise Flash Drives (EFD), people have asked me how they can identify which databases are the best candidates for EFD.  The thinking is clear:  EFD’s are great, but they are more expensive than other more conventional Fibre Channel/SAS drives.  So they understandably want to make sure they’re getting the best bang for the buck.

Now, there are some very cool advanced technologies around extending array cache with EFD (like EMC’s FASTCache and NetApp’s FlashCache – then there’s the old tried and true “Use gobs of traditional cache” approach).  There are also some technologies that can automatically tier data for you, (such as EMC’s FAST and Dell’s Data Progression).  Both of these techniques rely on the frequency of use, which makes sense.  Put the data that the server is requesting most frequently on the fastest storage.

This is great Ron Popeil-style “Set it and forget it” storage tiering.  But it’s still prudent to do some analysis to make sure that you’ve got enough of the EFD, and on the flip side you want to make sure that you don’t buy too much.  Now if you’re already an EMC customer with our high end or mid range storage products, we can do some analysis of the data to determine the optimal amount of EFD for your workloads.  But even that won’t take into account specific workloads that you want to accelerate.  And of course if you’re using something that can’t tier for you automatically, then you’re stuck with the manual method anyway.

This post is about looking at the problem from a SQL server perspective.  There are three techniques I use, and they complement each other.  Sometimes I use all three, sometimes only one or two.  All of them use a measure of activity divided by the size of a dataset.  To coin a term, I'll call this "I/O density").  The relative ratio is what you’re looking for to identify the EFD candidates.

• Perfmon data (LogicalDisk)

With this type of analysis, you take the GB used on each logical drive and divide that by the Reads/Sec and Writes/Sec from LogicalDisk.  You might get something like this:

What’s good about this approach is that you get to see data over time, so you can see how spiky the workload is.  You can also evaluate read/write mix and IO size, both of which are factors in the decision.  The drawback is that it doesn’t have the resolution that the other techniques use.  This is something even a rudimentary automated tiering technology could handle for you, namely “This LUN is busy.  Let’s move it up a tier.”  If you have multiple databases per LUN, or multiple files per LUN, you can’t tell which is driving the IO, so you could be moving more data than you need.  Also, you need more than just perfmon data – you need the logical disk sizes.  Perfcollect will capture all the necessary metrics in a nice little package for you.  It’s up to you to extract the data to CSV and analyze it (I used Excel here).

• Perfmon data (SQL Counters)

This involves taking a key counter for your database (<instancename>:Databases\Data File(s) Size (KB)) and dividing it by another counter for the database (<instancename>:Databases\Transactions/sec).  (Perfcollect will grab this data for you as well).  You might get something like this:

Again with this, you get timeseries data.  This case you see a more or less steady green line plugging along all through the sample.  In this case, it’s not a critical data set and I don’t need snappy response times for it, so I’ve already done something automated tiering cannot.  The time series aspect of this is cool, because I can see when people start logging in about 60% of the way through the sample, and I clearly identify the database I’d want to move.  The drawbacks are that (1) it identifies only transactions – not transactions that result in storage IO.  If you’ve got enough server-side cache, many transactions won’t ever hit disk, (2) it doesn’t distinguish between read and write transactions or the size of the resulting IO, both of which are key metrics for evaluating the applicability of EFD, and most importantly (3) it operates at the database level.  If you have multiple files in a user database, in many cases you’ll have one file busier than the others (check out the next method for an example).  Of course you may know which files are busiest (for example, if you’ve partitioned your database with tiering in mind), but this doesn’t tell you if you’re in for a surprise.

• fn_VirtualFileStats

This involves querying fn_virtualfilestats, so you need rights to log into the database.

Here’s the getSQLDBstats script.  Just log into a SQL server with management tools and run it like this:

For a default instance, run:

sqlcmd –S [hostname] -i getDBstats.sql –o [output file.csv]  -s”,”

For a named instance, run

sqlcmd –S [hostname]\[instancename] -i getDBstats.sql –o [outputfile] -s","

You’ll get a file that looks a little like this after you’ve formatted it in Excel.

Now that you’ve got the data, you can do some interesting things with it.  Primarily, you’ll be taking FileSize and dividing it by NumberReads and NumberWrites.  Eliminate the log files and you end up with something like this:

The advantages here are numerous.  First, you have file-level resolution, so if there are busier files in a given database, you can avoid moving the “slacker” files.  In this case, the files to the left are tempdb, which is very evenly distributed (as you'd expect).  The spiky files are user databases, so you can imagine that there's some sort of partitioning in use here, or that they've added files over time.  Second, you can evaluate read/write ratio and if you do some more math with BytesRead and BytesWritten, you can get get an idea of average IO size, both of which are useful data points for evaluating the bang for the buck you’ll get out of EFD.  Third, it’s quick.  Instead of grabbing data over a day, week or month, you can get this data in a matter of seconds, and with an Excel macro, you can have the analysis done just as quickly.  The drawback is that it’s not timeseries data – this is aggregated over time since the moment the database was attached.  So if you have event-based activities like end of day/month batch processing, that could significantly skew the analysis.  So I always like to look at it in conjunction with the perfmon time series data to ensure the analysis is correct.