Poor performance after clustered index rebuild

Hi,
On SQL SERVER 2000 SP4, I have a table with 1 013 000 rows. Primary key is a
INTEGER with auto-increment.
For the first time this week-end, the clustered index for primary key on
this table has been rebuild (DBCC DBREINDEX). Before rebuild, all query on
this table was fast. After rebuild, a simple SELECT without ORDER BY on this
table is very slow.
Why a rebuild on clustered index can result in poor performance ?
Can you help me ?
Thanks
Is the Auto-Shrink option on?
what is the output of
DBCC SHOWCONTIG (<tablename>)
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
"shwac" wrote:

> Hi,
> On SQL SERVER 2000 SP4, I have a table with 1 013 000 rows. Primary key is a
> INTEGER with auto-increment.
> For the first time this week-end, the clustered index for primary key on
> this table has been rebuild (DBCC DBREINDEX). Before rebuild, all query on
> this table was fast. After rebuild, a simple SELECT without ORDER BY on this
> table is very slow.
> Why a rebuild on clustered index can result in poor performance ?
> Can you help me ?
> Thanks
>
>
|||Hi,
Auto-shrink option is off.
Output of DBCC (Copy/past in notepad for good presentation):
ObjectName ObjectId IndexName IndexId Level
Pages Rows MinimumRecordSize MaximumRecordSize
AverageRecordSizeForwardedRecords Extents ExtentSwitches
AverageFreeBytes AveragePageDensity ScanDensity BestCount
ActualCount LogicalFragmentation ExtentFragmentation
-- -- -- --
-- -- -- --
-- --- --
-- -- --
-- -- --
Even 117575457 PK_Even 1 0
171804 NULL NULL NULL
NULL NULL 0 21510 NULL
NULL 99.8372925479987 21476 21511 0.0
NULL
Even 117575457 IDX_Id_Unique_Even 9 0
2890 NULL NULL NULL
NULL NULL 0 362 NULL
NULL 99.724517906336089 362 363 0.0
NULL
Even 117575457 FK_Cd_Type_Appel_Even 10 0
1221 NULL NULL NULL
NULL NULL 0 153 NULL
NULL 99.350649350649363 153 154 0.0
NULL
Even 117575457 FK_Cd_Type_Even_Even 11 0
1221 NULL NULL NULL
NULL NULL 0 153 NULL
NULL 99.350649350649363 153 154 0.0
NULL
Even 117575457 FK_Cd_Direct_Even 12 0
1334 NULL NULL NULL
NULL NULL 0 167 NULL
NULL 99.404761904761912 167 168 0.0
NULL
Even 117575457 FK_No_Vehi_Even 13 0
1555 NULL NULL NULL
NULL NULL 0 194 NULL
NULL 100.0 195 195 0.0
NULL
Even 117575457 FK_Cd_Grav_Even_Even 14 0
1221 NULL NULL NULL
NULL NULL 0 153 NULL
NULL 99.350649350649363 153 154 0.0
NULL
Even 117575457 FK_No_Categ_Even 15 0
1221 NULL NULL NULL
NULL NULL 0 153 NULL
NULL 99.350649350649363 153 154 0.0
NULL
Even 117575457 FK_No_Empl_Log_Even 16 0
1555 NULL NULL NULL
NULL NULL 0 194 NULL
NULL 100.0 195 195 0.0
NULL
Even 117575457 FK_Cd_Cent_Trans_Even 17 0
2059 NULL NULL NULL
NULL NULL 0 258 NULL
NULL 99.613899613899619 258 259 0.0
NULL
Even 117575457 IDX_Tourn_Horaire_Even 18 0
2823 NULL NULL NULL
NULL NULL 0 354 NULL
NULL 99.436619718309856 353 355 0.0
NULL
Even 117575457 FK_Serv_Even 19 0
2233 NULL NULL NULL
NULL NULL 0 280 NULL
NULL 99.644128113879006 280 281 0.0
NULL
Even 117575457 FK_No_Ligne_Even 20 0
1334 NULL NULL NULL
NULL NULL 0 167 NULL
NULL 99.404761904761912 167 168 0.0
NULL
Even 117575457 FK_No_Empl_Init_Even 21 0
1555 NULL NULL NULL
NULL NULL 0 194 NULL
NULL 100.0 195 195 0.0
NULL
Even 117575457 FK_No_Ligne_Dom_Even 30 0
1334 NULL NULL NULL
NULL NULL 0 167 NULL
NULL 99.404761904761912 167 168 0.0
NULL
Even 117575457 FK_No_Empl_Sign_Even 31 0
1555 NULL NULL NULL
NULL NULL 0 194 NULL
NULL 100.0 195 195 0.0
NULL
Even 117575457 IX_Tourn_Horaire_Ligne_Dom_Even 32 0
2823 NULL NULL NULL
NULL NULL 0 354 NULL
NULL 99.436619718309856 353 355 0.0
NULL
Even 117575457 IX_Hr_Fin_Even 34 0
19967 NULL NULL NULL
NULL NULL 0 2503 NULL
NULL 99.680511182108617 2496 2504 0.0
NULL
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> Is the Auto-Shrink option on?
> what is the output of
> DBCC SHOWCONTIG (<tablename>)
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> "shwac" wrote:
|||Did you try DBCC INDEXDEFRAG?
"shwac" wrote:
[vbcol=seagreen]
> Hi,
> Auto-shrink option is off.
> Output of DBCC (Copy/past in notepad for good presentation):
> ObjectName ObjectId IndexName IndexId Level
> Pages Rows MinimumRecordSize MaximumRecordSize
> AverageRecordSizeForwardedRecords Extents ExtentSwitches
> AverageFreeBytes AveragePageDensity ScanDensity BestCount
> ActualCount LogicalFragmentation ExtentFragmentation
> -- -- -- --
> -- -- -- --
> -- --- --
> -- -- --
> -- -- --
> --
> Even 117575457 PK_Even 1 0
> 171804 NULL NULL NULL
> NULL NULL 0 21510 NULL
> NULL 99.8372925479987 21476 21511 0.0
> NULL
> Even 117575457 IDX_Id_Unique_Even 9 0
> 2890 NULL NULL NULL
> NULL NULL 0 362 NULL
> NULL 99.724517906336089 362 363 0.0
> NULL
> Even 117575457 FK_Cd_Type_Appel_Even 10 0
> 1221 NULL NULL NULL
> NULL NULL 0 153 NULL
> NULL 99.350649350649363 153 154 0.0
> NULL
> Even 117575457 FK_Cd_Type_Even_Even 11 0
> 1221 NULL NULL NULL
> NULL NULL 0 153 NULL
> NULL 99.350649350649363 153 154 0.0
> NULL
> Even 117575457 FK_Cd_Direct_Even 12 0
> 1334 NULL NULL NULL
> NULL NULL 0 167 NULL
> NULL 99.404761904761912 167 168 0.0
> NULL
> Even 117575457 FK_No_Vehi_Even 13 0
> 1555 NULL NULL NULL
> NULL NULL 0 194 NULL
> NULL 100.0 195 195 0.0
> NULL
> Even 117575457 FK_Cd_Grav_Even_Even 14 0
> 1221 NULL NULL NULL
> NULL NULL 0 153 NULL
> NULL 99.350649350649363 153 154 0.0
> NULL
> Even 117575457 FK_No_Categ_Even 15 0
> 1221 NULL NULL NULL
> NULL NULL 0 153 NULL
> NULL 99.350649350649363 153 154 0.0
> NULL
> Even 117575457 FK_No_Empl_Log_Even 16 0
> 1555 NULL NULL NULL
> NULL NULL 0 194 NULL
> NULL 100.0 195 195 0.0
> NULL
> Even 117575457 FK_Cd_Cent_Trans_Even 17 0
> 2059 NULL NULL NULL
> NULL NULL 0 258 NULL
> NULL 99.613899613899619 258 259 0.0
> NULL
> Even 117575457 IDX_Tourn_Horaire_Even 18 0
> 2823 NULL NULL NULL
> NULL NULL 0 354 NULL
> NULL 99.436619718309856 353 355 0.0
> NULL
> Even 117575457 FK_Serv_Even 19 0
> 2233 NULL NULL NULL
> NULL NULL 0 280 NULL
> NULL 99.644128113879006 280 281 0.0
> NULL
> Even 117575457 FK_No_Ligne_Even 20 0
> 1334 NULL NULL NULL
> NULL NULL 0 167 NULL
> NULL 99.404761904761912 167 168 0.0
> NULL
> Even 117575457 FK_No_Empl_Init_Even 21 0
> 1555 NULL NULL NULL
> NULL NULL 0 194 NULL
> NULL 100.0 195 195 0.0
> NULL
> Even 117575457 FK_No_Ligne_Dom_Even 30 0
> 1334 NULL NULL NULL
> NULL NULL 0 167 NULL
> NULL 99.404761904761912 167 168 0.0
> NULL
> Even 117575457 FK_No_Empl_Sign_Even 31 0
> 1555 NULL NULL NULL
> NULL NULL 0 194 NULL
> NULL 100.0 195 195 0.0
> NULL
> Even 117575457 IX_Tourn_Horaire_Ligne_Dom_Even 32 0
> 2823 NULL NULL NULL
> NULL NULL 0 354 NULL
> NULL 99.436619718309856 353 355 0.0
> NULL
> Even 117575457 IX_Hr_Fin_Even 34 0
> 19967 NULL NULL NULL
> NULL NULL 0 2503 NULL
> NULL 99.680511182108617 2496 2504 0.0
> NULL
>
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
|||Result DBCC INDEXDEFRAG:
Pages Scanned Pages Moved Pages Removed
-- -- --
171804 0 0
I drop index and recreate it and I still have the problem:
create unique clustered index PK_Even on Even (No_Even)
with fillfactor = 10, drop_Existing
on [data]
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> Did you try DBCC INDEXDEFRAG?
> "shwac" wrote:
|||Is there any reason why you are using:
fillfactor = 10
?
If not, I would try 90 or 80
Please refer to -> http://msdn2.microsoft.com/en-us/library/ms177459.aspx
for reference about FILL FACTOR
"shwac" wrote:
[vbcol=seagreen]
> Result DBCC INDEXDEFRAG:
> Pages Scanned Pages Moved Pages Removed
> -- -- --
> 171804 0 0
> I drop index and recreate it and I still have the problem:
> create unique clustered index PK_Even on Even (No_Even)
> with fillfactor = 10, drop_Existing
> on [data]
>
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
|||Hi,
I think I reverse the value for this option, I put 10 instead of 90.
Examples in Books Online are not very clear on the syntax for this command.
I will do some tests and come back to you for result.
Thanks.
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> Is there any reason why you are using:
> fillfactor = 10
> ?
> If not, I would try 90 or 80
> Please refer to -> http://msdn2.microsoft.com/en-us/library/ms177459.aspx
> for reference about FILL FACTOR
>
> "shwac" wrote:
|||It was my problem... a bad fillfactor ! Before reindexation, the fillfactor
was 90. With DBREINDEX, the fillfactor become 10. I have a lot of table with
this case, so I will change it for all tables.
Thanks a lot for help and tips.
S.P.
"shwac" wrote:
[vbcol=seagreen]
> Hi,
> I think I reverse the value for this option, I put 10 instead of 90.
> Examples in Books Online are not very clear on the syntax for this command.
> I will do some tests and come back to you for result.
> Thanks.
>
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
sql

