September 26, 2007 at 9:14 am
I've figured out how to use the sys.dm_db_physical_Stats to dynamically do an ALTER INDEX...REBUILD in my system. And it works pretty well. In less than 10 minutes, I've rebuilt 50+ indexes that had a fragmentation rate through the roof. 😀
Unfortunately, I'm still coming up with a bunch of Indexes that only rebuilt so much (one went from 72 percent to 50 percent, for example). And the books I've checked said anything over 30% should be rebuilt. But no matter how I try, I simply can't get these indexes to defrag any further.
I suspect the issue is that these indexes are Primary Keys. I know some of them are, anyway, not sure about the rest. So the question is, does anyone know how to dynamically script ALTER TABLE scripts with a DROP CONSTRAINT and ADD CONSTRAINT based off a list of names?
I know it sounds like I'm being lazy, but I'm swamped with projects right now. I just don't have time to open up SSMS's Summary page, do the right-click thing for every index / constraint and script it out, etc. I just want to go to table X & Y, grab the definition and do the DROP / ADD automatically with dynamic T-SQL.
Any thoughts?
September 26, 2007 at 11:47 am
Those indexes that do not go further down in "fragmentation" levels are ususally part of very small tables so I would not worry about those at all because for small tables a scan is probably more efficent than an index seek.
Cheers,
* Noel
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply