SQL Q&A: Keep your data manageable

  • Article

This month, our SQL Server columnist helps readers parse through differing data, develop strategy and manage data with filegroups.

Paul S. Randal

Numbers don’t lie

Q. I’m trying to measure the I/O latency of some of our databases and I’m getting different results from the Dynamic Management Views (DMVs) and the Performance Monitor counters. Can you explain why these numbers aren’t the same?

A. These two methods use the sys.dm_io_virtual_file_stats DMV (see my blog post for more information about using that), and the Avg. Disk sec/Read and Avg. Disk sec/Write counters in the Physical Disk object in Performance Monitor. It’s entirely possible you might see different results between the two mechanisms.

The DMV measures the cumulative read and write stall time, or latency. It does this for every file in every database since the database was brought online (usually since the last restart of the SQL Server instance). Divide those totals by the number of reads and writes to get the average read and write I/O latency times. These averages are over the entire period of time from when you first queried the DMV back to when that database in question was brought online.

The Performance Monitor counters are a rolling average over a smaller period of time. These two great blog posts, “Windows Performance Monitor Disk Counters Explained,” and “Measuring Disk Latency with Windows Performance Monitor (Perfmon),” by the Windows Server Core team explain in more detail. You get what amounts to an instantaneous view of the disk read and write latencies. As you can see, these two methods of measuring latency are quite different, which can lead to different results.

The DMV only measures the latency of SQL Server database files. The Performance Monitor is measuring all I/Os on that volume. In a shared-storage environment, this could mean lots of other non-SQL Server files contributing to the I/O load on the volume. This could make the average low as far as Performance Monitor is concerned by measuring lots of I/Os across lots of files. The average from the DMV could be higher because it’s measuring fewer I/Os across fewer files.

For the same reasons, there could be poor performance for some time on the volume, but no SQL Server I/Os in process. The Performance Monitor counters would reflect the poor performance. As the DMV is only measuring SQL Server I/Os, the DMV results aren’t affected by that period of poor performance.

Also remember the DMV collects aggregate data. If there’s a period of poor performance with only SQL Server I/Os occurring, the DMV results will be affected by the poorly performing I/Os, even after the performance improves again. The Performance Monitor counters will reflect high latencies during the period of poor performance, then low latencies when performance improves.

As you can see, to make sense of the different values, you need to consider what they’re actually measuring. Unfortunately, there’s no way to reset the DMV counters without momentarily taking the desired database offline.

HA for everyone

Q. I’ve been asked to work on a new high availability (HA) strategy for our SQL Servers. I’m looking for some guidance of what to consider and where to start. Can you offer any advice?

A. One problem is figuring out which HA technology to use when designing your strategy. Often, a company will go with the incumbent technology just because it’s already in place. Worse, the company may make an arbitrary technology choice in the absence of clear requirements.

To design the correct HA strategy, you must gather and analyze your requirements. Without this critical step, you have no hope of satisfying the business requirements with your strategy. For each portion of data to which the HA strategy will apply, you need answers to the following questions:

  1. How important is this data compared to all the rest? Stating that everything is top priority and must be protected equally works with a small amount of data, but becomes increasingly impractical with multiple terabytes spread over multiple SQL Server instances.
  2. How much data can the business afford to lose? Business owners would understandably like to see zero data loss.
  3. How long can data be unavailable? Business owners also like to see zero downtime. Although you can get close, it’s unfortunately not achievable in reality.
  4. Do items No. 1 or No. 2 change at various times of the day or during the weekend? This can have a profound effect on your ability to meet requirements. Zero downtime and data loss are far more achievable for a limited period—say, 9 a.m. to 5 p.m. on weekdays—as compared to 24x365.
  5. Is it acceptable to compromise workload performance to preserve data availability and durability? The only technologies that can provide zero data loss require synchronous mirroring of transaction log records (database mirroring or SQL Server 2012 Availability Groups) or I/O subsystem writes (SAN replication). Both of these can lead to a processing delay, but it’s a trade-off.
  6. Does the secondary copy of the data have to be readable or writeable?

Once you’re faced with these requirements, you can work through the limitations imposed by the business and then compromise. It’s important to realize the limitations you’re working within may mean you can’t meet all of the requirements. In that case, you and the business managers will have to agree to a compromise. Otherwise any HA strategy you design isn’t going to meet expectations. It’s even more likely your solution will be comprised of multiple technologies, so knowing the limitations of each, as well as how they work together, will be critical to your success.

