Visualizing Data Compression: See it for Yourself

In my previous post on compression, “#EntryLevel : Compression & Data Types“, I demonstrated how to get a feel for compression effectiveness based on data types and repeating values. I’ll be providing some examples that expand on the information in that post, so you may want to review it, if you haven’t already. In this post, I’m going to explain how you can see the impact of compression on specific values in your data. This isn’t something you’ll necessarily ever have to do, but it’s helpful for a deeper understanding of how compression works. Visualizing data compression allows you to see how SQL Server is storing the data.

When I say “Visualizing Data Compression”, I mean viewing the page data through DBCC PAGE. While it’s not necessary to understand the details of DBCC PAGE to apply data compression, it’s a great thing to know about. Paul Randal’s posts, “Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back” and “Inside the Storage Engine: Anatomy of a page” provide fantastic information for the curious.

DBCC IND

To use DBCC PAGE, you’ll need to get a PagePID to look at a specific data page. For example, I can get a list of PagePIDs for the Person.Person table in my AdvetureWorks2014PE database by executing the following:

DBCC IND ('AdventureWorks2014PE', 'Person.Person', 0);

PagePID values returned by DBCC IND command

DBCC PAGE

Now, I can run DBCC PAGE to explore what’s on the page with ID 1472, knowing that will correspond to a page of data from Person.Person.

--Refer to aforementioned DBCC links for parameter option details
DBCC TRACEON (3604); --Output results to messages/console
GO 
DBCC PAGE ('AdventureWorks2014PE', 1, 1472, 3); --1 is the file number, 3 the output option
GO

This returns a large text result containing the contents of that page, as shown below.

Beginning of DBCC PAGE Results

sys.fn_PhysLocCracker

You may have noticed that DBCC IND provided an index of data pages for the table, but didn’t hint at the values stored on those pages. When visualizing data compression, we want to look at specific values. The sys.fn_PhysLocCracker function returns the page_id information for all of the rows or specific rows you want to see.

SELECT * FROM Person.Person
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%);

This adds the file_id, page_id, and slot_id to the end of the results.

Physical Location Information Provided by sys.fn_PhysLocCracker

Now, let’s pull this together to view the contents of a page with no compression, row compression, and page compression applied.

No Compression

First, I’ll execute a query to find the page storing “Melissa Price”.

SELECT page_id, slot_id FROM Person.Person
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE LastName = 'Price'
AND FirstName = 'Melissa';

This returns page_id 194193 and slot_id 3, so I execute the following to view the page contents.

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('AdventureWorks2014PE', 1, 194193, 3); 
GO

Then, I’ll go to the slot holding the record I’m after and view its contents. I’ve cut some of the unnecessary details from the output and highlighted the columns we’ll focus on for the remaining examples of this data. BusinessEntityID is an int, PersonType is nchar(2), and FirstName is nvarchar(50). Notice that those last two are capable of storing Unicode characters and take up twice the space of char and varchar.

Next, I’ll apply Row and Page compression, and follow the same steps to look at this data, again, cutting out much of the excess text.

Row Compression

Notice that row compression was able to store those three values more effectively.

Page Compression

Page compression uses additional steps to reduce redundancy (again, see my prior post for more details). It’s not able to save additional space on the int value, however, the PersonType and FirstName have repeating values on the page, so it’s able to store those once, and not have to store them in the individual slots.

In the page header, I can see some of those values stored under “CompressionInfo Raw Bytes”.

For SentryOne users, I recommend applying compression to the repository database. I have a series of posts explaining how and where to do that. Previously, I noted that the IX_Unique1 index on EventSourceHistoryDetail compresses rather well. I saw an 80% savings in the SentryOne database I used.

Page Compression for EventSourceHistoryDetail

The data types for the three columns in that index are uniqueidentifier, bigint, and nvarchar(128).

EventSourceHistoryDetail Index Data Types

The EventSourceID and RemoteObjectID columns contain relatively few unique values, and while RemoteSequenceID contains unique values, they are bigints (which means the value 1 eats up 8 bytes of space). Let’s look at those three values across the SQL Server Data Compression spectrum.

No Compression

Row Compression

Row compression isn’t able to do anything with the EventSourceID, but it’s able to optimize the storage for the RemoteSequenceID and RemoteObjectID.

Page Compression

Page compression saves an impressive amount of space with this data because it’s able to store the EventSourceID and RemoteObjectID once on this page instead of for each row with those same values. These particular columns had a combined size of 96; now it’s 1.

As I mentioned, you don’t need to know about DBCC PAGE in order to apply compression to a SQL Server database. You can, however, use it to see how specific data is being compressed for a deeper understanding of data compression. If you’re not sure about the savings you’re seeing, you can use it to verify what SQL Server is doing at the page level. Visualizing data compression provided a sanity check for me at times. Having explored various data types at different compression levels, I feel like I just “get it” more now that I’ve seen it.

If you’re just starting out with compression or haven’t looked into it because it was an Enterprise Edition feature, you should know that it’s available beyond Enterprise Edition in SQL Server 2016 SP 1. Now is the perfect time to master data compression!

Melissa Connors

Melissa is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.