Re: Restore = Defrag?
From: Anthony Thomas (ALThomas_at_kc.rr.com)
Date: 01/24/05
- Next message: Anthony Thomas: "Re: Restore = Defrag?"
- Previous message: Anthony Thomas: "Re: Host Name Change and Replication"
- In reply to: Andrew J. Kelly: "Re: Restore = Defrag?"
- Next in thread: Andrew J. Kelly: "Re: Restore = Defrag?"
- Reply: Andrew J. Kelly: "Re: Restore = Defrag?"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 23 Jan 2005 19:30:16 -0600
I'm not so sure about the last statement. Last year we had to scale up the
hardware of one of our co-hosted fail over clusters. We ended up migrating
the databases from the old server to the new, one database at a time, on
clean, freshly installed, shared cluster disks. This should have copied the
files in large contiguous chunks. Within 1 month, with weekly index
defrags, the OS file fragmentation was over 86%. Now, with minimal 1 to 2
GB growth per week, roughly 10% increaments, Autogrow, against about 70
databases, with 48 GB on a 60 GB partition, how could the OS fragmentation
get so high in so short a time?
Moreover, if we shut SQL Server down to defrag the disk, which would be a
very time consuming operation for a highly-available system, what would be
the point if it would be this fragmented again in such a short time?
What are do you see as a typical OS disk defragmenting schedule? I've
always told our OS System Admins to go suck a lemon because SQL Server
managed the logical data page organization when we defrag our clusterd
indexes and that we were never garaunteed a contigueous placement of these
data pages anyway.
Sincerely,
Anthony Thomas
-- "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:u3lCGDbAFHA.2104@TK2MSFTNGP14.phx.gbl... Maybe this will help: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx > Hey, Kalen, while I got you on the line, I realize shrinking the database > could, in the end, cause more fragmentation, due to page splits, Actually a shrink does not split the pages. It simply moves them (or the extents) from the back of the file to the first available space near the front. The fragmentation you get from shrinking is that the extents are no longer contiguous in the file (assuming they were to begin with). If you do regular enough index maintenance you can assume that most of the pages and extents are in the right order. The shrink will break that order at the extent level for sure. If the database files are created large enough to handle the data for quite a while to begin with you should avoid the OS level fragmentation. That comes from making the file too small and lots of growing and shrinking which should be avoided when ever possible. -- Andrew J. Kelly SQL MVP "Anthony Thomas" <ALThomas@kc.rr.com> wrote in message news:uN5n7yaAFHA.3616@TK2MSFTNGP11.phx.gbl... > Hey, Kalen, while I got you on the line, I realize shrinking the database > could, in the end, cause more fragmentation, due to page splits, > especially > if the fill factor is to high. However, my OS System Admins constantly > pester me about how much SQL Server database files are NTFS fragmented on > the disk. I realize that internally, SQL Server manages its own files by > page and extent placement and just because the file may be physically > sequential, this does not garauntee any page order in the logical > sequence. > > How would I go about explaining this to them that the logical > fragementation > of data pages and/or index pages is more significant to performance than > the > contigueousness of the physical file clusters? > > Thanks, > > Anthony Thomas > > > -- > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:u2UrLiPAFHA.1908@TK2MSFTNGP15.phx.gbl... > Be careful with this. First of all, SQL Server 2000 has a DBCC > SHRINKDATABASE, not SHRINKDB. > > There is no specific reason to do a shrink when you're trying to > defragment. > In fact, shrinking your data files, because it moves all the data to > earlier > in the file wherever there happens to be room, can actually drastically > increase your fragmentation. > > Shrinking a database is not rercommended, for many reasons. Please see: > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp > > -- > HTH > ---------------- > Kalen Delaney > SQL Server MVP > www.SolidQualityLearning.com > > > "Rick Sawtell" <r_sawtell@hotmail.com> wrote in message > news:euNveOPAFHA.3264@TK2MSFTNGP12.phx.gbl... >> >> "barryfz" <barryfz@nospam.nospam> wrote in message >> news:eW43XeJAFHA.3368@TK2MSFTNGP15.phx.gbl... >>> If I backup a db and then delete and restore it, is that equivalent to >> doing >>> a defrag? >>> >>> >> >> When you say defrag, what are you referring to? >> >> In general, No. SQL Server reads your data Extent by Extent and writes >> that to the backup. >> >> To defrag the data you need to defrag your clustered indexes and then run >> a >> DBCC SHRINKDB or DBCC SHRINKFILE command. You can look those in the BOL >> for more information about what they do for you. >> >> >> Rick Sawtell >> MCT, MCSD, MCDBA >> >> >> > >
- Next message: Anthony Thomas: "Re: Restore = Defrag?"
- Previous message: Anthony Thomas: "Re: Host Name Change and Replication"
- In reply to: Andrew J. Kelly: "Re: Restore = Defrag?"
- Next in thread: Andrew J. Kelly: "Re: Restore = Defrag?"
- Reply: Andrew J. Kelly: "Re: Restore = Defrag?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
