Seeing a lot of HEAPS tables with large row counts on prod. Is this a problem ?
-
Hi All,
This one is general question. I see dev team quite oftem comes to Apps DBA team saying that performance is slow.
So, I was trying to check whether any indexes are fragmented and when was the stats last updated with full scan.
Then I executed one of DMV query to check for fragmentation.
I see many Heap tables with high fragmentation level and I see this on PROD.
So, I have few questions.
1stly, Is this a Bad design issue which can lead to performance issue? I dont know why there are lot HEAP tables instead of clustered idx. What suggestions can be provided to that team saying that whether it is good or bad to have these heaps in the first place.
2nd think, how can I remove the fragmentation in case of Heaps?
3rd, Can I ignore this fragmentation levels or is there a real problem with these high values? what shoule be my troubleshooting path in this case to get better query performance.
Index fragmentation screenshot
=======================
Thanks,
Sam
-
normally that high number of heaps means
1 - whoever designed this has a Oracle background and followed the same approach
2 - the tables are staging type tables where having the index may slow down the loads and not necessarily speed up the process
3 - whoever designed this is not aware that on SQL Server Clustered Indexes are really a must have with very few exceptions.
there are more links on this but the following can be a start point.
https://www.brentozar.com/blitz/heaps-tables-without-primary-key-clustered-index/
https://www.red-gate.com/simple-talk/blogs/reasons-why-you-may-not-want-to-use-a-heap/
As always and as mentioned on the links above always investigate what is the best set of columns to create the clustered index and always test performance before and after for all major queries running against the tables (and not just individual queries but also those with multiple joins!!!)
to remove the fragmentation you just to a "alter table rebuild"
as for being bad having that fragmentation - for the big tables possibly for the smaller ones its unlikely but still possible. not having the clustered index is probably hurting more than the fragmentation.
-
You can rebuild a heap table using ALTER TABLE ... REBUILD;
Also check the amount of space used by your heap tables - I suspect these tables are constantly deleted and rebuilt (not truncated) which does not allow for the reserved space to be reused and the allocated space just continually grows.
They should define a clustered index for these - but review how the tables are loaded and utilized before making that decision.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
If these are staging tables, just be sure to TRUNCATE the table rather than DELETE the rows. TRUNCATE will keep the table allocations clean without having to create a clustering index.
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".
-
Check the usage on the heaps. If they're not being used, don't worry about their fragmentation.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks everyone for the value suggestions.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply