I was just at SQLRally in Dallas, and I was speaking to a DBA friend of mine over lunch. He made the comment that his manager thought he spent too much time at work taking care of the organization’s data. He told me that he spent so much time at his job because he wanted to ensure that the data was properly cared for. Now that is what I call an exceptional DBA. Here is a DBA that thinks protecting the organization’s data is more important than does his manager. And on top of this, this particular DBA donates a lot of his free time to the SQL Serve community. When I asked why he spent so much time helping out with the community, he said it was to help pay back for all of the help he had received from the SQL Server community over the years. What a DBA!

It is exceptional DBAs like this who need to be recognized by their peers, and they can by entering the 2012 Exceptional DBA of the Year Awards. These awards are hosted by SQLServerCentral.com, and sponsored by Red Gate Software.

If you think you are an exceptional DBA, you can nominate yourself for the award. Or if you know of an exceptional DBA, you can nominate them. Either way, to be recognized as an exceptional DBA, you must enter.

Once you are nominated (by yourself or another), your application will be reviewed by four judges: Steve Jones, Grant Fritchey, Rodney Landrum, and Brad McGehee. The judges will then select five finalists. Then these five finalists will be voted on by the SQL Server community, and the judges, to select the 2012 Exceptional DBA of the Year Award.

The award includes:

• An award plaque
• Being featured on the Simple-Talk website
• Full conference registration for the 2012 PASS Summit
• Four nights hotel accommodations
• $300 towards travel expenses
• A license for the SQL DBA Bundle with 36 months of support and upgrades.

For more information on the award, and to nominate yourself or another, check out www.exceptionaldba.com.

Microsoft SQL Server 2008 Internals by Kalen Delaney, Paul Randal, Kimberly Tripp, Conor Cunningham, and Adam Machanic.

SQL Server MVP Deep Dives, edited by Paul Nielsen, Kalen Delaney, Adam Machanic, and Kimberly Tripp.

SQL Server MVP Deep Dives: Volume 2, edited by Kalen Delaney, Louis Davidson, Greg Low, Paul Randal, Kimberly Tripp, and Brad McGehee.

Professional SQL Server 2008 Internals and Troubleshooting, by Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Jonathan Kehayias, Cindy Gross, and Steven Wort.

Troubleshooting SQL Server—A Guide for the Accidental DBA, by Jonathan Kehayias and Ted Krueger. Available as a free PDF file here.

Inside Microsoft SQL Server 2008: T-SQL Querying, by Itzik Ben-Gan, Lubor Kollar, Dejan Sarka, and Steve Kass.

DBA Survivor: Become a Rock Star DBA, by Thomas LaRock.

Inside the SQL Server Query Optimizer, by Benjamin Nevarez. Available as a free PDF file here.

SQL Server 2005 Practical Troubleshooting, by Ken Henderson.

SQL Server 2008 Administration in Action, by Rod Colledge.

SQL Server Hardware, by Glenn Berry. Available as a free PDF file here.

The above books were the top picks, but don’t include all of the recommendations made during the April Question of the Month. To read about all of the recommended books, click here.

Post your responses to the SQL Server Question of the Month in the comments section below (at www.bradmcgehee.com if you are viewing this from a syndicated newsfeed). And don’t forget to enter your e-mail address when you post your response, so I can contact you if you win.

Because there is no right or wrong answer, this month’s winner will be selected randomly from all the entries that are received.

This is a reprint from my editorial in Database Weekly. Also, check out the May Question of the Month, as it is also directly related to this topic.

Often when I speak at user groups and conferences, I ask attendees what versions of SQL Server they are running. As you may expect, I hear from attendees who are running the entire gamut of SQL Server versions, from SQL Server 6.5 up to SQL Server 2012. While it is an unscientific poll, it seems that most people are still running SQL Server 2005 and SQL Server 2008.

SQL Server 2012 RTM was announced on March 6 and went into general availability on April 2, but I have talked only to a very small handful of DBAs who are even thinking about upgrading to 2012, let alone having already done so. So why doesn’t everybody immediately upgrade to the latest edition of SQL Server when it is released? Why are there so many older versions of SQL Server in the wild?

