Rebuild indices

hello,
I have a problem where certain indexes on an sql server 2005 database
remain fragmented no matter how much rebuild I do on them.
Why is this? and how can I rebuild the indexes correctly without
fragmentation.. (I'm having about 80%) ?
Something weird I noticed is when I'm using the DBCC DBREINDEX on this
field (the one having 80% fragmentation),
when giving a fill factor of 10 or less the fragmentation is reduced
to 11% ... while greater than 10 the fragmentation remains basically
the same.
So the thing is the less and the better? What's happening exactly? I
thought it was because of the fill factor .. however by default Sql
server makes a fill factor of 90%... so shouldn't be that problem
Thanks in advance for shedding a bit of light on this!Varangian,
Can you post the "create index" statement and the result from "DBCC
SHOWCONTI" or "select ... from sys.dm_db_index_physical_stats"?
AMB
"Varangian" wrote:
> hello,
> I have a problem where certain indexes on an sql server 2005 database
> remain fragmented no matter how much rebuild I do on them.
> Why is this? and how can I rebuild the indexes correctly without
> fragmentation.. (I'm having about 80%) ?
> Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> field (the one having 80% fragmentation),
> when giving a fill factor of 10 or less the fragmentation is reduced
> to 11% ... while greater than 10 the fragmentation remains basically
> the same.
> So the thing is the less and the better? What's happening exactly? I
> thought it was because of the fill factor .. however by default Sql
> server makes a fill factor of 90%... so shouldn't be that problem
> Thanks in advance for shedding a bit of light on this!
>|||How much free space is in your database? If you are like 100% of my clients
you rely on autogrowths to size your dbs and thus you never have any
contiguous free space in your databases in which the defrag operations can
lay down the indexes. Double or triple the size of your databae and then do
a defrag and see what happens.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> hello,
> I have a problem where certain indexes on an sql server 2005 database
> remain fragmented no matter how much rebuild I do on them.
> Why is this? and how can I rebuild the indexes correctly without
> fragmentation.. (I'm having about 80%) ?
> Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> field (the one having 80% fragmentation),
> when giving a fill factor of 10 or less the fragmentation is reduced
> to 11% ... while greater than 10 the fragmentation remains basically
> the same.
> So the thing is the less and the better? What's happening exactly? I
> thought it was because of the fill factor .. however by default Sql
> server makes a fill factor of 90%... so shouldn't be that problem
> Thanks in advance for shedding a bit of light on this!
>|||In addition to the other posts:
How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> hello,
> I have a problem where certain indexes on an sql server 2005 database
> remain fragmented no matter how much rebuild I do on them.
> Why is this? and how can I rebuild the indexes correctly without
> fragmentation.. (I'm having about 80%) ?
> Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> field (the one having 80% fragmentation),
> when giving a fill factor of 10 or less the fragmentation is reduced
> to 11% ... while greater than 10 the fragmentation remains basically
> the same.
> So the thing is the less and the better? What's happening exactly? I
> thought it was because of the fill factor .. however by default Sql
> server makes a fill factor of 90%... so shouldn't be that problem
> Thanks in advance for shedding a bit of light on this!
>|||On Oct 29, 5:56 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> In addition to the other posts:
> How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Varangian" <ofm...@.gmail.com> wrote in message
> news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> > hello,
> > I have a problem where certain indexes on an sql server 2005 database
> > remain fragmented no matter how much rebuild I do on them.
> > Why is this? and how can I rebuild the indexes correctly without
> > fragmentation.. (I'm having about 80%) ?
> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> > field (the one having 80% fragmentation),
> > when giving a fill factor of 10 or less the fragmentation is reduced
> > to 11% ... while greater than 10 the fragmentation remains basically
> > the same.
> > So the thing is the less and the better? What's happening exactly? I
> > thought it was because of the fill factor .. however by default Sql
> > server makes a fill factor of 90%... so shouldn't be that problem
> > Thanks in advance for shedding a bit of light on this!
Yes ok the pages are 6... but anyhow still I want to know how to
resolve such issue as it may appear on a database with 500-1000
this is the dbcc showcontig on this table
DBCC SHOWCONTIG scanning 'Users' table...
Table: 'Users' (1748201278); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned........................: 6
- Extents Scanned.......................: 6
- Extent Switches.......................: 5
- Avg. Pages per Extent..................: 1.0
- Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
- Logical Scan Fragmentation ..............: 83.33%
- Extent Scan Fragmentation ...............: 83.33%
- Avg. Bytes Free per Page................: 1531.8
- Avg. Page Density (full)................: 81.07%
I changed the database size (as well as the log file) but nothing
happened.|||> Yes ok the pages are 6... but anyhow still I want to know how to
> resolve such issue as it may appear on a database with 500-1000
There's nothing to resolve. The first 8 pages from an index comes from shared extents. So, until you
have 8 pages, the pages can be allocated from any extent where there is free space. I.e, such an
index will be "fragmented", by nature of how storage allocation work in SQL server. So again,
exclude small indexes when you look at fragmentation.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193681980.777348.73060@.v3g2000hsg.googlegroups.com...
> On Oct 29, 5:56 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> In addition to the other posts:
>> How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
>> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Varangian" <ofm...@.gmail.com> wrote in message
>> news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
>> > hello,
>> > I have a problem where certain indexes on an sql server 2005 database
>> > remain fragmented no matter how much rebuild I do on them.
>> > Why is this? and how can I rebuild the indexes correctly without
>> > fragmentation.. (I'm having about 80%) ?
>> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
>> > field (the one having 80% fragmentation),
>> > when giving a fill factor of 10 or less the fragmentation is reduced
>> > to 11% ... while greater than 10 the fragmentation remains basically
>> > the same.
>> > So the thing is the less and the better? What's happening exactly? I
>> > thought it was because of the fill factor .. however by default Sql
>> > server makes a fill factor of 90%... so shouldn't be that problem
>> > Thanks in advance for shedding a bit of light on this!
>
> Yes ok the pages are 6... but anyhow still I want to know how to
> resolve such issue as it may appear on a database with 500-1000
> this is the dbcc showcontig on this table
> DBCC SHOWCONTIG scanning 'Users' table...
> Table: 'Users' (1748201278); index ID: 1, database ID: 6
> TABLE level scan performed.
> - Pages Scanned........................: 6
> - Extents Scanned.......................: 6
> - Extent Switches.......................: 5
> - Avg. Pages per Extent..................: 1.0
> - Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
> - Logical Scan Fragmentation ..............: 83.33%
> - Extent Scan Fragmentation ...............: 83.33%
> - Avg. Bytes Free per Page................: 1531.8
> - Avg. Page Density (full)................: 81.07%
>
> I changed the database size (as well as the log file) but nothing
> happened.
>|||On Oct 29, 8:18 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Yes ok the pages are 6... but anyhow still I want to know how to
> > resolve such issue as it may appear on a database with 500-1000
> There's nothing to resolve. The first 8 pages from an index comes from shared extents. So, until you
> have 8 pages, the pages can be allocated from any extent where there is free space. I.e, such an
> index will be "fragmented", by nature of how storage allocation work in SQL server. So again,
> exclude small indexes when you look at fragmentation.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Varangian" <ofm...@.gmail.com> wrote in message
> news:1193681980.777348.73060@.v3g2000hsg.googlegroups.com...
> > On Oct 29, 5:56 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> In addition to the other posts:
> >> How many pages`We frequently see similar posts here and it turns out that the index is only 3-4
> >> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "Varangian" <ofm...@.gmail.com> wrote in message
> >>news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
> >> > hello,
> >> > I have a problem where certain indexes on an sql server 2005 database
> >> > remain fragmented no matter how much rebuild I do on them.
> >> > Why is this? and how can I rebuild the indexes correctly without
> >> > fragmentation.. (I'm having about 80%) ?
> >> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
> >> > field (the one having 80% fragmentation),
> >> > when giving a fill factor of 10 or less the fragmentation is reduced
> >> > to 11% ... while greater than 10 the fragmentation remains basically
> >> > the same.
> >> > So the thing is the less and the better? What's happening exactly? I
> >> > thought it was because of the fill factor .. however by default Sql
> >> > server makes a fill factor of 90%... so shouldn't be that problem
> >> > Thanks in advance for shedding a bit of light on this!
> > Yes ok the pages are 6... but anyhow still I want to know how to
> > resolve such issue as it may appear on a database with 500-1000
> > this is the dbcc showcontig on this table
> > DBCC SHOWCONTIG scanning 'Users' table...
> > Table: 'Users' (1748201278); index ID: 1, database ID: 6
> > TABLE level scan performed.
> > - Pages Scanned........................: 6
> > - Extents Scanned.......................: 6
> > - Extent Switches.......................: 5
> > - Avg. Pages per Extent..................: 1.0
> > - Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
> > - Logical Scan Fragmentation ..............: 83.33%
> > - Extent Scan Fragmentation ...............: 83.33%
> > - Avg. Bytes Free per Page................: 1531.8
> > - Avg. Page Density (full)................: 81.07%
> > I changed the database size (as well as the log file) but nothing
> > happened.
Tibor .. ok but when the pages will be 8 and over what shall I do?
this means that it will be slower over a period of time. It's like
saying that no-one has control over this type of fragmentation|||There's no need to worry about fragmentation for small indexes. If it isn't hot (not frequently
accessed), then if it is small and you rarely access it, why worry. If it is hot (often accesses)
and small it will likely stay in cache and fragmentation is only an issue when pages are read from
disk.
For larger indexes, then you want to care about fragmentation. There are ways to defragment an
index. Check out:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Varangian" <ofmars@.gmail.com> wrote in message
news:1193685964.003127.81220@.k79g2000hse.googlegroups.com...
> On Oct 29, 8:18 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> > Yes ok the pages are 6... but anyhow still I want to know how to
>> > resolve such issue as it may appear on a database with 500-1000
>> There's nothing to resolve. The first 8 pages from an index comes from shared extents. So, until
>> you
>> have 8 pages, the pages can be allocated from any extent where there is free space. I.e, such an
>> index will be "fragmented", by nature of how storage allocation work in SQL server. So again,
>> exclude small indexes when you look at fragmentation.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Varangian" <ofm...@.gmail.com> wrote in message
>> news:1193681980.777348.73060@.v3g2000hsg.googlegroups.com...
>> > On Oct 29, 5:56 pm, "Tibor Karaszi"
>> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> >> In addition to the other posts:
>> >> How many pages`We frequently see similar posts here and it turns out that the index is only
>> >> 3-4
>> >> pages or so. Never worry about fragmentation unless you have at least some 500 to 1000 pages.
>> >> --
>> >> Tibor Karaszi, SQL Server
>> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
>> >> "Varangian" <ofm...@.gmail.com> wrote in message
>> >>news:1193671848.217677.90960@.v3g2000hsg.googlegroups.com...
>> >> > hello,
>> >> > I have a problem where certain indexes on an sql server 2005 database
>> >> > remain fragmented no matter how much rebuild I do on them.
>> >> > Why is this? and how can I rebuild the indexes correctly without
>> >> > fragmentation.. (I'm having about 80%) ?
>> >> > Something weird I noticed is when I'm using the DBCC DBREINDEX on this
>> >> > field (the one having 80% fragmentation),
>> >> > when giving a fill factor of 10 or less the fragmentation is reduced
>> >> > to 11% ... while greater than 10 the fragmentation remains basically
>> >> > the same.
>> >> > So the thing is the less and the better? What's happening exactly? I
>> >> > thought it was because of the fill factor .. however by default Sql
>> >> > server makes a fill factor of 90%... so shouldn't be that problem
>> >> > Thanks in advance for shedding a bit of light on this!
>> > Yes ok the pages are 6... but anyhow still I want to know how to
>> > resolve such issue as it may appear on a database with 500-1000
>> > this is the dbcc showcontig on this table
>> > DBCC SHOWCONTIG scanning 'Users' table...
>> > Table: 'Users' (1748201278); index ID: 1, database ID: 6
>> > TABLE level scan performed.
>> > - Pages Scanned........................: 6
>> > - Extents Scanned.......................: 6
>> > - Extent Switches.......................: 5
>> > - Avg. Pages per Extent..................: 1.0
>> > - Scan Density [Best Count:Actual Count]......: 16.67% [1:6]
>> > - Logical Scan Fragmentation ..............: 83.33%
>> > - Extent Scan Fragmentation ...............: 83.33%
>> > - Avg. Bytes Free per Page................: 1531.8
>> > - Avg. Page Density (full)................: 81.07%
>> > I changed the database size (as well as the log file) but nothing
>> > happened.
> Tibor .. ok but when the pages will be 8 and over what shall I do?
> this means that it will be slower over a period of time. It's like
> saying that no-one has control over this type of fragmentation
>