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.
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’
|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.