June 17, 2013 at 11:16 am
I know the subject isn't the clearest, but I think it'd be too long to put the entire question...
I'm looking at enabling compression on a table in one DB. the table is ~313 million records, and has one Clustered Index and 3 non-clustered. I'm planning to initially enable page-level compression on the CI, as I think that's going to give me the most savings (also backed up some by the estimate space saving SP)
What I'm concerned about, is *how* the compression is accomplished. Not the mechanics of the compression (I know page level is actually row-level first, then the pages,) but the how of it being done. Basically, if I enable this feature, will SQL need to hit the transaction log hard, possibly causing it to grow?
The DB in question is in Simple recovery, but the disks the DB and log live on are somewhat slim on space (because of this DB, actually) In a previous posting (Here) I had laid out what I was planning to do to recover some disk space from this table. The table in question had some "issues" that myself and the Dev had to work out (no CI, no PK... And he was trying to delete a couple million rows and it was taking days...) which in resolving, we didn't notice the table was originally page-compressed...
Yeah, long story...
Thanks,
Jason
June 17, 2013 at 1:34 pm
To enable compression, you have to rebuild the index. It will behave exactly as a normal index rebuild behaves, logging the full data size of the new index to the transaction log and needing free space in the data file for the new index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2013 at 6:03 am
OK, thanks.
Sounds like I won't be compressing this index / table any time soon then, as using the current size of the index as a guideline I don't believe we've got enough space to have a 2nd copy of it...
Hmm, time to look at other options...
Jason
June 18, 2013 at 6:13 am
OK, I might have a solution to this, if what I've heard is correct:
Lets say I have a DB with two log files, lets call them log A and B, on separate drives. If log A fills up, then SQL will simply start filling Log B. It is *NOT* "striping" between the log files, but more akin to having buckets on a flight of stairs, with each bucket filling the next one down when it fills up (or for the hoity-toity among us, a champagne glass pyramid)
Sound about right?
If so, then I can (temporarily) add a second log file on another drive in the server, and start the compression.
Thanks,
Jason
June 18, 2013 at 6:35 am
If you have two log files, SQL will fill the first then fill the second, then go back to the first (if there's free space in it)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2013 at 6:42 am
Excellent, thank you!
(So my bucket analogy was close, except the buckets are also on an escalator getting emptied out eventually and recycled to the bottom... 😉 )
Once more, thanks!
Jason
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply