Re: Restore = Defrag?
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/24/05
- Next message: Kalen Delaney: "Re: Restore = Defrag?"
- Previous message: brett.sander_at_tenix.com: "Re: SQLSERVER Event ID 17052, Error 1101, Severity 17, State:2"
- In reply to: Anthony Thomas: "Re: Restore = Defrag?"
- Next in thread: Anthony Thomas: "Re: Restore = Defrag?"
- Reply: Anthony Thomas: "Re: Restore = Defrag?"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 23 Jan 2005 21:38:13 -0500
The fragmentation at the OS level has absolutely nothing to do with
reindexing or defragging within SQL Server. Sql Server doesn't manage the
physical file per say, the OS does. Only during the intial file creation,
growing or shrinking does SQL Server ask the OS to physically adjust the
file in terms of size. And even then Sql Server does not have any control
as to where the file sis placed physically on the disk. Defragmenting with
DBREINDEX or INDEXDEFRAG works solely within the boundaries of the file and
can not cause any change in the physical placement on disk other than
forcing an AutoGrow. It sounds like the db had AutoGrow and AutoShrink (or
a Shrink job) turned on. I am not convinced the copying of the DB even
created contiguous files to start with but unless you checked the OS level
fragmentation immediately after the copies it would be hard to say. If
that was on a SAN you may want to check to be sure there isn't anything
funky with the OS Frag tool and the SAN. But in all the years that I have
worked with 7.0 or 2000 I have only seen fragmentation at the OS level as a
result of growing and shrinking. But unless the OS level fragmentation is
extreme it willnot have as much effect as Page and Extent fragmentation
within the file. A few non-contiguous segments in a data file are nothing to
be concerned with.
-- Andrew J. Kelly SQL MVP "Anthony Thomas" <ALThomas@kc.rr.com> wrote in message news:OWE2xRbAFHA.1400@TK2MSFTNGP11.phx.gbl... > 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: Kalen Delaney: "Re: Restore = Defrag?"
- Previous message: brett.sander_at_tenix.com: "Re: SQLSERVER Event ID 17052, Error 1101, Severity 17, State:2"
- In reply to: Anthony Thomas: "Re: Restore = Defrag?"
- Next in thread: Anthony Thomas: "Re: Restore = Defrag?"
- Reply: Anthony Thomas: "Re: Restore = Defrag?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
