asked
Heap index fragmentation
When i executed the below query. USE ABC GO SELECT CAST(DB_NAME(DATABASE_ID) AS VARCHAR(20)) AS 'DatabaseName', CAST(OBJECT_NAME([OBJECT_ID]) AS VARCHAR(20)) AS 'TableName', INDEX_ID, CAST(INDEX_TYPE_DESC AS VARCHAR(20)) AS INDEX_TYPE_DESC, AVG_FRAGMENTATION_IN_PERCENT FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID('ABC'),OBJECT_ID ('dbo.DATA'),NULL,NULL,NULL ); GO Iam getting the output as below, Database Name Table Name INDEX_ID INDEX_TYPE_DESC AVG_FRAGMENTATION_IN_PERCENT ABC TRAN 0 HEAP 51.35135135 ABC CIRCLE 0 HEAP 0 ABC FUNCTIONAL 0 HEAP 0 ABC DATA 1 CLUSTERED INDEX 0.720906282 ABC TRAN 0 HEAP 20.59496568 ABC DATA 1 CLUSTERED INDEX 30.12048193 ABC DATA 11 NONCLUSTERED INDEX 0.632911392 My database ABC have one clustered index and a non-clustered index in the DATA table. why this output is showing the index type as heap . please explain the output figures.
This site functions according to votes. For all helpful answers below, show this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer.
If there is no clustered index on a table, it is by definition a heap. Therefore, you'll see index_type_desc is heap for those tables.
For fragmentation on a heap, you can rebuild the table using syntax such as the following. ALTER TABLE T1 REBUILD; [
http://msdn.microsoft.com/en-us/library/ms190273.aspx][1] And some words of caution on that. [
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/][2] A better fix may be to figure out an appropriate Clustered Index. There are benefits to a Clustered Index that should be considered (such as avoiding forwarding records). [1]:
http://msdn.microsoft.com/en-us/library/ms190273.aspx [2]:
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/
17 People are following this question.
