Data compression and the size of the logs

Question

Data compression and the size of the logs

Roust_m

SSCoach

Points: 17410
More actions
November 9, 2010 at 11:54 pm

#226241

Hi,
Just was wondering, if you implement data compression and save some disk space in the data file, would this also make the log files to grow to smaller sizes? Theoretically, as you need to update less pages to do the same amount of changes to the data, you would need less space in the log. I am not sure though if this works this way? Would it also depend on if you use ROW or PAGE compression?
Thanks.

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

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

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 1

That's a good question. It's not that you update less pages, but that the pages are smaller when written to disk. I think that the log is still the same size.

Roust_m SSCoach Points: 17410 More actions · Answer 2

My logic is: with row compression, your varchar(1000) column may turn into char(100), so more records will fit into a single page as the page remains the same 8KB size so less pages would need to be updated for the same number of records. Am I missing anything?

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 3

I don't think that's true. The pages are smaller on disk, but in memory they are still 8kb pages. So it's not fewer pages, it's fewer IO operations that you save.

The log records, AFAIK, are also not compressed.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I don't have any machine to test this out but it's quite easy to test.

Build a new empty db and put 1 mb in the log and 1 mb increment.

Add a table with compression and then add 25mb of compressable data.

Then compare the space used in the log compared to the data files. That'll give you the answer.

Roust_m SSCoach Points: 17410 More actions · Answer 5

Well, I've tested it. I've created a new database with an empty compressed table and did an insert of 1GB of data. The data file went up to around 100MB, the log file was around 500MB which is much larger then the data, however, after I've created another database with the table not compressed and did the same insert, the data file went to around 1GB and the log file got to 1.6GB, which is three times larger then the log of the compressed database, so the myth is confirmed: data compression reduces the log file size as well as the data file. :hehe:

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 6

Roust_m (11/11/2010)
Well, I've tested it. I've created a new database with an empty compressed table and did an insert of 1GB of data. The data file went up to around 100MB, the log file was around 500MB which is much larger then the data, however, after I've created another database with the table not compressed and did the same insert, the data file went to around 1GB and the log file got to 1.6GB, which is three times larger then the log of the compressed database, so the myth is confirmed: data compression reduces the log file size as well as the data file. :hehe:

Great Thanks for confirmed.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/