June 9, 2009 at 9:58 am
Correct, but it's not about pointers, it's about physical movement. If the extents are located in order 1, 2, 3, 4, 5 inside the logical file, as they should be after a rebuild, then you have less head movement, quicker read ahead, less waiting on an interrupt, etc. If those in the logical file are however, located on sectors
4. 5. 2. 12. 19, then you have potentially lots of head movement. The physical defrag aligns disk sectors for the file so to move through the file you have less time required. A logical index rebuild then aligns the extents and pages inside those physical sectors close to each other.
At least that's what I've understood. We are talking very, very short time periods, but they can add up
June 16, 2009 at 1:46 pm
GilaMonster (6/9/2009)
GSquared (6/9/2009)
Indexes need to be rebuilt after physical defragmentation, per Microsoft.Ref please?
I can't see why. Index fragmentation is about the order of pages in the data file, shouldn't be affected at all by where the pieces of the data file are.
I'm going to have to withdraw that. I remember reading about it a few years ago, but I can't find the material, so can't back it up with anything.
It was on the subject that file fragmentation doesn't have the direct impact on SQL databases as it has on, for example, video or large bmp files, because the indexes include a disk address for the page the data is in, and they lose that advantage if you have something outside of SQL Server defragment the database file.
But, as mentioned, I can't find the article any more, so please disregard that I mentioned it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 17, 2009 at 1:57 am
The following two statements are both true and do not contradict each other.
(Gails point) The order in which you perform disk defragmentation / internal defragmentation will make no difference to the end result in terms of the way the data is organised on the disk (assuming nothing else happens at the same time).
(My point) It's common sense to do the disk defragmentation first because it will make the internal defragmentation run quicker.
If the second point wasn't true there would be no point in disk defragmentation at all because it would logically follow that it didn't improve the I/O profile and response times for SQL Server!
Tim
.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply