SQL Rally Q&A: Where is the SQL Engine does Compression Occur

This is another follow up on the Q & A’s that came about from SQL Rally, this question was asked by Jim Murphy (@SQLMurph | Blog).   Jim asked “Where in the SQL Engine does Compression Occur.”  Well Dear Reader the simple answer is in the Access Methods, but let’s dive a little deeper.

THIS IS YOUR BRAIN ON SQL

If you think of the SQL Server Engine as a brain, which has 2 hemispheres right and left, it has 2 parts the Relational Engine (right) and the Storage Engine (left). 

The Relation Engine receives a Query from the end user and it Parses it for syntax, Checks for a Query Plan, Retrieves it or Creates and Caches an execution plan, and finally it begins Executing the instructions to retrieve the Data.

This is where the Storage Engine comes into play.  The Relational Engine says here is what I want, and makes a Grocery List.  The Storage Engine takes that list and goes to the store to get all of the groceries. 

The Storage Engine receives the instructions of the Data that is required by the Relation Engine via the Access Methods.  The Access Methods will ask the Buffer Manager if this information is already in the Buffer Pool as part of the Data Cache.  If the Buffer Pool does not have the data in the Buffer Pool (ACTIVE MEMORY) then it is read off of Disk into the Buffer Pool.

As I mentioned earlier the Part of the Storage Engine that handles Compression is the Access Methods.

SO AMERICA WHY DOES THIS MATTER?

“So Balls,” You Say, “Why is this so important?”

Well Dear Reader it is because when the data is in the Buffer Pool (Active Memory) it is still in a compressed state.  So you are using the memory on your server more efficiently.  Not only is compression saving you space on disk, I/Os being read off of Disk, but the space occupied by the cached data pages is used more efficiently. 

A good example of why this is important is Transparent Data Encryption, TDE.  TDE is manage by the Buffer Pool.  That means that the data is unencrypted when it is read off of the disk and into the Buffer Pool Data Cache.  Because Page & Row Compression are managed by the Access Methods the Data is still compressed in Memory.

Another good example is when using backwards compatibility.  I've blogged about being able to use TDE on a SQL 2008 R2 Database using compatibility level 80 as well as a SQL 2008 R2 Database using Page & Row Compression using compatibility level 80.  The reason is that backwards compatibility changes the rules used by the Optimizer in the Relational Engine.

 The Storage Engine is not affected by compatibility level 80.  So knowing how SQL operates under the covers will show you how you should expect it to act, and help you know how to use the product to it's fullest extent.

Want to learn a little more about SQL Server Internals?

A great book on that is Professional SQL 2008 Internals and Troubleshooting, it is a wonderful read and there are more SQL top names on this book than you can shake a stick at.  I recommend having this book in your library.  It will help you on your way to discover more about how SQL works Internally, I know it helped me!

Thanks,

Brad