Some of the most common reasons I hear include:

• We don’t like being on the cutting edge, preferring only to move to a newer version only once it has been around awhile and proven to be stable. Read: Waiting for Service Pack 1.
• Our vendor applications, or in-house applications, haven’t been certified to run on SQL Server 2012, and this takes a lot of time. Based on my personal experience, vendor applications are extremely slow to keep up to date with the latest SQL Server technology. When I ask vendors why, they generally tell me that they don’t have the in-house resources to do the testing. In other words, they are cheap and don’t want to spend the money.
• If our application is working well on the current version, why go to all the hassle of upgrading when we are happy with what we have. Why stir the pot and cause potential problems?
• The new version doesn’t have any new features that we need, so why bother upgrading?
• Upgrading to a new version almost always means higher licensing costs, and with the economy as it is, we can’t afford to upgrade.
• While we want to upgrade, we don’t have the in-house resources to do so. It takes a huge amount of time to test existing applications (which are often tightly intertwined), and, if there are problems, to fix them; build new servers (as necessary); train staff on how to use new features; perform the actual upgrade; perform post upgrade tasks; monitor performance after upgrading to see if it is better or worse, and make any required adjustments. In other words, the process of upgrading is very time-consuming and costly.

So I have several questions for you. Have you upgraded any of your instances to SQL Server 2012 yet, and if so, how did the upgrade go? If you haven’t upgraded to SQL Server 2012 yet, when do you expect to? And besides what I have listed above, what other reasons or impediments have you run across that prevent you from upgrading to 2012?

The SQL Server 2012 and SharePoint Connections conference agenda brings  together more than 40 top-ranking experts from Microsoft, along with leading independent presenters to deliver in-depth sessions on how to succeed with SharePoint and SQL Server based on real life production deployments with the latest hot news from Microsoft’s biggest launch this year for SQL Server 2012.

The event will include a SQLServerCentral.com track consisting of six sessions from two SQL Server MVPs that I selected to participate in the track: Constantin Klein and Satya Jayanty. Their sessions include:

Constantin Klein

• New T-SQL Features in SQL Server 2012
• SQL Server for Developers
• Fun Managing Documents with SQL Server

Satya Jayanty

• Upgrading SQL Server: Tools and Best Practices
• DMVs – Best of the Bunch. Troubleshoot Your Data Platform Like a Pro
• SQL Server High Availability Concepts and Solution Guidance: 2008 R2 & 2012

Post your responses to the SQL Server Question of the Month in the comments section below (at www.bradmcgehee.com if you are viewing this from a syndicated newsfeed). And don’t forget to enter your e-mail address when you post your response, so I can contact you if you win.

Because there is no right or wrong answer, this month’s winner will be selected randomly from all the entries that are received.

Red Gate Software has announced “SQL in the City: London,” to be held on Friday, July 13, 2012, at the One Wimpole Street Conference Center, London England. This one day, free event includes five different sessions in each of three different tracks:  Database Administration Track, Developer Track, and the Red Gate Track. Sessions will not only cover general SQL Server topics, but attendees will also have the opportunity to attend Red Gate tool specific sessions in order to learn more about how to become more productive DBAs.

Some of the speakers speakers slated to attend include Brad M McGehee, Steve Jones, Grant Fritchey, Dave Ballantyne, Noemi Moreno, Dennis Lloyd Junior, among many others.

All SQL Server DBAs and Developers are welcome to attend this free event, but registration is limited, so be sure to sign up soon if you are planning to attend.

To find out more about this event, and how to register, visit www.sqlinthecity.com.

While we can’t stop growth, SQL Server 2008 and later, gives us some tools to help us better deal with all this data, and that is the promise of compression. Given the right circumstances, DBAs can use data compression (Enterprise Edition only, or alternatively take a look at Red Gate SQL Storage Compress) to reduce the size of our MDFS, and backup compression can help us reduce the amount of space our backups take. Not only does compression reduce physical file sizes, it reduces disk I/O, which can greatly enhance the performance of many database applications, along with database backups.

