November 2, 2014 at 7:53 am
On my database there are 9-10 Heap index type. some of them are 99% fragmented. I learned from online if I create index and then drop it the fragment will drop. And it worked from 99% to 38% fragmentation. That table had around 500,000 page count but it went up around 525,000 page count.
My question is since it added around 25,000 pages, should I be happy it did good or worst? Fragments went down but pages count went up, it is good or bad thing?
November 2, 2014 at 8:06 am
Create a clustered index and *leave it*. There are very few good reasons to have a heap table. Just pick a good clustered index, create it on the table and then you can treat the table the same as the other tables for index maintenance.
Fragmentation in heap is not the problem. Forwarded rows are often the more serious problem in a heap because they cause more reads, more CPU, etc.
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
November 2, 2014 at 8:32 am
what if there are more column with GUID? what do you suggest please.
November 2, 2014 at 10:16 am
Pick a good clustered index, create it on the table and leave it there.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply