Re: High Extent Scan Fragmentation
From: Paul S Randal [MS] (prandal_at_online.microsoft.com)
Date: 06/18/04
- Next message: Paul S Randal [MS]: "Re: data reorganization"
- Previous message: Jaxon: "Re: Troubleshooting Read Queue Length - or not?"
- In reply to: brett: "Re: High Extent Scan Fragmentation"
- Next in thread: brett: "Re: High Extent Scan Fragmentation"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 18 Jun 2004 15:49:40 -0700
This looks like you've got multiple files. You're running showcontig on the
clustered index over an identity column - which is going to monatonically
increase with each insert, so the inserts are ordered in the index
perfectly - that's why there's no logical scan fragmentation. The allocation
system allocates one extent at a time when doing regular inserts, and
round-robins them through the available files, thus creating extent scan
fragmentation. I'd guess there are 3 or 4 data files from the data below.
When you rebuild the index, it's probably allocating 4 extents at a time per
file, thus reducing the extent scan fragmentation reported. Even though the
extent scan fragmentation algorithm doesn't work very well on multiple
files, it will still respond to bigger groups of contiguous extents per
file.
What are the results of running your test on a database you've setup and can
prove to yourself only has a single data file?
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"brett" <brettolges@gmail.com> wrote in message
news:6de7e854.0406180528.4dc7243@posting.google.com...
> Hi Paul,
>
> Thanks for your help on this.
>
> As far as the dbreindex every night, I guess that's just being done in
> an attempt to squeeze every bit of performance possible out of the db.
> I'm actually researching this on behalf of a client. I think he
> understands the dbreindex every night is probably overkill -- but he's
> experimenting trying to get the best performance possible.
>
> The white paper you pointed us to seems to imply that reindexing may
> not even have a very significant effect on performance, depending on
> the characteristics of the database. So we realize we may be barking
> up the wrong tree, but the fact that the fragmentation can not be
> eliminated seems strange, so we're wondering if it could be a clue
> into some hardware problem or configuration problem we may be able to
> fix.
>
> I'm going to double check and make sure there's just one file in the
> filegroup.
>
> Until I verify that, here's what we're working on:
>
> To try and eliminate as many variables as we could, we set up a simple
> test that we could take and run on diff't servers.
>
> The test just creates a dummy table and fills it with data with lots
> of single insert statements (to try and create fragmentation). Then
> we check the ext. scan frag before and after the dbcc dbreindex.
>
>
>
> Here's the test itself...
>
>
============================================================================
==
>
> use pubs
> go
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FragTest]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[FragTest]
> GO
>
> CREATE TABLE [dbo].[FragTest] (
> [FragTestId] [int] IDENTITY (1, 1) NOT NULL ,
> [Field1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Field2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> CREATE CLUSTERED INDEX [idxFragTestId] ON
> [dbo].[FragTest]([FragTestId]) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[FragTest] ADD
> CONSTRAINT [PK_FragTest] PRIMARY KEY NONCLUSTERED
> (
> [FragTestId]
> ) ON [PRIMARY]
> GO
>
> CREATE INDEX [idxField1] ON [dbo].[FragTest]([Field1]) ON [PRIMARY]
> GO
>
>
>
> set nocount on
> declare @i as integer
> set @i = 0
> while @i < 30000
> begin
> insert FragTest (Field1,Field2) values
> ('aaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')
> insert FragTest (Field1,Field2) values
> ('bbb','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb')
> insert FragTest (Field1,Field2) values
> ('ccc','ccccccccccccccccccccccccccccccccccccccccccccccc')
> insert FragTest (Field1,Field2) values
> ('ddd','dddddddddddddddddddddddddddddddddddddddddddddd')
> insert FragTest (Field1,Field2) values
> ('eee','eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee')
> insert FragTest (Field1,Field2) values
> ('fff','fffffffffffffffffffffffffffffffffffffffffffffff')
> insert FragTest (Field1,Field2) values
> ('ggg','gggggggggggggggggggggggggggggggggggggggggggggg')
> insert FragTest (Field1,Field2) values
> ('hhh','hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh')
> insert FragTest (Field1,Field2) values
> ('iii','iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii')
> insert FragTest (Field1,Field2) values
> ('jjj','jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj')
> set @i = @i + 1
> end
>
>
> dbcc showcontig('FragTest')
>
> DBCC DBREINDEX (FragTest, '', 100)
>
> dbcc showcontig('FragTest')
>
>
> =======================================================================
>
>
> Here are the results we got on the 'problem' server...
>
>
>
> Before DBCC REINDEX:
>
> DBCC SHOWCONTIG scanning 'FragTest' table...
> Table: 'FragTest' (1187767955); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned................................: 2632
> - Extents Scanned..............................: 334
> - Extent Switches..............................: 333
> - Avg. Pages per Extent........................: 7.9
> - Scan Density [Best Count:Actual Count].......: 98.50% [329:334]
> - Logical Scan Fragmentation ..................: 0.04%
> - Extent Scan Fragmentation ...................: 59.88%
> - Avg. Bytes Free per Page.....................: 48.9
> - Avg. Page Density (full).....................: 99.40%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
>
>
> After DBCC REINDEX:
>
> DBCC SHOWCONTIG scanning 'FragTest' table...
> Table: 'FragTest' (1187767955); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned................................: 2632
> - Extents Scanned..............................: 330
> - Extent Switches..............................: 329
> - Avg. Pages per Extent........................: 8.0
> - Scan Density [Best Count:Actual Count].......: 99.70% [329:330]
> - Logical Scan Fragmentation ..................: 0.00%
> - Extent Scan Fragmentation ...................: 31.82%
> - Avg. Bytes Free per Page.....................: 48.9
> - Avg. Page Density (full).....................: 99.40%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
>
>
>
>
>
>
>
> "Paul S Randal [MS]" <prandal@online.microsoft.com> wrote in message
news:<u3zdscOVEHA.3540@TK2MSFTNGP11.phx.gbl>...
> > I'd guess the free space in the file is fragmented then, preventing
> > contiguous extents being allocated when the index is rebuilt - although
this
> > much continuously present extent fragmentation really smacks of multiple
> > files. Are you sure there is only one file in the filegroup (I noticed
you
> > used primary file and primary filegroup in different responses)? Can you
> > post the output of dbcc checkalloc (as text, not as an attachment)?
> >
> > DBCC DBREINDEX will only fix the problem if there is contiguous free
space
> > in the file equal to the size of the index being rebuilt.
> >
> > Btw, why are you reindexing every night?
> >
> > Thanks
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "brett" <brettolges@gmail.com> wrote in message
> > news:6de7e854.0406170436.21ae2e17@posting.google.com...
> > > Hi Paul,
> > >
> > > This table and it's indexes are not built on multiple files. Both the
> > > table and it's indexes reside on the same RAID array, and all on the
> > > PRIMARY file.
> > >
> > > Everything i've found so far says that a dbcc dbreindex should fix the
> > > problem with extent scan fragmentation as long as a clustered index
> > > exists on the table. We've tried everything and I'm stumped.
> > >
> > > Actually it's not just one table, but several that show high (40-50%)
> > > extent scan fragmentation, even though we do dbcc dbreindexes on them
> > > every night. The Logical Scan Fragmentation goes down as we'd expect,
> > > but the dbreindex has very minimal (maybe 2 or 3%) effect on the
> > > extent scan fragmentation.
> > >
> > > Thanks for your response and any further clues!
> > >
> > >
> > >
> > > "Paul S Randal [MS]" <prandal@online.microsoft.com> wrote in message
> > news:<OcSydv9UEHA.1292@TK2MSFTNGP10.phx.gbl>...
> > >
> > > > The Extent Scan Fragmentation algorithm does not work on multiple
> > files -
> > > > this is documented. You shoudl use Logical Scan Fragmentation.
Please
> > read
> > > > the whitepaper below which will explain everything to you. Let me
know
> > if
> > > > you have any more questions.
> > > >
> > > >
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > > >
> > > > Regards
> > > >
> > > > --
> > > > Paul Randal
> > > > Dev Lead, Microsoft SQL Server Storage Engine
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "DeeJay Puar" <deejaypuar@yahoo.com> wrote in message
> > > > news:1d6ce01c453ac$22a84570$a001280a@phx.gbl...
> > > > > Hi,
> > > > >
> > > > > Just wanted to send a minor correction (I think). Here is
> > > > > what BOL says:
> > > > >
> > > > > Understanding Logical Scan Fragmentation and Extent Scan
> > > > > Fragmentation values:
> > > > >
> > > > > Logical Scan Fragmentation and, to a lesser extent, Extent
> > > > > Scan Fragmentation values give the best indication of a
> > > > > table's fragmentation level. Both these values should be
> > > > > as close to zero as possible (although a value from 0%
> > > > > through 10% may be acceptable). It should be noted that
> > > > > the Extent Scan Fragmentation value will be high if the
> > > > > index spans multiple files. Both methods of reducing
> > > > > fragmentation can be used to reduce these values.
> > > > >
> > > > > IMHO, RAID level has no effect on fragementation. Do you
> > > > > have an Index/table that has multiple files? Also, how are
> > > > > you moving the data to your laptop? Backing up and restore
> > > > > a database file anywhere (RAID 0, 1, 2, 5, etc) does NOT
> > > > > remove fragmentation. You can test this by restoring the
> > > > > database to a different RAID 5 server and compare the DBCC
> > > > > output. It will be the same.
> > > > >
> > > > > hth
> > > > >
> > > > > DeeJay
> > > > >
> > > > >
> > > > > >-----Original Message-----
> > > > > >yes.
> > > > > >
> > > > > >I read an article that suggested "Extent Scan
> > Fragmentation" was not a
> > > > > >reliable number to look at when data spanned multiple
> > disks.
> > > > > >
> > > > > >I believe the more reliable number to use is scan density.
> > > > > >
> > > > > >Hope this helps
> > > > > >
> > > > > >
> > > > > >Greg Jackson
> > > > > >PDX, Oregon
> > > > > >
> > > > > >
> > > > > >.
> > > > > >
- Next message: Paul S Randal [MS]: "Re: data reorganization"
- Previous message: Jaxon: "Re: Troubleshooting Read Queue Length - or not?"
- In reply to: brett: "Re: High Extent Scan Fragmentation"
- Next in thread: brett: "Re: High Extent Scan Fragmentation"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
