Re: Index Fragementing Rapidly

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Karl Pierburg (KarlPierburg_at_discussions.microsoft.com)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 12:34:02 -0700

Thanks - I did have auto shrink on for the database, as well as running the DBCC command nightly. I'm sure that didn't help matters. I've turned off Auto Shrink, and have rmoved the DBCC command. Only time will tell....... Now for the other interesting part, watching our database size. It likes to sit around 12 - 13 GB.

Side question: I'm a DBA only by way of the fact that there's no else to do it (small, 3 person IT staff). I can program SQL Server no problem - any resources (i.e. books) you would recommend for things like this. Specifically, I'd love to look into daily admin tasks (i.e. preventative maintenance) and performace / db tuning).

Thanks for your help - this has been really informative.

KP

"Andrew J. Kelly" wrote:

> > 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
>
> To shrink the file it must move any pages at the end of the physical file to
> someplace near the beginning since the shrink happens from the end inward.
> This for one is a very expensive operation in terms of resources and
> logging. But chances are after the move the data that was so nicely
> defragged and contiguous earlier (by the reindexing) is now spread all over
> the file where ever SQL Server had a place to put the extents. This is
> usually mixed in amongst all the other extents and causes extent
> fragmentation. But then later that night you reindex the tables again and
> this forces the database to grow and starts the whole process all over
> again. Put lots of free space in the data files and leave it there.
>
>
> > 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on
> INDENTITY fields. It seems to me that I would want to specify a fill factor
> of 100%, since I'll never do any inserts earlier in the page / extent?
>
> Yes in your case you probably do want 100%. The new rows will be appended
> and will never grow. This is an ideal situation for keeping the
> fragmentation and reads to a minimum. I have to believe Autoshrink was
> kicking in and not only freezing your database (so it seemed) but
> fragmenting the tables as well.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Karl Pierburg" <KarlPierburg@discussions.microsoft.com> wrote in message
> news:852300A1-859C-41DC-A047-F12C87F61D11@microsoft.com...
> > Andrew-
> >
> > Actually, I do do a DBCC SHRINKDATABASE every night as part of my nightly
> processing. I just read a post that you were involved with that basically
> said "don't do that".
> >
> > I will take it out. It's just a weird problem that happens with no
> regularity.
> >
> > These rows are NEVER updated or deleted. Some basic read-only reporting
> is all that takes place.
> >
> > Could you comment on 2 things:
> >
> > 1.) Why to use DBCC SHRINKDATABASE, and why it's bad.
> > 2.) What effect FILL FACTOR or PAD INDEX has on Clustered indexs on INDEN
> TITY fields. It seems to me that I would want to speficy a fill factor of
> 100%, since I'll never do any inserts earlier in the page / extent?
> >
> > "Andrew J. Kelly" wrote:
> >
> > > Karl,
> > >
> > > Are you sure there isn't a shrink operation going on? DO you have a Job
> > > scheduled to do a shrink or worse yet is AutoShrink turned on? A
> clustered
> > > index on an Identity column will not cause splits or fragmentation with
> just
> > > inserts. Do you update these rows after they are inserted?
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Karl Pierburg" <KarlPierburg@discussions.microsoft.com> wrote in
> message
> > > news:1671C010-B598-4EC4-8225-0FBAE90CB611@microsoft.com...
> > > > But why does my table fragment so quickly? I have approx. 400,000
> records
> > > in the table, with, on average, about 1 row being added a second and it
> > > appears to go from being < 1% fragment to > 90% in a matter of seconds
> (all
> > > after running fine for hours / days without reindexing).
> > > >
> > > > THe problem is that all of the sudden the website goes from working
> fine
> > > to completely shut down in a matter of seconds.
> > > >
> > > > "Hari Prasad" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Info from Books online:-
> > > > >
> > > > > Table fragmentation occurs through the process of data modifications
> > > > > (INSERT, UPDATE, and DELETE statements) made against the table.
> > > > > Because these modifications are not usually distributed equally
> among
> > > the
> > > > > rows of the table, the fullness of each page can vary over
> > > > > time causing fragments.For queries that scan part or all of a table,
> > > such
> > > > > table fragmentation can cause additional page reads,
> > > > > which hinders parallel scanning of data.
> > > > >
> > > > > Thanks
> > > > > Hari
> > > > > MCDBA
> > > > >
> > > > > "Karl Pierburg" <KarlPierburg@discussions.microsoft.com> wrote in
> > > message
> > > > > news:A957CF4F-0820-4628-84D6-3BD40FE07664@microsoft.com...
> > > > > > I'm a little confused over something that keeps happinging to my
> > > database.
> > > > > >
> > > > > > I have a web app that insert a row into a SS2K table every time a
> page
> > > is
> > > > > viewed. It's an intranet site, and I maybe average one page a
> second,
> > > no
> > > > > big deal. THe table uses an Indentity field, and has a clustered
> index
> > > on
> > > > > that Indenty Field alone.
> > > > > >
> > > > > > Most of the time this works fine, absolutely no problems, no slow
> > > down,
> > > > > etc. However, from time to time the insert into this table will
> "lock
> > > up" -
> > > > > take 1 - 1 1/2 minutes to perform, which obviously shuts down my
> > > website.
> > > > > >
> > > > > > When I look at the tables, using DBCC SHOWCONTIG, the logical
> > > > > fragmentation is high (i.e. 98%). I run a DBCC INDEXDEFRAG, and
> that
> > > > > generally takes care of the problem. I've setup a job to run
> nightly
> > > that
> > > > > runs the following sql command: DBCC DBREINDEX ('Activity','',70).
> > > > > >
> > > > > > Here's my question: This will happen suddenly (i.e. one page
> request,
> > > no
> > > > > problem, the next, lock up). Why does my index fragment so
> rapidly? I
> > > > > would expect a "build up". Could something else be going on that
> I'm
> > > > > missing?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Relevant Pages

  • Re: index fragmentation
    ... > Andrew - can you provide a link to Tibor's ... > 2) shrink has run and totally reversed the fragmentation status - to be the ... >>>>> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.server)
  • Re: index fragmentation
    ... shrink has run and totally reversed the fragmentation status - to be the ... Andrew - can you provide a link to Tibor's ... >>>> Andrew J. Kelly SQL MVP ...
    (microsoft.public.sqlserver.server)
  • Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It
    ... The internal fragmentation has nothing to do with the file level ... This has NO bearing on what the disk defrag tool sees at the file level. ... So when you shrink a file ... How much usable free space you end up with depends a lot on ...
    (microsoft.public.sqlserver.replication)
  • Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It
    ... The internal fragmentation has nothing to do with the file level ... This has NO bearing on what the disk defrag tool sees at the file level. ... So when you shrink a file ... How much usable free space you end up with depends a lot on ...
    (microsoft.public.sqlserver.server)
  • Re: DBCC SHRINKFILE Taking Forever & GROWING Data File Instead of Shrinking It
    ... The internal fragmentation has nothing to do with the file level ... This has NO bearing on what the disk defrag tool sees at the file level. ... So when you shrink a file ... How much usable free space you end up with depends a lot on ...
    (microsoft.public.sqlserver.tools)