Fragmented Indexes

I have a query to find all indexes that are fragmented over 10%. The query
also excludes any where the page count is less than 8 and the index size is
smaller than 64k. Immediately after running my maintenance script that
reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
returns quite a few fragmented indexes. I've also insured that all tables
have a clustered index (most of them on an identity column). Why do I still
show fragmented indexes? Is there some reason they all won't defragment?
Any help is greatly appreciated!Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
calculated, things like extent order etc. As the index gets to some realistic size, you will not be
affected by this "misreport".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>I have a query to find all indexes that are fragmented over 10%. The query
> also excludes any where the page count is less than 8 and the index size is
> smaller than 64k. Immediately after running my maintenance script that
> reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> returns quite a few fragmented indexes. I've also insured that all tables
> have a clustered index (most of them on an identity column). Why do I still
> show fragmented indexes? Is there some reason they all won't defragment?
> Any help is greatly appreciated!|||Thanks for your quick response! Any chance you have a link to a page that
shows the 1000 pages MS recommends? Any other MS recommendation pages?
"Tibor Karaszi" wrote:
> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only two
> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation is
> calculated, things like extent order etc. As the index gets to some realistic size, you will not be
> affected by this "misreport".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >I have a query to find all indexes that are fragmented over 10%. The query
> > also excludes any where the page count is less than 8 and the index size is
> > smaller than 64k. Immediately after running my maintenance script that
> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
> > returns quite a few fragmented indexes. I've also insured that all tables
> > have a clustered index (most of them on an identity column). Why do I still
> > show fragmented indexes? Is there some reason they all won't defragment?
> > Any help is greatly appreciated!
>|||Below might have to 1000 pages recommendation:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> Thanks for your quick response! Any chance you have a link to a page that
> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>
> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way fragmentation
>> is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will not
>> be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!|||Or then again, you might check this paper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
It specifically says:
Generally, you should not be concerned with fragmentation levels of indexes
with less than 1,000 pages. In the tests, indexes containing more than
10,000 pages realized performance gains, with the biggest gains on indexes
with significantly more pages (greater than 50,000 pages).
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Below might have to 1000 pages recommendation:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is due
>> to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do I
>> > still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>|||Thank you both for your input! I'll change my maintenance script, and I'm
sure you've just saved us several thousand reindexing jobs that aren't
necessary on a weekly basis! Thanks again!
"Kalen Delaney" wrote:
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes
> with less than 1,000 pages. In the tests, indexes containing more than
> 10,000 pages realized performance gains, with the biggest gains on indexes
> with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
> > Below might have to 1000 pages recommendation:
> > http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Tony" <Tony@.discussions.microsoft.com> wrote in message
> > news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
> >> Thanks for your quick response! Any chance you have a link to a page
> >> that
> >> shows the 1000 pages MS recommends? Any other MS recommendation pages?
> >>
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Ignore indexes with less than at least 500 pages (MS recommends 1000
> >> pages). You can, with only two
> >> extents, still have reported fragmentation after a rebuild. This is due
> >> to the way fragmentation is
> >> calculated, things like extent order etc. As the index gets to some
> >> realistic size, you will not be
> >> affected by this "misreport".
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
> >> >I have a query to find all indexes that are fragmented over 10%. The
> >> >query
> >> > also excludes any where the page count is less than 8 and the index
> >> > size is
> >> > smaller than 64k. Immediately after running my maintenance script
> >> > that
> >> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
> >> > query still
> >> > returns quite a few fragmented indexes. I've also insured that all
> >> > tables
> >> > have a clustered index (most of them on an identity column). Why do I
> >> > still
> >> > show fragmented indexes? Is there some reason they all won't
> >> > defragment?
> >> > Any help is greatly appreciated!
> >>
> >
>
>|||Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I have no idea why I posted
an URL to the I/O basic paper...
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
> Or then again, you might check this paper:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> It specifically says:
> Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000
> pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with
> the biggest gains on indexes with significantly more pages (greater than 50,000 pages).
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000 pages). You can, with only
>> two
>> extents, still have reported fragmentation after a rebuild. This is due to the way
>> fragmentation is
>> calculated, things like extent order etc. As the index gets to some realistic size, you will
>> not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The query
>> > also excludes any where the page count is less than 8 and the index size is
>> > smaller than 64k. Immediately after running my maintenance script that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above query still
>> > returns quite a few fragmented indexes. I've also insured that all tables
>> > have a clustered index (most of them on an identity column). Why do I still
>> > show fragmented indexes? Is there some reason they all won't defragment?
>> > Any help is greatly appreciated!
>>
>|||You probably have them all in a list, just like I do, and you grabbed the
wrong one. I just made absolutely sure that I grabbed the right one!
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23JZtwKMeIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Thanks Kalen. That was, of course, indeed the paper I meant to refer to. I
> have no idea why I posted an URL to the I/O basic paper...
> :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%238Hxd5LeIHA.4464@.TK2MSFTNGP02.phx.gbl...
>> Or then again, you might check this paper:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> It specifically says:
>> Generally, you should not be concerned with fragmentation levels of
>> indexes with less than 1,000 pages. In the tests, indexes containing more
>> than 10,000 pages realized performance gains, with the biggest gains on
>> indexes with significantly more pages (greater than 50,000 pages).
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23nSaZrLeIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Below might have to 1000 pages recommendation:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:BA4F1CD9-5AB3-431B-8E31-6BA38C8CEC12@.microsoft.com...
>> Thanks for your quick response! Any chance you have a link to a page
>> that
>> shows the 1000 pages MS recommends? Any other MS recommendation pages?
>>
>> "Tibor Karaszi" wrote:
>> Ignore indexes with less than at least 500 pages (MS recommends 1000
>> pages). You can, with only two
>> extents, still have reported fragmentation after a rebuild. This is
>> due to the way fragmentation is
>> calculated, things like extent order etc. As the index gets to some
>> realistic size, you will not be
>> affected by this "misreport".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:6F06ECB2-B999-4681-A238-CBE7193A429D@.microsoft.com...
>> >I have a query to find all indexes that are fragmented over 10%. The
>> >query
>> > also excludes any where the page count is less than 8 and the index
>> > size is
>> > smaller than 64k. Immediately after running my maintenance script
>> > that
>> > reorganizes/rebuilds indexes (using a threshhold of 30%) My above
>> > query still
>> > returns quite a few fragmented indexes. I've also insured that all
>> > tables
>> > have a clustered index (most of them on an identity column). Why do
>> > I still
>> > show fragmented indexes? Is there some reason they all won't
>> > defragment?
>> > Any help is greatly appreciated!
>>
>>
>