When we discuss SQL Server compression, we need to think of it two different ways. First, there is data compression, which includes row-level and page-level compression that occurs within the MDF files of our databases. Second, there is backup compression, which occurs only when data is backed up. While both of these are forms of compression, they are architected differently. Because of this, it is important to treat them separately. In this blog post, I will mainly focus on data compression.

Data Compression comes in two different forms:

• Row-level Data Compression: Row-level data compression is essentially turning fixed length data types into variable length data types, freeing up empty space. It also has the ability to ignore zero and null values, saving additional space. In turn, more rows can fit into a single data page.
• Page-level Data Compression: Page-level data compression starts with row-level data compression, then adds two additional compression features: prefix and dictionary compression. We will take a look at what this means a little later in this chapter. As you can imagine, page-level compression offers increased data compression over row-level compression alone.

Backup Compression comes in a single form:

• Backup Compression: Backup compression does not use row-level or page-level data compression. Instead, backup compression occurs only at the time of a backup, and it uses its own proprietary compression technique. Backup compression can be used when using, or not using, data compression, although using backup compression on a database that is already compressed using data compression may not offer additional benefits.

In the next section, we will take a high-level overview of data compression, and then we will drill down into the detail of the different types of compression available with SQL Server 2008 and later.

Data Compression Overview

Data compression has been around for years. For example, who hasn’t zipped a file at some point in their career? While compression isn’t a new technology, it was new to SQL Server 2008. Unlike zip compression, SQL Server’s data compression does not automatically compress an entire database; instead, data compression can only be used for these database objects:

• A table stored as a heap
• A table stored as a clustered index
• A non-clustered index
• An indexed view
• Partitioned tables and indexes

In other words, as the DBA, you must evaluate each of the above objects in your database, decide if you want to compress it, then decide whether you want to compress it using either row-level or page level compression. Once you have completed this evaluation, then you must turn on compression for that object. There is no single switch you can flip that will turn compression on or off for all the objects listed above, although you could write a Transact-SQL script to accomplish this task.

Fortunately, other than turning compression on or off for the above objects, you don’t have to do anything else to enable compression. You don’t have to re-architect your database or your application, as data compression is entirely handled under the covers by the SQL Server Storage Engine. When data is passed to the Storage Engine, it is compressed and stored in the designated compressed format (on disk and in the Buffer Cache). When the Storage Engine passes the information to another component of SQL Server, then the Storage Engine has to uncompress it. In other words, every time data has to be passed to or from the Storage Engine, it has to be compressed or uncompressed. While this does take extra CPU overhead to accomplish, in many cases, the amount of disk I/O saved by compression more than makes up for the CPU costs, boosting the overall performance of SQL Server.

Here’s a simplified example. Let’s say that we want to update a row in a table, and that the row we want to update is currently stored on disk in a table that is using row-level data compression. When we execute the UPDATE statement, the Relational Engine (Query Processor) parses, compiles, and optimizes the UPDATE statement, ready to execute it. Before the statement can be executed, the Relational Engine needs the row of data that is currently stored on disk in the compressed format, so the Relational Engine requests the data by asking the Storage Engine to go get it. The Storage Engine (with the help of the SQLOS) goes and gets the compressed data from disk and brings it into the Data Cache, where the data continues to remain in its compressed format.

Once the data is in the Data Cache, the row is handed off to the Relational Engine from the Storage Engine. During this pass off, the compressed row is uncompressed and given to the Relational Engine to UPDATE. Once the row has been updated, it is then passed back to the Storage Engine, where is it again compressed and stored in the Data Cache. At some point, the row will be flushed to disk, where it is stored on disk in its compressed format.

Data compression offers many benefits. Besides the obvious one of reducing the amount of physical disk space required to store data—and the disk I/O needed to write and read it—it also reduces the amount of Data Cache memory needed to store data in the Data Cache. This in turn allows more data to be stored in the Data Cache, reducing the need for SQL Server to access the disk to get data, as the data is now more likely to be in memory than disk, further reducing disk I/O.

Just as data compression offers benefits, so it has some disadvantages. Using compression uses up additional CPU cycles. If your server has plenty to spare, then you have no problem. But if your server is already experiencing a CPU bottleneck, then perhaps compression is better left turned off.

Row‐Level Data Compression

