February 9, 2009 at 8:28 am
We noticed that one of our databases has become a lot bigger than it should. sp_spaceused has thrown up a lot of unused space, and on further digging, sys.dm_db_index_physical_stats is showing a lot of fragmentation.
On a nightly basis, the tables of this database get cleared, and then re-populated from another database. I realise that lots of DELETEs and INSERTs cause fragmentation over time, and I was under the impression that dropping indexes prior to the delete/repopulation, and recreating them all after the data is loaded, would be enough to stop the fragmentation from happening. Apparently not!
Would I be better off leaving the indexes as they are prior to the delete/repopulating, and then running ALTER INDEX REORGANISE?
I have attached an *.xls with an example of the data I'm getting back from sys.dm_db_index_physical_stats.
It seems that tables with no clustered index have suffered the most, fragmentation-wise, compared to ones with clustered indexes.
The tables that seem worse than all are the ones with no indexes on them - what could I do about them, seeing as there is no index to rebuild? (apart from "redesign the whole thing".... I would love to, but you know how it is...)
Many thanks for any thoughts/pointers
February 9, 2009 at 4:41 pm
For heaps obviously you won't be able reduce fragmentation without going thru the complete cycle of creating a clustered index on the heap and then rebuilding or reorganizing it based on frag. level and finally dropping it again.
MJ
February 9, 2009 at 10:35 pm
You really should consider indexing the heaps. A clustered index is how SQL Server is optimized to work, so create one on each table. Is there a reason not to do that?
February 10, 2009 at 6:13 am
Thanks folks
I am in the process of adding clustered indexes now.
As I'm deleting/inserting nightly, I expect that these indexes will start getting fragmented again soon.
The database is pretty small, and we didn't notice any performance problems even with the fragmentation - would you say that a weekly reorganise would likely be enough to keep the db from fragmenting?
Thanks again
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply