Compress the Partitioned OR Partition the Compressed ?

Question

Compress the Partitioned OR Partition the Compressed ?

VoldemarG

Hall of Fame

Points: 3883
More actions
March 29, 2021 at 11:14 pm

#3863419

Does it make sense, for query performance gains, to BOTH apply Data Compress (eg Page Compression) and Partitioning to a SQL Server 2016 table of 100 000 000 row clustered index table?
that is 80% used for reporting/Selects and 20% data updates to it.
IF YES, then is there a difference what to do first and why?
* Compess Partitioned tables?
OR
* Partition the Compressed tables ?

Likes to play Chess

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101120 More actions · Answer 1

In general, data compression is a huge performance gain. Partitioning is not a particular performance if the table has its best possible clustering index, i.e. you lookup by the clus column(s) and they do not cause a lot of page splitting in the table.

If UPDATEs significantly expand rows, you could take a real performance hit there from compression. It's likely still worth it, but you'd want to verify that to be sure.

With that many rows, you'd certainly also want to consider using columnstore.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

Should Columnstore be considered an alternative to compression, since Columnstore is also a compression?

If a comparable/similar performance gain is observed during testing for both Compressed table and same table with Columnstore

then which one should I rather go with?

Likes to play Chess

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

columnstore is an alternative to rowstore with page compression.

Typically you get even better compression with columnstore.

As to which is best, that depends on the specific situation with that table.

I'd say do these, in order:

page compress data as part of determining the best clus index for the table, based on query stats and cardinality. if that clus index is not in place, put it in place. adjust non-clus indexes as required
review the results of that for a while. then try a columnstore if you need further performance gains to see if it gives them to you

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

VoldemarG Hall of Fame Points: 3883 More actions · Answer 4

May be this is a naiive or ignorant question, but

if Columnstore is so good, and Columnstore compression is even a better compression than regular (page or row) Data Compression, then why even bother with Data Compression? Why not be concerned about Columnstore only ? 🙂

Likes to play Chess

ScottPletcher SSC Guru Points: 101120 More actions · Answer 5

Columnstore has its own quirks. For example, every time you SELECT data from the table you'll read roughly 1M rows. If your processing (almost) always does that anyway, such as some type of reporting / data mart task, then that does not cause extra overhead. But if you typically read only, say, 2000 rows per task, then columnstore is extra overhead. Columnstore also has some "quirks" with adding rows and doing updates that make it less efficient than normal under certain conditions.

That's why I said you need to investigate and determine which is better for you.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

VoldemarG Hall of Fame Points: 3883 More actions · Answer 6

Thank you for sharing such a great summary on Columnstore!

Would that be fair to say that BOTH data compression and columnstore indexing is most suited for DW scenarious,

or I should test if, for example, Row type of Compression of all indexes in table or Clustered Columnstore Index will make a difference in our OLTP environment as well?

Likes to play Chess

ScottPletcher SSC Guru Points: 101120 More actions · Answer 7

Yes, both can make a big difference in OLTP as well. The key is that it reduces I/O because fewer pages must be written to disk.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".