The simplest method of data compression, row-level compression, works by:

• Reducing the amount of metadata used to store a row.
• Storing fixed length numeric data types as if they were variable-length data types. For example, if you store the value 1 in a bigint data type, storage will only take 1 byte, not 8 bytes, which the bigint data types normally takes.
• Storing CHAR data types as variable-length data types. For example, if you have a CHAR (100) data type, and only store 10 characters in it, blank characters are not stored, thus reducing the space needed to the store data.
• Not storing NULL or 0 values

Row-level data compression offers less compression than page-level data compression, but it also incurs less overhead, reducing the amount of CPU resources required to implement it.

Page Level Data Compression

Page-level data compression offers greater compression, but at the expense of greater CPU utilization. It works using these techniques:

• It starts out by using row-level data compression to get as many rows as it can on a single page.
• Next, prefix compression is run. Essentially, repeating patterns of data at the beginning of the values of a given column are removed and substituted with an abbreviated reference that is stored in the compression information (CI) structure that immediately follows the page header of a data page.
• And last, dictionary compression is used. Dictionary compression searches for repeated values anywhere on a page and stores them in the CI. One of the major differences between prefix and dictionary compression is that prefix compression is restricted to one column, while dictionary compression works anywhere on a data page.

The amount of compression provided by page-level data compression is highly dependent on the data stored in a table or index. If a lot of the data repeats itself, then compression is more efficient. If the data is more random, then little benefits can be gained using page-level compression.

Data Compression Demo

Data compression can be performed using either SQL Server Management Studio (SSMS) or by using Transact-SQL. For this demo, we will take a look at how you can compress a table that uses a clustered index, using SSMS.

Let’s say that we want to compress the Sales.Customer table (which has a clustered index) in the AdventureWorks database. The first step is to right-click on the table in SSMS, select “Storage,” and then select “Manage Compression.”

Figure 1: SSMS can be used to manage compression.

This brings up the Data Compression Wizard, displayed below.

Figure 2: The Data Compression Wizard, or Transact-SQL commands, can be used to manage data compression.

After clicking “Next,” the wizard displays the following screen, which allows you not only to select the compression type, but it also allows you to calculate how much space you will save once compression has been turned on.

Figure 3: Use this screen to select the compression type, and to calculate how much space will be saved.

First, let’s see how much space we will save if we use row-level compression on this table. To find out, click on the drop-down box below “Compression Type,” select “Row,” and then click “Calculate.”

Figure 4: For this table, row-level compression doesn’t offer much compression.

After clicking “Calculate,” the wizard runs and calculates how much space is currently being used, and how much space would be used after row-level compression. As we can see, very little space will be saved, about 1.6%.

Now, let’s see how much compression savings page-level compression offers us for this particular table. Again, I go to the drop-down menu under “Compression Type,” select “Page,” then press “Calculate.”

Figure 5: Page-level compression is higher than row-level compression.

After pressing “Calculate,” we see that compression has improved greatly, now saving about 20% space. At this point, if you should decide to turn on page-level compression for this table, click on the “Next” button.

Figure 6: The wizard allows you several options in which to turn on compression.

At the above screen, you can choose to perform the compression now (not a good idea during busy production times because the initial compression process can be very CPU and disk I/O intensive), schedule it to run later, or just to script the Transact-SQL code so you can run it at your convenience.

Once you have compressed this table (a clustered index), keep in mind that any non-clustered indexes that this table may have are not automatically compressed for you. Remember, compression is based on a per object basis. If you want to compress the non-clustered indexes for this table, you will have to compress each one, one at a time.

While this wizard helps you to see how much compression either method offers, it does not suggest which compression method should be used, nor does it recommend whether compression should be used at all for this object. As the DBA, it will be your job to evaluate each compressible object to determine if it should have compression enabled or not. In other words, you must decide if the benefits of compression outweigh the negatives.

Summary

In this article, we have learned about the two forms of data compression, and about backup compression. While data compression might seem like a seductive new feature of SQL Server, I highly recommend that it is only used by experienced DBAs. While it offers lots of promise for increased performance, it can just as easily cause performance problems if misused.

This post was updated on 3-13-2012. The original posting can be found here.