I was extremely fortunate to be able to present about data compression at the EightKB SQL Server internals conference last week. If you missed my talk in person, you can now view it, as well as the entire day, on YouTube!
I was able to answer many questions during the session, but there were a few still left after time ran out. I wanted to address them all, so here they are!
With row compression, is the overhead in terms of latency noticeable?
I’d argue it’s not noticeable. Back when the feature was new in 2008 it was typically less than 5%. Now 13 years later with all the hardware advancements that have taken place I’d be surprised if it were noticeable at all.
Is it measurable? Sure. Nothing is free in computing, and the work involved in compressing/decompressing is still work, and that will always take more time than doing no work at all.
Wait, you’re running this in a python notebook? Could you or someone please elaborate on this a bit, “offline” in Slack would also be ok. Thank you.
Yep! It’s actually a SQL Notebook, but same idea and based on Jupyter notebooks. They’re one of the awesome features you can find in Azure Data Studio. More info here.
Thomas Keijer mentioned in a SQL Bits conference presentation sometime ago that updates slowed down 4x with page level compression, is that still true? thank you.
I took a look at the SQLBits website and am unable to find the session you’re speaking about, otherwise I would have watched it to see what was said and the context that information was offered in.
That being said, I’m not surprised at all that updates are slower with compression enabled. As I mentioned above, the work of doing compression will always consume more time than doing no compression at all. The question quickly shifts to whether or not that impact makes a noticeable impact on performance.
What about a table with a lot of NULL values, does it have any advantages from row compression?
Absolutely! With row compression, NULL (and zero) values take up no space at all, so there are definitely space savings to be had. The same goes for page compression, because it starts by applying row compression.