SQL Server 2014 table compression

techresearch7777777 1,881

Hello, planning on running a SQL Agent Job to compress some Prod Tables which looks like will take a couple of hours to finish.

Is it correct to say that during this Tables (only) compression job just the target tables will be unavailable/locked and not the entire DB?

If yes will any Views that refer to these Tables also be unavailable/locked?

Thanks in advance.

7 answers

tibor_karaszi@hotmail.com 4,311 Reputation points

2020-08-20T18:13:03.993+00:00

I did a test and did a rebuild of a clustered index 4 times. 2 times offline and 2 times online. On both occasion, the online required about twice ad much space in the transaction log. Also, if you have users modifying the data while the index is being rebuilt, then you need space in tempdb for that.

If you were on SQL Server 2019, you could do a resumable index rebuild. Stop it and truncate the log while it is paused.

I, of course, assume full recovery model, since index rebuild is minimally logged in simple and bulk_logged.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
techresearch7777777 1,881 Reputation points

2020-08-20T22:11:47.287+00:00

Thanks so much Tibor.

We're using Simple Recovery.

Please let me know if anything else I should take into consideration.

Cheers.
Please sign in to rate this answer.
Shashank Singh 6,251 Reputation points

2020-08-21T04:05:45.953+00:00

There would hardly be much difference between transaction logs generated in simple and full recovery so it would not matter much and you would see same amount of logs. The only thing is in simple when checkpoint happens the log file will automatically try to truncate giving you reusable space in transaction log file. But in full, like Tibor said, if you want to truncate logs you have to pause index rebuild and take transaction log backup but truncate

tibor_karaszi@hotmail.com 4,311 Reputation points

2020-08-21T10:31:42.36+00:00

"There would hardly be much difference between transaction logs generated in simple and full recovery so it would not matter much and you would see same amount of logs."

I don't follow you here Shanky. In simple, a rebuild is minimally logged so there should be only a fraction of log records in simple recovery (compared to full recovery). I just tested a rebuild turning a non-compressed cl ix to a compressed cl is, and in simple recovery, the lfg file grew from 6 MB to 300MB. In full recovery doing the same operation, I had 5.5 GB in the log.

Shashank Singh 6,251 Reputation points

2020-08-22T06:25:21.56+00:00

@TiborKarazi The rebuild of index in ONLINE mode is fully logged but minimally logged in offline mode. This is well documented in This KB Article. Sorry for late response we dont have mechanism to get alert when someone responses here on this QA website

Erland Sommarskog 112.7K Reputation points MVP

2020-08-22T09:08:42.647+00:00

But Shanky, that article talks about databases using full recovery. Apparently SQL Server used minimal recovery for online rebuilds in SQL 2005, which sounds like a major blunder and they corrected this in SQL 2008.

I made a quick test on a million-row table and rebuilt all indexes (without compression) offline and online in simple recovery and then in full recovery. The log grew a lot more in full recovery. And in fact, the online rebuild only caused the log grow to about half of the what the offline rebuild required.

tibor_karaszi@hotmail.com 4,311 Reputation points

2020-08-22T09:34:52.907+00:00

Did you see my test above? The ldf file grew from 6 to 300 mb in simple mode and but in full mode it instead gre 5.5 GB. Perhaps it is fully logged, but it ca truncate while running? Several transactions,some connection to the ability to truncate log for resumable rebuild? ? And perhaps this isn't the case on 2014? I don't have a 2014 to play with at tme moment...

Erland Sommarskog 112.7K Reputation points MVP

2020-08-22T13:23:05.157+00:00

As I said, that was a quick test. That database also proved to have a database snapshot, which makes it a little special.

Anyway, I ran the script 19674-slask.txt on all versions from SQL 2008 and up and I got the result as seen here:

The outcome for simple recovery is somewhat bewildering.

Erland Sommarskog 112.7K Reputation points MVP

2020-08-22T17:47:34.887+00:00

All numbers in the screen shot is the size of the log file in KB after the rebuild. The size of the log file before the rebuild was 20 480 KB.

tibor_karaszi@hotmail.com 4,311 Reputation points

2020-08-23T12:25:04.693+00:00

Interesting. Erland! What are we looking at here? I.e., the numbers in the diagram. The growth of the ldf file? One possibility for the small number for 2019 in simple mode is that the operation is "fully logged" (whatever that might mean more exactly), but the log is truncated during the operation. So, the code in SQL Sever taking advantage if the resumable "truncate the log while it is paused" also without pausing. I.e., that is why the file isn't larger in your screenshot for simpla/2019 - it did produce more log records but it was truncated during the operation.

tibor_karaszi@hotmail.com 4,311 Reputation points

2020-08-23T12:27:36.7+00:00

Btw, Shanky. There is notification options (among others for comments), just go though all the stuff you can configure. You account, upper right in the screen. :-)

Erland Sommarskog 112.7K Reputation points MVP

2020-08-23T18:57:13.767+00:00

Not sure what you are talking about the log being truncated while the operation is running. The log cannot be truncated, if the transaction has to be rolled back.

Yes, with resumable index rebuilds, there are multiple transactions, and this could hold down the log growth, but I did not specify the operation to be resumable.

The numbers you see are not the log growth per se, but the final size of the log with a starting size of 20480. So what is happening on SQL 2008, I don't know.

As for SQL 2019, it turned out that I had Accelerated Database Recovery enabled on that database. I scratched the database and rebuilt it from scripts and re-ran the test, but the result was the same.

tibor_karaszi@hotmail.com 4,311 Reputation points

2020-08-23T19:26:30.81+00:00

Imagine sql server doing a micropause, even though you didn't say resumable. Without you knowing it. And the sole purpose of this micropause is to truncate the log. That would explain the numbers we see for 2019. Sure, a rollback wouldn't rollback the whole operation, but who cares? Should be easy enough to test. Halt the operation and see if the fraglevel is same as before the operation. Assuming the index was fragmented in the first place. I might give that a spin tomorrow.

Erland Sommarskog 112.7K Reputation points MVP

2020-08-23T21:25:06.43+00:00

I think await your spins, before I try something more.

But the fraglevel should not change until the rebuild is done. True, it could commit in parts, because all that has to be rolled back is the extent allocations, and that could be performed in a kind of ghost cleanup.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL Server 2014 table compression

7 answers

Your answer