November 6, 2013 at 5:58 am
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
November 6, 2013 at 6:08 am
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;-)
November 6, 2013 at 6:08 am
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.
November 6, 2013 at 6:12 am
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!
November 6, 2013 at 6:31 am
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
November 6, 2013 at 8:17 am
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!
November 6, 2013 at 10:36 am
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
November 6, 2013 at 4:05 pm
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) "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".
November 6, 2013 at 10:05 pm
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
November 7, 2013 at 1:48 am
As said in my original post, I did use sort_in_tempdb = on. But that gave me about 20 GB of free space.
November 7, 2013 at 1:55 am
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply