Extent fragmentation still high after index drop/create

[gary]

hello everyone,

we dropped the clustered & nonclustered indeces on a table, then
rebuilt them. logical fragmentation is near zero, but extent
fragmentation is about 40%. how can this be if the indeces are brand
new?

[Paul S Randal [MS]]

Here's a whitepaper you should read:
http://www.microsoft.com/technet/tre...e/ss2kidbp.asp

Also, what effect is the extent fragmentation having on the performance of
your workload? (i.e. do you have empricial evidence that the workload perf
is decreasing with increasing extent fragmentation - to justify the overhead
of these index operations?)

Regards,

Paul.

--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<ga**@dba723.net> wrote in message
news:fb*************************@posting.google.co m...

hello everyone,

we dropped the clustered & nonclustered indeces on a table, then
rebuilt them. logical fragmentation is near zero, but extent
fragmentation is about 40%. how can this be if the indeces are brand
new?

[gary]

Thanks for the response, Paul!

A particular query on my test server with logical and extent frag near
zero ran in about 12 seconds (very complex query). The same query on
my reporting box took over a minute, with some of the largest tables
included at about 40% extent frag, near zero logical frag.
"Paul S Randal [MS]" <pr*****@online.microsoft.com> wrote in message news:<3f********@news.microsoft.com>...

Here's a whitepaper you should read:
http://www.microsoft.com/technet/tre...e/ss2kidbp.asp

Also, what effect is the extent fragmentation having on the performance of
your workload? (i.e. do you have empricial evidence that the workload perf
is decreasing with increasing extent fragmentation - to justify the overhead
of these index operations?)

Regards,

Paul.

--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
<ga**@dba723.net> wrote in message
news:fb*************************@posting.google.co m...

hello everyone,

we dropped the clustered & nonclustered indeces on a table, then
rebuilt them. logical fragmentation is near zero, but extent
fragmentation is about 40%. how can this be if the indeces are brand
new?