Does SQL compression process takes additional temporary disk space?, 1.2 TB database to compress

Question

Does SQL compression process takes additional temporary disk space?, 1.2 TB database to compress

Pablo Campanini-336244

SSC Eights!

Points: 823
More actions
August 27, 2018 at 9:37 am

#415506

Compressing 1.2 TB database. Do I need temporary disk space for compression?.
My largest table is 132 GB and for compressing, does it needs additional 132 GB in Temp DB?, or in the database it self?
Where I can find the detailed process of the compression in SQL Server to see if I need extra disk space?
Appreciate your help.
Thanks
Oscar Pablo Campanini

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Pablo Campanini-336244 - Monday, August 27, 2018 9:37 AM
Compressing 1.2 TB database. Do I need temporary disk space for compression?.
My largest table is 132 GB and for compressing, does it needs additional 132 GB in Temp DB?, or in the database it self?
Where I can find the detailed process of the compression in SQL Server to see if I need extra disk space?
Appreciate your help.
Thanks
Oscar Pablo Campanini

Compressing tables is NOT a panacea of performance. Have you done any performance testing on your larger tables to ensure that you're not actually opening Pandora' Box? Have you checked that doing the compression will actually result in a byte savings that makes it worth it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101099 More actions · Answer 2

I don't think you need extra space. SQL compresses page by page (I'm assuming page compression, as row compression wouldn't save you all that much space). Row compression only would definitely not require additional space.

I've had great performance results from page compression. We have the typical situation where disk I/O is our biggest performance factor.

But be aware: a rebuild takes considerably longer on compressed data, and even reorgs are noticeably slower. Be more selective in when you rebuild/reorg and try just updating statistics instead.

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".

Pablo Campanini-336244 SSC Eights! Points: 823 More actions · Answer 3

OK, I think I did not make myself clear, I know that compressing a table in SQL Server will use less disk space at the end.
But my question is really the compression task itself will require additional space?.
1.- Read data
2.- compress data
3.- write the compressed data

To write the compressed data with less disk space usage, does it deletes the original data after the process? or does it uses a temporary disk space?.

ScottPletcher SSC Guru Points: 101099 More actions · Answer 4

Pablo Campanini-336244 - Monday, August 27, 2018 10:48 AM
OK, I think I did not make myself clear, I know that compressing a table in SQL Server will use less disk space at the end.
But my question is really the compression task itself will require additional space?.
1.- Read data
2.- compress data
3.- write the compressed data
To write the compressed data with less disk space usage, does it deletes the original data after the process? or does it uses a temporary disk space?.

I understood, and, again, no, it does not.

There's no "deleting" of any data. SQL writes the compressed data over the uncompressed data -- it's still the same data page, after all. Db compression does not go across pages.

Good luck with your compression.

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".

frederico_fonseca SSCoach Points: 16155 More actions · Answer 5

Yes the process will need additional space - original index/table + the new one - new one can be build on tempdb or on the destination db itself.
Log file space will also be required.

Advise here is start compressing the smaller tables first - this will free up space for the bigger ones.
If both indexes and tables are on the same file you may wish to drop extra indexes before compressing the table (heap) or clustered index (all your tables should have one except for very specific exception cases) . This way you avoid the need for a lot more extra space on the filegroup.

Once the compression is done the old table/index is removed and the new one takes it place.

ScottPletcher SSC Guru Points: 101099 More actions · Answer 6

Log space will be needed, as always for any mods, but I don't think both tables exist simultaneously unless you're doing an ONLINE rebuild, which you wouldn't do if you were worried about disk space. Also, I'm assuming you're rebuilding using a (T)SQL command and not using the GUI -- I have no idea what overhead the gui might add (or not) to the normal compression process.

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".

Joe Torre SSChampion Points: 10248 More actions · Answer 7

Since the biggest bottle neck (or narrowest I guess) for databases is I/O, for servers with adequate memory you will see an improvement for read especially as more data will fit in memory. Your millage may vary. The best way is to experiment in a development environment with your particular data. For writes to disk there may be added latency depending on your server's specifications.

TomThomson SSC Guru Points: 104773 More actions · Answer 8

ScottPletcher - Monday, August 27, 2018 10:53 AM
There's no "deleting" of any data. SQL writes the compressed data over the uncompressed data -- it's still the same data page, after all. Db compression does not go across pages.

