Voice of the DBA

Tag: sql server

  • Data Compression in SQL Server

    Do you know much about data compression? It seems that quite a few people I meet don’t really understand how it works, or what the benefits are. I heard about compression first hand from Sunil Agarwal a few years back at TechEd. I visited one of his sessions and then he was gracious enough to spend about 15-20 minutes with me afterwards answering more questions about additional details. At the time, I was just another schmuck in the audience, but Sunil was very pleasant and patient with me.

    I was lucky that day,but Sunil has a video where he explains some of the ways in which compression works for Microsoft customers.

    http://channel9.msdn.com/posts/SQL-Server-Data-Compression/player?w=512&h=288

    The summary is that data compression works in a few ways. One is, obviously, to save space. The second, however, is that fewer I/Os’s are required to move data onor off a disk, which can result in a performance increase. The SQL Server team tried to strike a balance in how they implemented compression to allow for both of these benefits. The tradeoff is that CPU is needed to compress or decompress data, so while more compression is possible, it can negatively impact performance.

    Compression is a cool feature, and consists of a few parts. You can read more about each of them if you are interested:

    The DBA has complete control over what is compressed, but it is also some work to analyze the space savings and then make a determination about whether or not it is worth you compressing your table. If the space savings is too small, you might end up negating any storage gains with additional CPU costs.

    How Do I Decide?

    There is a stored procedure (sp_estimate_data_compression_savings) that can be used on tables or indexes to help you determine what the savings are. Numerous scripts are available, like this one from Paul Neilsen – Whole Database – Data Compression Procs.

    There’s also one easy decision. Only Enterprise and Data Center editions of SQL Server support this feature, so if you aren’t running those, you don’t have to worry about this.

    Alternatives

    There are a couple other alternatives to implementing data compression.  If you want to get compression savings in other editions of SQL Server, my company, Red Gate Software, makes a product called SQL Storage Compress, which implements compression at the file level.

    This is based on Hyperbac technology and is completely transparent to SQL Server. We are working on some extensive case studies now and this is part of the SQL Server I/O Reliability Program, but this can help performance on your system by reducing I/O.

    If space is an issue in test, customer service, and development environments, you can use Virtual Restore to mount a backup file as a database that you can read, or write to. The writes occur in a sparse file, so your backup file is untouched. However you get a quicker “restore” that is transparent to SQL Server.

    As I mentioned, I work for Red Gate software, so take these as mentions of products my company builds and sells. I’ve heard great things about them, and use them in a few places for work at SQLServerCentral. I’d recommend you try them and see if they are a fit in your environment and have a good price/value ratio for your company.

  • Archiving

    This actually looks like an easy way to archive data

    Not many database systems get designed with a well thought out archiving plan at the beginning of their lifecycle. In most cases that’s fine since few databases seem to gather enough data to require archiving, and if there is extra time early in the life cycle of an application, it’s probably better spent tuning queries for efficiency anyway.

    However when archiving is implemented, it can’t be done so in a cumbersome way. That almost always leads to some sort of data hoarding by individuals, which often leads to an application being built on Excel rather than SQL Server. This article talks about the problem of data access from a storage point of view, but it could easily apply to databases. If we require some special function, or intervention by the DBA to allow access to archived data, it’s quite possible that users will take matters into their own hands.

    As SQL Server has grown, we have ended up with a number of features that should make the archival of data much easier. Partitioned views and tables in many cases can allow an application to function with understanding that the underlying data is stored in multiple locations, and potentially even multiple systems. It does take a little more administrative work to seamlessly implement these features, but it’s a skill that DBAs should be able to make work smoothly.

    The problem of large data sets will continue to grow over time, and it’s a problem I suspect most DBAs will face at some point in their career. Take some time to learn about partitioning and how you can both manage data more efficiently over time, as well as improve performance if you find access patterns vary for different sets of data.

    It’s also a good reason to ensure that your applications don’t ever expect to pull back an entire table for any reason. Working with a few rows of data at a time is prudent when you have thousands of rows and essential when you have billions.

    Steve Jones

    The Voice of the DBA Podcasts

  • BCPs – The Disaster Recovery Plan

    I came up with a short acronym for my Preparation for Disaster presentation: BCPs. It describes what I think you need to do in order to be prepared for issues with your database server. The initials stand for:

    • Backups
    • Checks
    • Practice and Preparation
    • Script and Schedule

    With a utility in SQL Server going by BCP, this ought to be easy to remember. I’ll describe what I mean below by each of these:

    Backups

    You need to make sure that you have backups. They are insurance for the issues that will befall you at some point. They are also required for restores, which do matter.

    You need a backup plan that meets the need for the particular system you are protecting. Not your entire infrastructure, but what’s appropriate for each system. Once size does not fit all here.

    Checks

    You can’t prevent corruption and it can occur at any time. The best you can do is detect it early, and fix it as soon as you can. Make sure you have checks in place that look for corruption.

    You also want checks for things that are out of the ordinary. Make sure you have some monitoring/base-lining set up so that you can proactively be aware of what a disaster might occur.

    Practice and Preparation

    All of the backups, checks, etc. in the world are useless if you don’t know how to apply them. Or if you don’t have the keys, passwords, account numbers, etc. Practice the skills you need to recover your systems, and make sure that you are prepared to do so in a variety of situations.

    Explaining to your CEO that you don’t have the keys for that TDE encrypted restore will make for a very, very bad day.

    Script and Schedule

    You must automate most of your job to be successful and efficient, and you ought to have lots of your DR insurance (backups/scripts) scheduled so that it get performed in a timely manner, and a regular manner. Humans forget things, and make mistakes constantly. You need to ensure that you are scripting your tasks (to speed up processes) and scheduling them (to ensure they occur regularly).

  • What’s a Disaster?

    In working on my Preparation for Disaster presentation and doing research, one of the questions that came up early for me was “what is a disaster?” After all, if you can’t answer that question, how do you prepare for it.

    I’ve got a list of stuff here, which ones of these qualify as disasters?

    • Hard Drive crash
    • Hurricane wrecks data center
    • Fire in the server room
    • Corruption in a clustered index
    • DBA deletes a table by running DELETE without a WHERE clause
    • User clicks submit on a web page and the database crashed before they get the acknowledgement
    • Admin uses Access to update and lock a table, and forgets to save their work before leaving for lunch
    • DBA deploys code to production instead of development by accident.

    The answer is all of these are disasters. Most people seem to plan for the first 3, and forget to plan for the rest.

    Anything that interrupts your system’s ability to serve clients and get work done is a disaster. It’s worth keeping that in mind and planning for all sorts of potential problems. It doesn’t mean you have to be ultra paranoid, but you ought to have thought about the various potential problems and have some ideas about how to deal with them.