October 30, 2014 at 5:05 am
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
October 30, 2014 at 5:34 am
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
October 30, 2014 at 6:56 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply