June 8, 2009 at 2:02 pm
Despite the downside of shrinking databases, I may have to in order to free up some disk space until I can get more storage. We may be able to purge data, then shrink to reclaim space. If we do, is there a way to repair the fragmentation damage that occurs ? Would dropping the database & restoring it help ? The database is on a SAN shared by other servers.
June 8, 2009 at 2:11 pm
homebrew01 (6/8/2009)
If we do, is there a way to repair the fragmentation damage that occurs ?
Internal (index) fragmentation or external (file) fragmentation?
For the first, rebuild your indexes
For the second, stop SQL and run a disk defragmentation tool. Speak to the SAN admins. There's usually special defrag tools for SANs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 8:47 am
GilaMonster (6/8/2009)
Internal (index) fragmentation or external (file) fragmentation?
For the first, rebuild your indexes
For the second, stop SQL and run a disk defragmentation tool. Speak to the SAN admins. There's usually special defrag tools for SANs
I'm concerned about both. Does it matter which is done first ? Does SAN defragmentation re-fragment the data or vice versa ?
June 9, 2009 at 9:17 am
No, no and no.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 9:25 am
Indexes need to be rebuilt after physical defragmentation, per Microsoft.
- 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 9, 2009 at 9:30 am
But if you rebuild your indexes after shrinking the data files, won't this force the data files to grow again during the rebuild essentially negating all of your work?
June 9, 2009 at 9:30 am
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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 9:33 am
fbonneville (6/9/2009)
But if you rebuild your indexes after shrinking the data files, won't this force the data files to grow again during the rebuild essentially negating all of your work?
It may make the file grow, that's one reason that it's recommended, if a shrink has to happen, to leave some free space in the file. Depends how much space was freed with the shrink and how big the biggest index is whether or not it will negate the shrink. If you're freeing so little space that a rebuild takes it all back, then there's no point in shrinking in the first place.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 9:34 am
No. It won't undo the shrink. Adding data, adding indexes, etc., would do so. Rebuilding them shouldn't be a significant problem. Besides, it's necessary, since the indexes point to actual disk locations, and having them point to incorrect sectors isn't a good thing.
- 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 9, 2009 at 9:35 am
Actually I would think that you would want to rebuild indexes after physical fragmentation as well. I don't have a ref, but anytime you move physically the extents around, you potentially move them out of physical order. And that translates to head movement to read them. So even though the physical file is now contiguous (or mostly contiguous), the pieces of the file inside might have been moved around.
June 9, 2009 at 9:36 am
apparently everyone is in this thread now, but I'll repeat what's above. Rebuilding indexes will require more space, but you NEVER, NEVER, shrink down to the data size. Even if it's read only, you want space to add/rebuild indexes if needed. So you leave space in the file.
June 9, 2009 at 9:40 am
GSquared (6/9/2009)
Besides, it's necessary, since the indexes point to actual disk locations, and having them point to incorrect sectors isn't a good thing.
?????
As far as I'm aware, nothing within a SQL data file points to the actual disk locations. Pointers within indexes point to pages (file ID, Page Number). So an index's next page pointer may point to file 1 page 4732, meaning an offset of 4732*8kb into that data file. Where that is physically should only be an OS concern.
Can you reference BoL or MSDN article please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 9:46 am
Steve Jones - Editor (6/9/2009)
Actually I would think that you would want to rebuild indexes after physical fragmentation as well. I don't have a ref, but anytime you move physically the extents around, you potentially move them out of physical order. And that translates to head movement to read them. So even though the physical file is now contiguous (or mostly contiguous), the pieces of the file inside might have been moved around.
If a defrag could result in a page with an ID of x and an offset of x*8k before the defrag appearing at an offset of y*8k afterwards, fragmented indexes would be the least of your worries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 9:56 am
GilaMonster (6/9/2009)
GSquared (6/9/2009)
Besides, it's necessary, since the indexes point to actual disk locations, and having them point to incorrect sectors isn't a good thing.?????
As far as I'm aware, nothing within a SQL data file points to the actual disk locations. Pointers within indexes point to pages (file ID, Page Number). So an index's next page pointer may point to file 1 page 4732, meaning an offset of 4732*8kb into that data file. Where that is physically should only be an OS concern.
Can you reference BoL or MSDN article please?
I remember reading it, but I don't remember where. I'll have to find it. Depending on workload, may or may not have time today.
- 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 9, 2009 at 9:58 am
It's common sense to fix the file fragmentation first.
It will make fixing the internal fragmentation run faster.
If the file is very fragmented, much faster.
Tim
.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply