Scenario:
Database sizing is one of the most critical tasks for any DBA. One of the DBA’s tasks is to also ensure optimum performance. Database/table size is one of the key factors impacting the performance.
Hence, it becomes essential to keep close eye on data volumes entering and departing the databases.
Solution:
I am sure most of the DBA’s would be aware of Microsoft’s undocumented SP’s.
‘sp_spaceused’ in one of them and proves to be very handy for DBA’s.
This SP displays the usage statistic for a table or a database.
I deal with lots of databases that are part of ETL process. Given the vibrant nature of ETL systems, loads of data is crunched on frequent basis. This leads to huge fragmentation and page splits. Most of the ETL databases are index light. And tables without indexes are known as Heaps (very trivial but just thought to put one liner).
Here is the output of sp_spaceused of one of the huge tables in my environment.
Syntax: sp_spaceused ‘TableABC’
| Name | Rows | Reserved | data | index_size | unused |
| TableABC | 3767252 | 77409456 KB | 23722280 KB | 56288 KB | 53630888 KB |
Total size of table is around 77 GB. However, 53 GB is free. Question is how to get rid of this unused space.
Answer is very simple. Just rebuild the heap, here is the syntax
Alter table ‘TableABC’ Rebuild
This will free up all the unused space in the table. In effect the database size would also be drastically reduced improving overall performance.
Note: Alter table <tablename> Rebuild, besides heap the command will also rebuild all the indexes (if any) on the table.
Regards
Ritesh Medhe
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
Ouch!
The posting is quite accurate. However, you did not point out any of the pain associated with such an action. Depending on the verison of SQL Server you may actually lock activity on a table during the re-indexing process.
For a large table, this action is no small thing. It is the correct thing to do. But, beware…you can bring a production server to it’s knees.
Hey Ben, Point taken.
I performed this activity on a heap. The table size was around 77 GB out of which 53 GB was free. And to my surprise the rebuild for heap took less than 5 minutes.
But yes if the table have CI/NCI then it would be a pain. However, such activities should be carried out during off hours or low traffic:)
Hmmm..Interesting..
So what about the forwarding pointers in the heap table ? Also can it be used on clustered tables ?
Hi Sachin, Yes heap rebulid will take care of forwarding pointers. FWD pointers will be removed, in effect will improve the performance.
Yes this can be used on clustered tables and as i mentioned
Alter table Rebuild, besides heap the command will also rebuild all the indexes (if any) on the table [CI & NCI]
So basically if I have a forwarding pointer in a table that is not a heap so will it still build the NC index ? And also what about the clustered index on it will that to also be rebuilt ?
Sachin: Absolutely, NCI & CI will be rebuilt…