Page Compression

Question

Page Compression

Steve Jones - SSC Editor

SSC Guru

Points: 736297
More actions
June 11, 2011 at 9:28 pm

#73806

Comments posted to this topic are about the item Page Compression
June 13, 2011 at 1:38 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Great question, with subtle differences between the answers 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

M&M SSC-Insane Points: 21700 More actions · Answer 2

Koen Verbeeck (6/12/2011)
Great question, with subtle differences between the answers 🙂

I agree with you, it is a little tough to get this answer right 🙂

M&M

paul s-306273 SSChampion Points: 10730 More actions · Answer 3

I thought I had the correct answer, but the subtlety of the question caught me out.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Excellent question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Good question.

But one major niggle and one minor niggle:

Major niggle: if page compression is enabled on a table already containing data, each page is rebuilt and evaluated for page compression; page compression can occur here (unless I've got it wrong) and may make the data fit into fewer pages than if only row compression occurred. So the answer always applies to pages which are empty/unallocated when compression is enabled, but not always to pages which already contain data when compression is enabled.

Minor niggle: page compression won't occur even when a new row doesn't fit if the engine determines that page compression would not free up a significant amount of space.

edit: SCTEs

Tom

ashjoe7 SSC Rookie Points: 36 More actions · Answer 6

This question is little tricky i.e When compression is enabled on server then only

page compression can occur .if page compression is not enabled and if page is full and a new row is added then compression may not worked .

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 7

Tom.Thomson (6/13/2011)
Good question.
But one major niggle and one minor niggle:
Major niggle: if page compression is enabled on a table already containing data, each page is rebuilt and evaluated for page compression; page compression can occur here (unless I've got it wrong) and may make the data fit into fewer pages than if only row compression occurred. So the answer always applies to pages which are empty/unallocated when compression is enabled, but not always to pages which already contain data when compression is enabled.
Minor niggle: page compression won't occur even when a new row doesn't fit if the engine determines that page compression would not free up a significant amount of space.
edit: SCTEs

Fair points. I've added a note that this is after the initial page compression setting.

Peter Trast SSCarpal Tunnel Points: 4392 More actions · Answer 8

Missed it! But learned some important detail.

Great question!

Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems

Rafael Krisller Right there with Babe Points: 737 More actions · Answer 9

Nice question!

See Data Compression: Strategy, Capacity Planning and Best Practices in ( http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx )

Cliff Jones SSChampion Points: 10547 More actions · Answer 10

Cliff Jones

SSChampion

Points: 10547

June 13, 2011 at 12:36 pm

#1337980

Good question and great topic, thanks.

WayneS SSC Guru Points: 95460 More actions · Answer 11

Steve, this is a really good question. Thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

M&M SSC-Insane Points: 21700 More actions · Answer 12

Tom.Thomson (6/13/2011)
Good question.
But one major niggle and one minor niggle:
Major niggle: if page compression is enabled on a table already containing data, each page is rebuilt and evaluated for page compression; page compression can occur here (unless I've got it wrong) and may make the data fit into fewer pages than if only row compression occurred. So the answer always applies to pages which are empty/unallocated when compression is enabled, but not always to pages which already contain data when compression is enabled.
Minor niggle: page compression won't occur even when a new row doesn't fit if the engine determines that page compression would not free up a significant amount of space.
edit: SCTEs

Thanks Tom for adding these points, very useful really.

M&M