January 10, 2006 at 12:30 pm
Realizing that fragmentation is an inevitability, we allocate time each night for a complete run of DBCC DBREINDEX on each database. Of course, as servers fill and client requests increase, the maintenance windows shrink (what a shame) so continuing this practice may become rather difficult, forcing a switch to a 1x a week schedule or something.
There is a gold lining though. As a couple others have mentioned, it is possible to do a complete defrag (logical and physical) whole the databases are online. Most "modern" tools such as D.K. allow this.
We've recently upgraded to tthe latest version of this tool and find it not only very effective at defragging tasks but much more efficient than expected. As always, I would suggest plenty of development testing befiore using on your production servers but to date, we've had no issues in any of our environments. There is a 30 day free trial. Use it!
February 10, 2006 at 8:45 am
For file defragmentation, I heard indeed that Diskeep does a good job been able to defrag files in use and base on a schedule, never use it though.
Regarding
"The defrag that comes with win2k3 is capable of defragging the files while sql server is online, it's a huge I/O hit and the server is pratically unusable; it's possible though."
I use it on one of our production servers and during the process it made the tempdb inaccessible. Even if I try to stop the process the SQL Server was inaccessible. I had to restart it.
February 10, 2006 at 8:52 am
Regarding
"The defrag that comes with win2k3 is capable of defragging the files while sql server is online, it's a huge I/O hit and the server is pratically unusable; it's possible though."
Diskeeper monitors the IO queue and throttles back when this rises so the hit to the machine is near nothing.
You could, essentially, run the defragger every hour. Why though...
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
February 27, 2006 at 1:16 pm
Hi,
I'm having negative experience with Diskeeper. It's locking us out of SQL when it's defragging the server. Defrag is scheduled weekly on Saturdays when we usually aren't hitting the db.
I'm running Win2003 Server and SQL 2000 on Raid 5 array. Two db's with the problem are 66 and 50 Gb each. Hard drive is 410 GB with 50% free.
First noticed it 3 weeks ago when a db maintenance job blew up. Changed it to complete before the defrag began and this worked.
However, a few data entry folks were in this Saturday am and could no longer access the db as soon Diskeeper started. Totally locked out. And PO'd.
From the thread it sounds like this shouldn't happen with Diskeeper. Is there an option/setting that we're missing?
Other than scheduling the defrag for later on Saturday, looking for a better solution.
Any ideas? Our network admin told me SQL has a defrag function. Since I already regularly defrag indexes weekly I'm not sure what he's talking about.
Thanks
Greg H
September 29, 2007 at 11:12 pm
There are at least five or six factual errors in this article, which I was going to list but there got to be too many. What's the definition of "internal fragmentation" again?
:pinch:
January 18, 2008 at 12:06 pm
I am having some "drinking from the firehose" moments where situation dictates I have to learn some things quickly and have limited tolerance for error. Hopefully, I can state my question succinctly and someone will be willing to offer advice.
I ran the DBCC scan on the DB and with some of the larger tables (500,000+ records) it was reporting over 70,000 pages with extent fragmentation in excess of 30% and logical fragmentation in excess of 40%. My understanding is this is a DB badly in need of defrag. (Side note, my understanding is that an earlier rev siilarly used would "in explicably become inaccessible and it looked like it was the tempdb" was the explanation)
Would your recommendation be to:
1) defrag with something like DK and then use REINDEX
2) RECREATE the indexes
3) something else? (to my limited understanding DEFRAG would not be a best candidate in this case)
Hopefully I have offered enough information to hazard an opinion.
January 18, 2008 at 12:09 pm
I did find this article very informative and, I believe, explained a seemingly complex subject quite well. But then again, I am not by training a DBA - I am just learing to dance as fast as I can.
October 15, 2008 at 6:47 pm
See BOL:
CREATE INDEX, DROP_EXISTING
"Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account"
I learnt this the hard way...
Umm, whatever you learned the hard way, I doubt it was due to DROP_EXISTING. What BOL is saying is that it rebuilds NCIs if need be, otherwise doesn't waste the time. However I see one caveat "If a clustered index is rebuilt to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the clustered index." Not something I'm ever likely to do, but figured I'd mention it, since I was about to claim that DROP_EXISTING never gets you in trouble. Anyway, unless you're doing something advanced, in a complex setup, it won't.
October 22, 2009 at 5:05 am
Hi.
3 years after its last publication, this article still sounds great 🙂
I think that it dismissed a T-SQL command that is a good alternative of running 'DBCC DBREINDEX (tablename)' :
ALTER INDEX ALL
ON [tablename]
REBUILD WITH ( PAD_INDEX = ON, FILLFACTOR = yourfillfactor )
Why ?
dbreindex offer the fillfactor paramter only if you specify an index; if not, or if fillfactor is not provided, it will use (and fill) 100%.
So any new insert will make a split of page (think about uniqueidentifier datatype, which will not be 'sequential' 😉 ).
Alter index offer pad_index and fillfactor (default is 0, value of freespace this time).
An other great reason is that you'll be able to rebuild the indexes for a partition, so only where it must be.
what do you think about that ?
December 6, 2010 at 11:11 pm
Hey Guys
Is there anyway to get these results returned by DBCC SHOWCONTIG through a system view. I've been using sys.dm_db_index_physical_stats but this doesn't give the extent fragmentation.
Reason being is that I want to run this across multiple server through a .net program which I've developed but it takes so long.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply