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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy