Index fragmentation issue - SQL2005

Hi,
We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem
where our indexes are going from 100% unfragmented to 30% unfragmented
(According to dbcc showcontig) in the space of 10 minutes when we insert
data.
Now, obviously we'll get some fragmenation and we re-index overnight but the
speed the fragmentation occurs so quickly it makes our new server slower
than our old one.
How can I track down why these tables are fragmenting so quickly?Nick wrote:
> Hi,
> We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem
> where our indexes are going from 100% unfragmented to 30% unfragmented
> (According to dbcc showcontig) in the space of 10 minutes when we insert
> data.
> Now, obviously we'll get some fragmenation and we re-index overnight but the
> speed the fragmentation occurs so quickly it makes our new server slower
> than our old one.
> How can I track down why these tables are fragmenting so quickly?
>
How big (how many pages) are these indexes?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The biggest table (There are 3 with issues) is just over 8000 pages.
Our index proc has run overnight, and still one of the tables shows 30% scan
density - issuing a dbcc reindex then put it back to 99%. I don't understand
how the reindex doesn't affect it one time and then it does the next!
The tables are on a filegroup that is split over 3 files - this was to help
performance. It's a 64-bit AMD box with storage on a NetApp filer. SP1
applied with all hotfixes I could find!
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:453F9977.5060504@.realsqlguy.com...
> Nick wrote:
>> Hi,
>> We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem
>> where our indexes are going from 100% unfragmented to 30% unfragmented
>> (According to dbcc showcontig) in the space of 10 minutes when we insert
>> data.
>> Now, obviously we'll get some fragmenation and we re-index overnight but
>> the speed the fragmentation occurs so quickly it makes our new server
>> slower than our old one.
>> How can I track down why these tables are fragmenting so quickly?
> How big (how many pages) are these indexes?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know whether it applies to this particular situation, but scan density doesn't give
meaningful information if the filegroup has several files. "Jumping back and forth between the
files" counts as fragmentation. This is why scan density isn't presented in the now recommended way
to check fragmentation sys.dm_db_index_physical_stats. Google for Paul Randall and suitable keywords
and you will probably find some more technicalities on the subject.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nick" <no.thanks@.nowhere.com> wrote in message news:eBWQPPM%23GHA.4388@.TK2MSFTNGP02.phx.gbl...
> The biggest table (There are 3 with issues) is just over 8000 pages.
> Our index proc has run overnight, and still one of the tables shows 30% scan density - issuing a
> dbcc reindex then put it back to 99%. I don't understand how the reindex doesn't affect it one
> time and then it does the next!
> The tables are on a filegroup that is split over 3 files - this was to help performance. It's a
> 64-bit AMD box with storage on a NetApp filer. SP1 applied with all hotfixes I could find!
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:453F9977.5060504@.realsqlguy.com...
>> Nick wrote:
>> Hi,
>> We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem where our indexes are
>> going from 100% unfragmented to 30% unfragmented (According to dbcc showcontig) in the space of
>> 10 minutes when we insert data.
>> Now, obviously we'll get some fragmenation and we re-index overnight but the speed the
>> fragmentation occurs so quickly it makes our new server slower than our old one.
>> How can I track down why these tables are fragmenting so quickly?
>> How big (how many pages) are these indexes?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Thanks Tibor; I've started using the new function as you suggest.
Unfortunatley, the fragmentaion is still bad but at least this might give me
a clue and pointer in the right direction.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eMR%23wCN%23GHA.2408@.TK2MSFTNGP05.phx.gbl...
>I don't know whether it applies to this particular situation, but scan
>density doesn't give meaningful information if the filegroup has several
>files. "Jumping back and forth between the files" counts as fragmentation.
>This is why scan density isn't presented in the now recommended way to
>check fragmentation sys.dm_db_index_physical_stats. Google for Paul Randall
>and suitable keywords and you will probably find some more technicalities
>on the subject.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Nick" <no.thanks@.nowhere.com> wrote in message
> news:eBWQPPM%23GHA.4388@.TK2MSFTNGP02.phx.gbl...
>> The biggest table (There are 3 with issues) is just over 8000 pages.
>> Our index proc has run overnight, and still one of the tables shows 30%
>> scan density - issuing a dbcc reindex then put it back to 99%. I don't
>> understand how the reindex doesn't affect it one time and then it does
>> the next!
>> The tables are on a filegroup that is split over 3 files - this was to
>> help performance. It's a 64-bit AMD box with storage on a NetApp filer.
>> SP1 applied with all hotfixes I could find!
>> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>> news:453F9977.5060504@.realsqlguy.com...
>> Nick wrote:
>> Hi,
>> We've upgraded to a SQL 2005 box from SQL 2000 and are having a problem
>> where our indexes are going from 100% unfragmented to 30% unfragmented
>> (According to dbcc showcontig) in the space of 10 minutes when we
>> insert data.
>> Now, obviously we'll get some fragmenation and we re-index overnight
>> but the speed the fragmentation occurs so quickly it makes our new
>> server slower than our old one.
>> How can I track down why these tables are fragmenting so quickly?
>> How big (how many pages) are these indexes?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>
>