One of the many benefits of working in a test environment is that you can do something like that right away to look for some of the negative side effects and make note of things that might require more attention later on in the testing and fine-tuning phase.įor those unfamiliar with compression, you must make a choice between row compression and page compression when you apply it. I am not advocating the blind application of compression in such a manner, I was exploring in a test environment at this point to satisfy some curiosity. That roughly equated applying compression to 7% of the indexes in the database.
When I first started exploring compression in a test SQL Sentry database, I applied it to the top 50 largest indexes by row count to get some numbers for overhead. I will also highlight a couple of benefits outside of saving disk space. I will show you an example of that in the SQL Sentry database as we continue. When considering data compression, these two thoughts seem to come to mind almost instantly "this will save disk space" and "this will be a major hit to CPU." Both of those statements can be true, but if done properly you can minimize the impact to CPU, if done improperly, you can actually waste more disk space. It's best to go out on a high note, right? That will be my approach to this series. I suppose I am a "bad news first" kind of person. I wanted to get that reminder out of the way in the beginning so as not to disappoint anyone at the end of all of this. This is still true in SQL Server 2014 and appears to remain that way in SQL Server 2016. The data compression feature is limited to the Enterprise Edition of SQL Server. If you are looking for more in-depth coverage on data compression, I recommend this SQL Server Technical Article from Microsoft titled, " Data Compression: Strategy, Capacity Planning, and Best Practices".
#DATABASE PART 1 HOW TO#
I hope that walking you through various stages of my testing and analysis will also give you some ideas on how to apply data compression to one of your other databases, but that will not be the focus here.
#DATABASE PART 1 SERIES#
While I will go over some of the very basics, this series of posts is primarily concerned with showing you how you can best implement data compression with your SQL Sentry repository database and what to expect for changes in performance and resource consumption. Many helpful and knowledgeable bloggers out there have posted about SQL Server’s data compression feature. One of those first projects involved analyzing the usage of the data compression feature in SQL Server with the SQL Sentry database.
I started working on Special Projects (or SpecialOps as I say when I am trying to impress my friends) here at SQL Sentry in April. Hello, reader! I am Melissa you may know me from PASS events as the person running the Maine chapter (where you were surprised to learn about non-lobster related careers existing in Maine) or have stumbled across me on Twitter over the years as which is clearly my stage name ("All the world's a stage", after all). Before I jump into this blog post, which is my first post at SQL Sentry, I would like to take a brief moment to introduce myself.