You could face any of the following limitations:

  • Budget
  • Available electrical power
  • Physical space for new servers, racks and air-conditioning equipment
  • Personnel—either there’s no one available to manage the new systems, or no one currently has the skills to implement and manage the new technologies required

There are in-depth descriptions of the technologies and strategic examples in these two white papers:

Although these white papers were written for SQL Server 2008/2008 R2, everything in them still applies. Also check out the white paper, “AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups,” for the latest SQL Server 2012 technologies.

Group up

Q. I’ve been told by some of my colleagues I should be using filegroups for new databases instead of a single data file. Can you explain some of the benefits and drawbacks of doing so?

A. I can’t think of any downsides of using filegroups, except maybe going too far and using hundreds of them. As far as the benefits go, multiple filegroups start to become necessary as your databases become larger (more than 50GB-100GB). Still, there are three main reasons for having them.

Filegroups allow for fast, targeted restores during a disaster. Imagine you have a 1TB database, with most of the space taken up with a sales table that contains data from 2009 through today. If the database is destroyed in a disaster, what’s the smallest amount you can restore? If it’s all in one filegroup there aren’t any options—you must restore the entire 1TB, including all older data.

A better approach for disaster recovery is if you have separate filegroups: primary, 2009, 2010, 2011, 2012 and 2013. In the event of a disaster, you want the 2013 data to be online as fast as possible. That’s what supports your Online Transaction Processing (OLTP) sales system. As long as you have SQL Server Enterprise edition, you can leverage partial database availability and begin this process by restoring the PRIMARY filegroup using the WITH PARTIAL syntax. Then you can restore the other filegroups you want online straightaway. Then you can end the restore sequence.

You can restore the other filegroups online and at your leisure. Now you’re leveraging another Enterprise edition feature called “online piecemeal restore.” This is a combination of features for very large databases (VLDBs). These features reduce downtime and give you a lot of flexibility when it comes to prioritizing your restore sequence during a disaster. It also means you can perform a piecemeal restore if only a portion of the database is damaged for some reason. This further limits downtime requirements in the event of a disaster.

The second reason to use filegroups is to support partitioning and better manageability. Partitioning lets you easily load and delete data from a large table very quickly, without generating a lot of transaction log. Fully explaining the benefits of partitioning is beyond the scope of this column, but the following white papers do a great job, using different example scenarios:

One other manageability feature deals with fragmentation. Using the same sales table example as before, if you have indexes on the sales table that become fragmented and the table and indexes are not partitioned, then the ALTER INDEX … REBUILD or REORGANIZE commands will have to operate on the entire index being defragmented. This is true even though the older data won’t be fragmented. If you’ve split the table into multiple partitions, each in a separate filegroup, you can defragment only the partitions of the indexes that are fragmented. This saves a lot of time and resources.

Last, filegroups let you isolate different workloads within the database onto different portions of the I/O subsystem. For instance, imagine you have some lightly used tables and a few that are heavily used and updated. If everything is in one filegroup, you may find the operational performance on the lightly used tables is compromised because of operations on the heavily updated tables.

In that case, you could separate the lightly used tables into one filegroup on its own portion of the I/O subsystem. Then store each of the heavily used and updated tables in separate filegroups. Have each on its own portion of the I/O subsystem. This segregates the I/O load so the workloads don’t interfere with each other.

You may also have to do this within a single table, if there’s an OLTP workload on the most recent data (for instance, in a sales table, as discussed earlier) and a data warehouse workload on the older data. This is a case where partitioning will be necessary and different workloads will be confined to separate partitions of the table on separate filegroups—again segregating the workloads from each other.

Paul S. Randal

Paul S. Randal is the managing director of SQLskills.com, a Microsoft regional director and a SQL Server MVP. He worked on the SQL Server Storage Engine team at Microsoft from 1999 to 2007. He wrote DBCC CHECKDB/repair for SQL Server 2005 and was responsible for the Core Storage Engine during SQL Server 2008 development. Randal is an expert on disaster recovery, high availability and database maintenance, and is a regular presenter at conferences worldwide. He blogs at SQLskills.com/blogs/paul, and you can find him on Twitter at twitter.com/PaulRandal.

Related Content