Compact, fully indexed, archive database

Question

Post reply

Compact, fully indexed, archive database

alex.valen

SSC Eights!

Points: 940
More actions
November 6, 2013 at 5:58 am

#300808

Hi guys,
I was wondering how to create a compact database that is fully indexed (without fragmentation).
I've got a 7 GB table (heap) and I created 1 clustered and 6 non-clustered indexes on it. When it's done building these (with sort_in_tempdb = on), i've got a 13 GB database (used space, 20 GB unallocated) that is using 33 GB on disk! Shrink with truncate_only removes only a few MB. Shrink with reorganize would screw up the indexes (of course). The database will be used read-only when it's ready. How can i make the database more compact without fragmentation? Any thoughts on this?
Thanks in advance,
- Alex

Viewing 11 posts - 1 through 10 (of 10 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

alex.valen (11/6/2013)
The database will be used read-only when it's ready.- Alex

Rebuild all the indexes (preferably clustred ones) just before you are making the database read-only

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

Will this be hosted on enterprise edition? Alternatively phrased, can you use compression?

I would probably pre-size another filegroup to the correct size then rebuild the clustered and non-clustered indexes into that group.

You then either have the option of moving them back to primary or just shrinking it to minimal size.

alex.valen SSC Eights! Points: 940 More actions · Answer 3

Yes, indeed it will be hosted on enterprise, but I'm already using (page) compression.

I think I might give it a try with the filegroup option and see where that leads me!

Thanks for the (very quick) responses!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I'll second the 'rebuild indexes onto another filegroup' option. If you just rebuild them in the filegroup they're currently in, you'll probably end up with a lot of empty space in that filegroup (where the indexes used to be prior to the rebuild)

If you rebuild them into a different filegroup (not just file), then the empty space is in primary, all the indexes are in the other filegroup, you can shrink the files associated with primary, it'll fragment the system tables but that's not a big concern, leaving you with a nice small primary filegroup and a secondary with all your indexes and little free space.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alex.valen SSC Eights! Points: 940 More actions · Answer 5

Thanks guys! Worked like a charm.

REBUILDing on another FG doesn't work (ALTER INDEX doesn't support FG change). But dropping them and then CREATEing them on the other FG was successfull. And indeed, it took only the space needed!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Good point. Should have said CREATE ... WITH DROP_EXISTING

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 100942 More actions · Answer 7

You should also consider using option "SORT_IN_TEMPDB = ON" in the rebuild. That should give you about the same space gains without having to use a separate fg within the same db. Moreover, tempdb is often more highly tuned and/or on different drives, offering performance gains for the rebuild itself.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Even with sort in tempdb, there will still be free space left from where the index used to be. How much depends on the order that the rebuilds are done, among other things.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alex.valen SSC Eights! Points: 940 More actions · Answer 9

As said in my original post, I did use sort_in_tempdb = on. But that gave me about 20 GB of free space.

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 10

GilaMonster (11/6/2013)
Even with sort in tempdb, there will still be free space left from where the index used to be. How much depends on the order that the rebuilds are done, among other things.

+1

The new indexes will be located where there is space in the filegroup before the original structure is dropped. These could be at the "end" or "beginning" of the file so the poster will likely still be in a similar position to now and not be able to shrink the file without reorganising the pages.