That appears to say that compression will produce no space saving in the immediate term, because each row is still on the same page it was before so there are still the same number of pages. That also ensures that the same number of pages must be read as before, so no reduction of IO. And no reduction in RAM occupancy because because a page still occupies the same space. So compressing to fix a current problem doesn't work unless reorganisation happens too.

Without reorganisation it may mean that future added data will occupy less space than it would have done if the compression hadn't taken place. That will be true even if there are no tables whose clustered index key is neither ever-increasing nor ever-decreasing, but when that is the case there is no space saving for anything page which is neither last in a table with an ever-increasing key nor first in a table with an ever-decreasing key - but I suspect that case is quite rare, there will often be some tables whose clustering keys are neither ever-increasing nor ever decreasing.

Tom

ScottPletcher SSC Guru Points: 101099 More actions · Answer 9

TomThomson - Tuesday, August 28, 2018 10:56 AM
ScottPletcher - Monday, August 27, 2018 10:53 AM
There's no "deleting" of any data. SQL writes the compressed data over the uncompressed data -- it's still the same data page, after all. Db compression does not go across pages.
That appears to say that compression will produce no space saving in the immediate term, because each row is still on the same page it was before so there are still the same number of pages. That also ensures that the same number of pages must be read as before, so no reduction of IO. And no reduction in RAM occupancy because because a page still occupies the same space. So compressing to fix a current problem doesn't work unless reorganisation happens too.
Without reorganisation it may mean that future added data will occupy less space than it would have done if the compression hadn't taken place. That will be true even if there are no tables whose clustered index key is neither ever-increasing nor ever-decreasing, but when that is the case there is no space saving for anything page which is neither last in a table with an ever-increasing key nor first in a table with an ever-decreasing key - but I suspect that case is quite rare, there will often be some tables whose clustering keys are neither ever-increasing nor ever decreasing.

Thanks for pointing that out, since that's not really what I meant to say. For page compression, the total number of pages will be reduced, since pages will fit into less space. What I really meant was, it's an in-place operation, i.e., it does not use separate work space to do the compression, it does it in the actual db pages themselves. Therefore, it never takes additional disk space, only less space. In the rare event that page compression does not significantly reduce space used, SQL does not compress that page.

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".

frederico_fonseca SSCoach Points: 16155 More actions · Answer 10

TomThomson - Tuesday, August 28, 2018 10:56 AM
ScottPletcher - Monday, August 27, 2018 10:53 AM
There's no "deleting" of any data. SQL writes the compressed data over the uncompressed data -- it's still the same data page, after all. Db compression does not go across pages.
That appears to say that compression will produce no space saving in the immediate term, because each row is still on the same page it was before so there are still the same number of pages. That also ensures that the same number of pages must be read as before, so no reduction of IO. And no reduction in RAM occupancy because because a page still occupies the same space. So compressing to fix a current problem doesn't work unless reorganisation happens too.
Without reorganisation it may mean that future added data will occupy less space than it would have done if the compression hadn't taken place. That will be true even if there are no tables whose clustered index key is neither ever-increasing nor ever-decreasing, but when that is the case there is no space saving for anything page which is neither last in a table with an ever-increasing key nor first in a table with an ever-decreasing key - but I suspect that case is quite rare, there will often be some tables whose clustering keys are neither ever-increasing nor ever decreasing.

This is wrong as far as I know.

Page compression will move rows from one page to another as required and space savings will be immediate without the need for a "reorg" - this is done as part of the the index rebuild or table rebuild with compression.

For new records being inserted into a table - if it has a clustered index the data will be compressed on the insert - if it is a heap it works differently.

Compressed indexes (non clustered) also have their own behaviour changes.

https://www.microsoftpressstore.com/articles/article.aspx?p=2225060&seqNum=4 does point how some of the mechanics of this.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

