SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSION / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 20:02:28 GMT20RE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspx[quote][b]GilaMonster (3/6/2013)[/b][hr][quote][b]SQLCharger (3/6/2013)[/b][hr]My tests have shown a performance degradation when the same queries run on compressed tables compared to uncompressed.I know, there are fewer pages to trawl across, but it is more expensive to crack them open as well - I've found the net effect to be 10 - 20% worse.Especially with page compression, even if you have 1/3 of the pages (typical for many data tables) I've found it to still be quicker when having compression off. Looks like the access speed of each page goes down by a bit more than the size reduction.If there are exceptions to this I'd love to hear them, so I can fine-tune the algorithms.:cool:[/quote]That depends entirely on the workload and on the type of compression. Also you have the effect of reducing memory footprint thereby reducing IO load (more can fit into memory), reducing the CPU impact of moving data around, etc.Page compression's overhead can be unacceptable, row compression often isn't, when there's an overhead at all.Take a look at the whitepaper on compression, the performance results are at the bottom.[url]http://msdn.microsoft.com/en-us/library/dd894051.aspx[/url]I've seen other benchmarks where even OLTP apps got performance improvements from compressing at row or page level.Pretty much it's a case of 'test it and see', unless you're running a vendor app where that testing's been done.[/quote]Gail,Thomas Kejser has just posted some relevant metrics on his excellent blog:[url]http://blog.kejser.org/2013/03/11/quantifying-the-cost-of-compression/[/url]Mon, 11 Mar 2013 05:59:49 GMTSQLChargerRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxYep, 'test it and see' indeed.If you have lots of half-empty Char(100) columns, I guess row-compression may speed things up even comparing memory access speeds.My tests were using an efficient design where 'every byte counts', so compression would have a harder time to prove its worth.Once we move the transactional system to 2008, I'll test that one for compression gains (it will still fit in memory uncompressed, but the schema is inefficient so maybe compression will be worth it).SQL Server matters are rarely cut'n'dry eh...Wed, 06 Mar 2013 07:37:05 GMTSQLChargerRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxGail beat me to that one. Oh well.Wed, 06 Mar 2013 07:23:49 GMTLynn PettisRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxHere is one link: [b][url]http://msdn.microsoft.com/en-us/library/dd894051(v=SQL.100).aspx[/url][/b].Wed, 06 Mar 2013 07:22:13 GMTLynn PettisRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspx[quote][b]SQLCharger (3/6/2013)[/b][hr]My tests have shown a performance degradation when the same queries run on compressed tables compared to uncompressed.I know, there are fewer pages to trawl across, but it is more expensive to crack them open as well - I've found the net effect to be 10 - 20% worse.Especially with page compression, even if you have 1/3 of the pages (typical for many data tables) I've found it to still be quicker when having compression off. Looks like the access speed of each page goes down by a bit more than the size reduction.If there are exceptions to this I'd love to hear them, so I can fine-tune the algorithms.:cool:[/quote]That depends entirely on the workload and on the type of compression. Also you have the effect of reducing memory footprint thereby reducing IO load (more can fit into memory), reducing the CPU impact of moving data around, etc.Page compression's overhead can be unacceptable, row compression often isn't, when there's an overhead at all.Take a look at the whitepaper on compression, the performance results are at the bottom.[url]http://msdn.microsoft.com/en-us/library/dd894051.aspx[/url]I've seen other benchmarks where even OLTP apps got performance improvements from compressing at row or page level.Pretty much it's a case of 'test it and see', unless you're running a vendor app where that testing's been done.Wed, 06 Mar 2013 07:13:33 GMTGilaMonsterRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspx[quote][b]GilaMonster (3/6/2013)[/b][hr][quote][b]SQLCharger (3/6/2013)[/b][hr]If it is normally in memory anyway, you may decide to leave it uncompressed.[/quote]Why?[/quote]Gail,My tests have shown a performance degradation when the same queries run on compressed tables compared to uncompressed.I know, there are fewer pages to trawl across, but it is more expensive to crack them open as well - I've found the net effect to be negative (10-20% give or take).Especially with page compression, even if you have 1/3 of the pages (typical for many data tables) I've found it to still be quicker when having compression off. Looks like the access speed of each page goes down by a bit more than the size reduction.If there are exceptions to this I'd love to hear them, so I can fine-tune the algorithms.:cool:Wed, 06 Mar 2013 07:04:40 GMTSQLChargerRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspx[quote][b]Steve Malley (3/6/2013)[/b][hr]I should have mentioned in my initial post that I was speaking of a Primary Key *Clustered* constraint. This creates a corresponding index.[/quote]And that index is the data at the leaf level, it isn't a separate index like nonclustered indexes.Wed, 06 Mar 2013 06:56:59 GMTLynn PettisRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxI should have mentioned in my initial post that I was speaking of a Primary Key *Clustered* constraint. This creates a corresponding index.Wed, 06 Mar 2013 06:37:51 GMTSteve MalleyRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspx[quote][b]SQLCharger (3/6/2013)[/b][hr]If it is normally in memory anyway, you may decide to leave it uncompressed.[/quote]Why?Wed, 06 Mar 2013 06:15:16 GMTGilaMonsterRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxIt is also worth understanding how this table is used and if it normally resides in memory or not.If it is routinely scanned, then you may choose accept lower savings figures from page compression (say 50% instead of 80%)If it is routinely used for key lookups, you may choose to err on the side of row compressionIf it is normally in memory anyway, you may decide to leave it uncompressed.If the only way to fit it in memory is through compression, you may evaluate otherwise.Same with volatility - if 10% of the table is written every day, compression is less attractive compared to it being read-only.Test with representative load and then you'll know what works best for you;-)Wed, 06 Mar 2013 05:27:50 GMTSQLChargerRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxEstimate space gain with sp_estimate_data_compression_savings procedure:exec sp_estimate_data_compression_savings 'myschema', 'mytable', 1, NULL, 'none'exec sp_estimate_data_compression_savings 'myschema', 'mytable', 1, NULL, 'row'exec sp_estimate_data_compression_savings 'myschema', 'mytable', 1, NULL, 'page'With compression = none you check savings because of defragmentation.Replay your average load and see how much CPU usage will increase.Each time compresses row or page is accessed (e.g. for reading) it must be decomressed using CPU.Wed, 06 Mar 2013 03:04:23 GMTVedran KesegicRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxMicrosoft. When you create a primary key constraint, if a clustered index does not exist on the table SQL Server defaults the primary key to a clustered index.Not always what one may want for the clustered index on a table.Tue, 05 Mar 2013 18:45:52 GMTLynn PettisRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxThanks Vedran and Gail for pointing out that a primary key is not the equivalent of a clustered index.Where does this confusion come from?Tue, 05 Mar 2013 18:33:40 GMTarnipeturssonRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspx[quote][b]Steve Malley (3/5/2013)[/b][hr]Can someone explain how data compression works at the index level? If I compress a primary key, am I compressing the whole table [since the data pages are leaf pages]?[/quote]Depends whether the primary key is clustered or not. There's no requirement that it is.If you compress the clustered index, you are compressing the table, if you compress a nonclustered index, you're compressing just the index.Have a look for the performance benchmarks microsoft did (cat team I think). They got performance gains in all but one case, even when compressing active, in-use portions of the database. Less data to be moved around, less memory required resulted in improved performance.I'll see if I can find the link in the morning.Tue, 05 Mar 2013 16:40:05 GMTGilaMonsterRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxThanks for your observations.Tue, 05 Mar 2013 15:16:22 GMTSteve MalleyRE: If I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxThere is row-level compression and page-level compression (stronger and slower). If you compress clustered index, in fact you compress the table. PK does not have to be the same as clustered key. You should estimate compression gain first to see what you will get in terms of space. Also, be aware that compressing active objects/indexes/partitions is not recommended unless you really have a lot of unused cpu resources. Usually, one compresses old partitions that are queried almost never.Tue, 05 Mar 2013 15:11:26 GMTVedran KesegicIf I compress a primary key, am I compressing the whole table --&gt; P-key with DATA_COMPRESSIONhttp://www.sqlservercentral.com/Forums/Topic1427049-391-1.aspxCan someone explain how data compression works at the index level? If I compress a primary key, am I compressing the whole table [since the data pages are leaf pages]?Tue, 05 Mar 2013 14:56:40 GMTSteve Malley