How to Reduce the page count on Heap Index

Question

How to Reduce the page count on Heap Index

santoshkal

SSC-Addicted

Points: 437
More actions
October 30, 2014 at 5:05 am

#397586

I had a db with many tables which don't have any clustered or non clustered indexes but when i run the below query its show avg_fragement _percentage around 90% & page count in millions
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
can you help me out how to reduce the page count with out creating any indexes or any other way to release the unused space
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Create a clustered index on that table (and leave it there). There are few good reasons to have a table as a heap, very few. The storage engine is designed to work best with tables that have clustered indexes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

You do realize that indexes are absolutely wonderful things and extremely helpful in lots of situations? Lots of heap tables on a database sounds like a very questionable design. I'd strongly suggest reexamining it.

And yeah, if you want to de-fragment heaps, you need to use an index to do it.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software