ScottPletcher - Tuesday, August 28, 2018 11:11 AM
TomThomson - Tuesday, August 28, 2018 10:56 AM
ScottPletcher - Monday, August 27, 2018 10:53 AM
There's no "deleting" of any data. SQL writes the compressed data over the uncompressed data -- it's still the same data page, after all. Db compression does not go across pages.
That appears to say that compression will produce no space saving in the immediate term, because each row is still on the same page it was before so there are still the same number of pages. That also ensures that the same number of pages must be read as before, so no reduction of IO. And no reduction in RAM occupancy because because a page still occupies the same space. So compressing to fix a current problem doesn't work unless reorganisation happens too.
Without reorganisation it may mean that future added data will occupy less space than it would have done if the compression hadn't taken place. That will be true even if there are no tables whose clustered index key is neither ever-increasing nor ever-decreasing, but when that is the case there is no space saving for anything page which is neither last in a table with an ever-increasing key nor first in a table with an ever-decreasing key - but I suspect that case is quite rare, there will often be some tables whose clustering keys are neither ever-increasing nor ever decreasing.
Thanks for pointing that out, since that's not really what I meant to say. For page compression, the total number of pages will be reduced, since pages will fit into less space. What I really meant was, it's an in-place operation, i.e., it does not use separate work space to do the compression, it does it in the actual db pages themselves. Therefore, it never takes additional disk space, only less space. In the rare event that page compression does not significantly reduce space used, SQL does not compress that page.

That makes sense - and it's what I thought I remembered happening las time I used compression (more than a decade ago).

Tom

ScottPletcher SSC Guru Points: 101099 More actions · Answer 12

TomThomson - Tuesday, August 28, 2018 1:28 PM
ScottPletcher - Tuesday, August 28, 2018 11:11 AM
TomThomson - Tuesday, August 28, 2018 10:56 AM
ScottPletcher - Monday, August 27, 2018 10:53 AM
There's no "deleting" of any data. SQL writes the compressed data over the uncompressed data -- it's still the same data page, after all. Db compression does not go across pages.
That appears to say that compression will produce no space saving in the immediate term, because each row is still on the same page it was before so there are still the same number of pages. That also ensures that the same number of pages must be read as before, so no reduction of IO. And no reduction in RAM occupancy because because a page still occupies the same space. So compressing to fix a current problem doesn't work unless reorganisation happens too.
Without reorganisation it may mean that future added data will occupy less space than it would have done if the compression hadn't taken place. That will be true even if there are no tables whose clustered index key is neither ever-increasing nor ever-decreasing, but when that is the case there is no space saving for anything page which is neither last in a table with an ever-increasing key nor first in a table with an ever-decreasing key - but I suspect that case is quite rare, there will often be some tables whose clustering keys are neither ever-increasing nor ever decreasing.
Thanks for pointing that out, since that's not really what I meant to say. For page compression, the total number of pages will be reduced, since pages will fit into less space. What I really meant was, it's an in-place operation, i.e., it does not use separate work space to do the compression, it does it in the actual db pages themselves. Therefore, it never takes additional disk space, only less space. In the rare event that page compression does not significantly reduce space used, SQL does not compress that page.
That makes sense - and it's what I thought I remembered happening las[t] time I used compression (more than a decade ago).

Interesting. SQL Server only added it a decade ago, so presumably it was a different rdbms? Naturally I wouldn't imply that any of these methods necessarily apply to any dbms other than SQL Server.

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".

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

Joe Torre - Monday, August 27, 2018 11:48 AM
Your millage may vary. The best way is to experiment in a development environment with your particular data.

Amen to that!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 14

ScottPletcher - Tuesday, August 28, 2018 1:35 PM
TomThomson - Tuesday, August 28, 2018 1:28 PM
That makes sense - and it's what I thought I remembered happening las[t] time I used compression (more than a decade ago).
Interesting. SQL Server only added it a decade ago, so presumably it was a different rdbms? Naturally I wouldn't imply that any of these methods necessarily apply to any dbms other than SQL Server.

I guess 6 August 2008 isn't more that a decade ago if you are rounding to closest years. But a decade plus 22 days actually is more than a decade. I end to think of anything that happened before I retired as being more than a decade ago, although strictly speaking it sometimes isn't (as I didn't actually retire until some time in 2009). Besides, we'd been close enough to MS to be invited to take a place on their patch at a couple of trade shows, so maybe we got our hands on the release (or a non-release prerelease version?) a bit before the official date. I had decided already that we wouldn't upgrade to SQL server 2008 before we moved the company headquarters (including all R&D) from London to Beirut because it was more important to get well into a newer OS version and the new interactive client-side IO structures that were taking shape in Windows (and probably would carry on changing a bit over the next year) but it was clear that we would have to get onto a more modern version of SQL Server soon so I spent a few hours playing with sql server 2008 as soon as it was available in case delaying upgrade was a mistake (it wasnt).

Tom