Hi All,
I have partitioned table with lot of unused space in OLTP environment. i ran following query to find fragmentation percent. Most of the values are around 1.5. The fill factor for the table is set to 0 and the table has primary key.
How can i find what's causing the unused space to grow and also
free up the space without causing production issues.
TIA
-- Find the average fragmentation percentage of all indexes
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'ProdDb'),
OBJECT_ID(N'dbo.transaction'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;
name rows
reserved data
index_size unused
Transaction 7,222,185,836
595439200 KB 421631304 KB
2082904 KB 171724992 KB
Thanks
