index_id = 0

Question

index_id = 0

river1

SSC-Insane

Points: 24309
More actions
November 5, 2015 at 2:58 am

#399282

I have this query:
(SELECT
case when avg_fragmentation_in_percent < 30 then 'ReOrg' ELSE 'Rebuild' end as command,
object_id AS ObjectID,
index_id AS IndexID,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags,
avg_fragment_size_in_pages AS PagesPerFrag,
page_count AS NumPages
FROM sys.dm_db_index_physical_stats(DB_ID('distribuition'),
NULL, NULL, NULL , 'DETAILED')
WHERE avg_fragmentation_in_percent > 0 and page_count > 1000 and index_id <> 0 ) r
on a.object_id = r.ObjectID
ORDER BY PercentFragment desc
Question is I don't I need to rebuild indexes that have index_id = 0 ?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Index ID 0 means the tables a heap, rebuilding a heap can be done, but all the nonclustered indexes also get rebuild to change the RID pointer.

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/

river1 SSC-Insane Points: 24309 More actions · Answer 2

Ok. Let me give you an example:

nameObjectIDIndexIDPercentFragmentTotalFragsPagesPerFragNumPages

ticket_synchronization2145129316084.303417198648122509.4364444444444421232

Would you rebuild this index?

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Ant-Green

SSC Guru

Points: 113507

November 5, 2015 at 3:29 am

#1837902

No

river1 SSC-Insane Points: 24309 More actions · Answer 4

Ok. What about if the index has a value page_count < 1000 you don't rebuild also? even if its near 100% of avg_fragmentation?

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

I never rebuild heaps, in fact we don't even allow them to be created.

Every table change has to be reviewed, in the off chance one slips past us there is a policy on the servers to run hourly to flag up any tables which don't have a clustered index. Would love to do it as on change prevent, but its on schedule only which is a shame.

Any that are flagged get sent back to the developers to sort out, and if they can't we add in an identity column and make it the clustered index. Is identity the best to use, probably not but if there is no good clustering key, its better than nothing.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Fragmentation in heaps is completely different to indexes. For indexes it's logical fragmentation. Heaps have no logical order, so they only have extent fragmentation.

To be honest, if you have a lot of heaps, you should probably be considering adding a good clustered index to them.

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

ScottPletcher SSC Guru Points: 101106 More actions · Answer 7

I would certainly rebuild that table, if it's used a lot or you have some other cause of concern over it. As always for best performance, first determine and add the best clustered index to the table. For a table that small, you should be able to review all nonclustered indexes, if any, at the same time.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".