sql server 2012 adventureworks database download

The answer, as always, is – “It depends.”

Harking back to the previous article, you may wonder if you can now get file system space back. That may mean that your partitions (or the PRIMARY partition if your tables are not partitioned) are now only half-full. Say you’ve gone through the exercise of compressing everything that looks worth the effort. My tables are compressed, so what about the file system space? In such a case, it may be prudent to compress one partition at a time, rather than hogging the system while you compress all partitions at once. Imagine, though, that you have very large tables that are partitioned. For AdventureWorks, all tables are in a single partition, so this is fine, though I could have written “PARTITION = 1” with the same result. For a large table, expect to wait a bit! Also, note that I specified “PARTITION = ALL”. The actual run time of the compression operation is dependent on the size of the tables and indexes, of course. We’ll compress the two candidates we found. Here is a query that will extract a neat overview of compression opportunities: Two handy DMVs will give us this information. What tables and indexes are not compressed? Unless you are severely CPU-bound – in which case you have bigger problems – compression may well enhance your system throughput by reducing I/O. However, you will need less I/O to read and write those tables, since they now occupy (much) less space on disk. Compressed tables use extra CPU cycles to compress/decompress their contents. This will give you vital insight into what to prioritize when you get to work on those space hogs!īefore we get started, let me answer a question you may be asking about performance. In this article, I’ll start off by showing you how you can find out what tables and indexes are not compressed. Modern compression algorithms can reduce the on-disk footprint by 40-60% or even more, depending on the type of data.Ĭompressing your SQL Server data is a simple way to cram more into your limited disk space. The idea is simple: Find repetitive sections of data in a table and store unique information just per row or page or column store index. SQL Server data compression, available in Enterprise versions, is one such approach.

In the meantime, though, you know it will be smart to do all you can to minimize the storage footprint of your database. In the previous article, on tracking database file storage usage How to track SQL Server database space usage with built-in functions and DMVs, I introduced some ideas and calculations for figuring out when your database is going to run out of headroom on the file system – hopefully in time to get an order in the works to increase that space!

Sql server 2012 adventureworks database download