Reclaim unused space / Rebuild table
-
Hello,
I was hoping to get suggestions to the best method to my issue regarding unused space. SQL SERVER 2014 BI EDITTION
I have a table that showed 62% Data, 7% unallocated, and 29% unused space.
I ran the ALTER TABLE <table name> REBUILD, which changed it to 32% data and 67% unused.
What I do with this table monthly and what I believe is causing this unused space issue is this:
Every 1st of the month I get a csv file that holds around 3.2 million rows/15 columns of data. About 3 gbs every time.
It's 13 months of data. I remove the last 12 months from the current table and import the new 13 month data.
I do this twice a month and have no choice because the data is constantly updated and why we get it twice a month.
So I am deleting around 6 gbs of data a month as well as adding around 6 gbs of data a month.
I believe this is why I end up with so much unused space, but just found the REBUILD command, which worked but now a small hit on performance and have too much space in the current table. I have read several times that shrinking the file is no good, but what other way to get rid of the extra space in this table? Also, the table doesn't have any indexes or primary key because of duplicates.
Thank you for any suggestions.
-
From what you say you are dealing with a Heap and when you add and delete data in a Heap, SQL Server manages what is "allocated" to the table and what is not. Without a shrink you will find that there are times when you will have pages allocated to an object but not used. You could see if creating a clustered index on the table and dropping it would help with your allocations.
I would not recommend using Shrink in many cases, but if your only design is to get rid of the extra allocations for that object, then shrink would certainly take care of it. Without indexes fragmentation will only happen on the heap, but your mileage will vary based on your scenario. You could copy the database to another database and run some tests on what a shrink would do or what a create clustered index would do to help you in your situation.
Bottom line, there is only one way to remove space from a database without copying data out into another, and that is a shrink. Tread carefully.
-
Using SHRINK fragments all indexes in the database on a massive scale. I wouldn't do it unless something really bad and huge happened to the database and then I'd also have a plan for how to defragment every table and index without growing things too much.
I agree with Ben above. From your description, is sounds like the table is actually a HEAP (no clustered index). You won't be able to recover much free space from the table unless it has a clustered index. Since you're working with the table on a "monthly basis", I'd recommend adding a clustered index to the date column that you're using to delineate the months. Understand that will also cause the non-clustered indexes to be rebuilt, as well.
As for you just finding out about "REBUILD", do you have a DBA that has setup a maintenance plan to selectively rebuild/reorganize indexes in your database or have you setup such a thing? If so, what method are they using (I ask because some are way better than others) and if not, you should get some help on it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thank you both for your responses! I do realize that shrink is not popular and haven't nor do I want to do it. I was looking for responses about it to confirm what I thought was a bad idea.
There are not any indexes because all columns have multiple duplicates and the developer did it this way.
I did test the rebuild on one and it did clear up the extra unused space, but a very small performance hit.
I will have to check the fragmentation on the heap as you mentioned, but for some reason I thought it would be corrected with the maintenance plan.
Maintenance Plan = I run OH's maintenance plan.
Thanks again!
-
Ok, 2 things:
1. Don't just check the frag on the one heap cause if you read above he said that it frags all indexes. Or it can anyway. So the issue of shrinking is about more than just the table you're targeting.
2. Try my reindex routine (link below). It's way more flexible than OH's routine. We've got over 60pgs of docs and even vids to show you how to use it. But it's really super easy to setup.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Just to clarify I will check the frag on all databases that I have this issue with. Each database has heaps without any indexes or primary key. It is data that each month must be deleted and new imported in. All of the columns have many duplicates. The biggest heap is about 3.2 million rows each time.
I did test one database with the REBUILD statement. I realize I may just have to create a new heap and copy data over.
I will check out your routine and thanks for posting!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply