DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?

am a bit confused about something. I am trying to solve a problem. My
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
.obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
--
Olu AdedejiDo you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
--
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>