Poor performance after clustered index rebuild

Hi,
On SQL SERVER 2000 SP4, I have a table with 1 013 000 rows. Primary key is a
INTEGER with auto-increment.
For the first time this week-end, the clustered index for primary key on
this table has been rebuild (DBCC DBREINDEX). Before rebuild, all query on
this table was fast. After rebuild, a simple SELECT without ORDER BY on this
table is very slow.
Why a rebuild on clustered index can result in poor performance ?
Can you help me ?
ThanksIs the Auto-Shrink option on?
what is the output of
DBCC SHOWCONTIG (<tablename> )
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
"shwac" wrote:

> Hi,
> On SQL SERVER 2000 SP4, I have a table with 1 013 000 rows. Primary key is
a
> INTEGER with auto-increment.
> For the first time this week-end, the clustered index for primary key on
> this table has been rebuild (DBCC DBREINDEX). Before rebuild, all query on
> this table was fast. After rebuild, a simple SELECT without ORDER BY on th
is
> table is very slow.
> Why a rebuild on clustered index can result in poor performance ?
> Can you help me ?
> Thanks
>
>|||Hi,
Auto-shrink option is off.
Output of DBCC (Copy/past in notepad for good presentation):
ObjectName ObjectId IndexName IndexId Level
Pages Rows MinimumRecordSize MaximumRecordSize
AverageRecordSizeForwardedRecords Extents ExtentSwitches
AverageFreeBytes AveragePageDensity ScanDensity BestCount
ActualCount LogicalFragmentation ExtentFragmentation
-- -- -- --
-- -- -- --
-- --- --
-- -- --
-- -- --
--
Even 117575457 PK_Even 1 0
171804 NULL NULL NULL
NULL NULL 0 21510 NULL
NULL 99.8372925479987 21476 21511 0.0
NULL
Even 117575457 IDX_Id_Unique_Even 9 0
2890 NULL NULL NULL
NULL NULL 0 362 NULL
NULL 99.724517906336089 362 363 0.0
NULL
Even 117575457 FK_Cd_Type_Appel_Even 10 0
1221 NULL NULL NULL
NULL NULL 0 153 NULL
NULL 99.350649350649363 153 154 0.0
NULL
Even 117575457 FK_Cd_Type_Even_Even 11 0
1221 NULL NULL NULL
NULL NULL 0 153 NULL
NULL 99.350649350649363 153 154 0.0
NULL
Even 117575457 FK_Cd_Direct_Even 12 0
1334 NULL NULL NULL
NULL NULL 0 167 NULL
NULL 99.404761904761912 167 168 0.0
NULL
Even 117575457 FK_No_Vehi_Even 13 0
1555 NULL NULL NULL
NULL NULL 0 194 NULL
NULL 100.0 195 195 0.0
NULL
Even 117575457 FK_Cd_Grav_Even_Even 14 0
1221 NULL NULL NULL
NULL NULL 0 153 NULL
NULL 99.350649350649363 153 154 0.0
NULL
Even 117575457 FK_No_Categ_Even 15 0
1221 NULL NULL NULL
NULL NULL 0 153 NULL
NULL 99.350649350649363 153 154 0.0
NULL
Even 117575457 FK_No_Empl_Log_Even 16 0
1555 NULL NULL NULL
NULL NULL 0 194 NULL
NULL 100.0 195 195 0.0
NULL
Even 117575457 FK_Cd_Cent_Trans_Even 17 0
2059 NULL NULL NULL
NULL NULL 0 258 NULL
NULL 99.613899613899619 258 259 0.0
NULL
Even 117575457 IDX_Tourn_Horaire_Even 18 0
2823 NULL NULL NULL
NULL NULL 0 354 NULL
NULL 99.436619718309856 353 355 0.0
NULL
Even 117575457 FK_Serv_Even 19 0
2233 NULL NULL NULL
NULL NULL 0 280 NULL
NULL 99.644128113879006 280 281 0.0
NULL
Even 117575457 FK_No_Ligne_Even 20 0
1334 NULL NULL NULL
NULL NULL 0 167 NULL
NULL 99.404761904761912 167 168 0.0
NULL
Even 117575457 FK_No_Empl_Init_Even 21 0
1555 NULL NULL NULL
NULL NULL 0 194 NULL
NULL 100.0 195 195 0.0
NULL
Even 117575457 FK_No_Ligne_Dom_Even 30 0
1334 NULL NULL NULL
NULL NULL 0 167 NULL
NULL 99.404761904761912 167 168 0.0
NULL
Even 117575457 FK_No_Empl_Sign_Even 31 0
1555 NULL NULL NULL
NULL NULL 0 194 NULL
NULL 100.0 195 195 0.0
NULL
Even 117575457 IX_Tourn_Horaire_Ligne_Dom_Even 32 0
2823 NULL NULL NULL
NULL NULL 0 354 NULL
NULL 99.436619718309856 353 355 0.0
NULL
Even 117575457 IX_Hr_Fin_Even 34 0
19967 NULL NULL NULL
NULL NULL 0 2503 NULL
NULL 99.680511182108617 2496 2504 0.0
NULL
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> Is the Auto-Shrink option on?
> what is the output of
> DBCC SHOWCONTIG (<tablename> )
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS
> "shwac" wrote:
>|||Did you try DBCC INDEXDEFRAG?
"shwac" wrote:
[vbcol=seagreen]
> Hi,
> Auto-shrink option is off.
> Output of DBCC (Copy/past in notepad for good presentation):
> ObjectName ObjectId IndexName IndexId Level
> Pages Rows MinimumRecordSize MaximumRecordSize
> AverageRecordSizeForwardedRecords Extents ExtentSwitches
> AverageFreeBytes AveragePageDensity ScanDensity BestCount
> ActualCount LogicalFragmentation ExtentFragmentation
> -- -- -- --
> -- -- -- --
> -- --- --
> -- -- --
> -- -- --
> --
> Even 117575457 PK_Even 1 0
> 171804 NULL NULL NULL
> NULL NULL 0 21510 NULL
> NULL 99.8372925479987 21476 21511 0.0
> NULL
> Even 117575457 IDX_Id_Unique_Even 9 0
> 2890 NULL NULL NULL
> NULL NULL 0 362 NULL
> NULL 99.724517906336089 362 363 0.0
> NULL
> Even 117575457 FK_Cd_Type_Appel_Even 10 0
> 1221 NULL NULL NULL
> NULL NULL 0 153 NULL
> NULL 99.350649350649363 153 154 0.0
> NULL
> Even 117575457 FK_Cd_Type_Even_Even 11 0
> 1221 NULL NULL NULL
> NULL NULL 0 153 NULL
> NULL 99.350649350649363 153 154 0.0
> NULL
> Even 117575457 FK_Cd_Direct_Even 12 0
> 1334 NULL NULL NULL
> NULL NULL 0 167 NULL
> NULL 99.404761904761912 167 168 0.0
> NULL
> Even 117575457 FK_No_Vehi_Even 13 0
> 1555 NULL NULL NULL
> NULL NULL 0 194 NULL
> NULL 100.0 195 195 0.0
> NULL
> Even 117575457 FK_Cd_Grav_Even_Even 14 0
> 1221 NULL NULL NULL
> NULL NULL 0 153 NULL
> NULL 99.350649350649363 153 154 0.0
> NULL
> Even 117575457 FK_No_Categ_Even 15 0
> 1221 NULL NULL NULL
> NULL NULL 0 153 NULL
> NULL 99.350649350649363 153 154 0.0
> NULL
> Even 117575457 FK_No_Empl_Log_Even 16 0
> 1555 NULL NULL NULL
> NULL NULL 0 194 NULL
> NULL 100.0 195 195 0.0
> NULL
> Even 117575457 FK_Cd_Cent_Trans_Even 17 0
> 2059 NULL NULL NULL
> NULL NULL 0 258 NULL
> NULL 99.613899613899619 258 259 0.0
> NULL
> Even 117575457 IDX_Tourn_Horaire_Even 18 0
> 2823 NULL NULL NULL
> NULL NULL 0 354 NULL
> NULL 99.436619718309856 353 355 0.0
> NULL
> Even 117575457 FK_Serv_Even 19 0
> 2233 NULL NULL NULL
> NULL NULL 0 280 NULL
> NULL 99.644128113879006 280 281 0.0
> NULL
> Even 117575457 FK_No_Ligne_Even 20 0
> 1334 NULL NULL NULL
> NULL NULL 0 167 NULL
> NULL 99.404761904761912 167 168 0.0
> NULL
> Even 117575457 FK_No_Empl_Init_Even 21 0
> 1555 NULL NULL NULL
> NULL NULL 0 194 NULL
> NULL 100.0 195 195 0.0
> NULL
> Even 117575457 FK_No_Ligne_Dom_Even 30 0
> 1334 NULL NULL NULL
> NULL NULL 0 167 NULL
> NULL 99.404761904761912 167 168 0.0
> NULL
> Even 117575457 FK_No_Empl_Sign_Even 31 0
> 1555 NULL NULL NULL
> NULL NULL 0 194 NULL
> NULL 100.0 195 195 0.0
> NULL
> Even 117575457 IX_Tourn_Horaire_Ligne_Dom_Even 32 0
> 2823 NULL NULL NULL
> NULL NULL 0 354 NULL
> NULL 99.436619718309856 353 355 0.0
> NULL
> Even 117575457 IX_Hr_Fin_Even 34 0
> 19967 NULL NULL NULL
> NULL NULL 0 2503 NULL
> NULL 99.680511182108617 2496 2504 0.0
> NULL
>
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
>|||Result DBCC INDEXDEFRAG:
Pages Scanned Pages Moved Pages Removed
-- -- --
171804 0 0
I drop index and recreate it and I still have the problem:
create unique clustered index PK_Even on Even (No_Even)
with fillfactor = 10, drop_Existing
on [data]
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> Did you try DBCC INDEXDEFRAG?
> "shwac" wrote:
>|||Is there any reason why you are using:
fillfactor = 10
?
If not, I would try 90 or 80
Please refer to -> http://msdn2.microsoft.com/en-us/library/ms177459.aspx
for reference about FILL FACTOR
"shwac" wrote:
[vbcol=seagreen]
> Result DBCC INDEXDEFRAG:
> Pages Scanned Pages Moved Pages Removed
> -- -- --
> 171804 0 0
> I drop index and recreate it and I still have the problem:
> create unique clustered index PK_Even on Even (No_Even)
> with fillfactor = 10, drop_Existing
> on [data]
>
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
>|||Hi,
I think I reverse the value for this option, I put 10 instead of 90.
Examples in Books Online are not very clear on the syntax for this command.
I will do some tests and come back to you for result.
Thanks.
"Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
[vbcol=seagreen]
> Is there any reason why you are using:
> fillfactor = 10
> ?
> If not, I would try 90 or 80
> Please refer to -> http://msdn2.microsoft.com/en-us/library/ms177459.aspx
> for reference about FILL FACTOR
>
> "shwac" wrote:
>|||It was my problem... a bad fillfactor ! Before reindexation, the fillfactor
was 90. With DBREINDEX, the fillfactor become 10. I have a lot of table with
this case, so I will change it for all tables.
Thanks a lot for help and tips.
S.P.
"shwac" wrote:
[vbcol=seagreen]
> Hi,
> I think I reverse the value for this option, I put 10 instead of 90.
> Examples in Books Online are not very clear on the syntax for this command
.
> I will do some tests and come back to you for result.
> Thanks.
>
> "Edgardo Valdez, MCTS, MCITP, MCSD, MCDBA" wrote:
>