SQL compact edition: rows

Showing posts with label rows. Show all posts

Wednesday, March 28, 2012

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 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:
> 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
> >
> >
> >
> >|||Did you try DBCC INDEXDEFRAG?
"shwac" wrote:
> 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:
> > 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
> > >
> > >
> > >
> > >|||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:
> Did you try DBCC INDEXDEFRAG?
> "shwac" wrote:
> > 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:
> >
> > > 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
> > > >
> > > >
> > > >
> > > >|||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:
> 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:
> > Did you try DBCC INDEXDEFRAG?
> >
> > "shwac" wrote:
> >
> > > 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:
> > >
> > > > 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,
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:
> 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:
> > 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:
> >
> > > Did you try DBCC INDEXDEFRAG?
> > >
> > > "shwac" wrote:
> > >
> > > > 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:
> > > >
> > > > > 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
> > > > > >
> > > > > >
> > > > > >
> > > > > >|||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:
> 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:
> > 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:
> >
> > > 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:
> > >
> > > > Did you try DBCC INDEXDEFRAG?
> > > >
> > > > "shwac" wrote:
> > > >
> > > > > 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:
> > > > >
> > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >

Poor performance after clustered index rebuild

Monday, March 26, 2012

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

[ABSMain].[dbo].[FAXNET].[faxnet0]))
| |--Table Spool
| |--Clustered Index
Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[faxnet00]),
WHERE

[FAXNET].[DataSetID]=57242))
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[faxnet000]))
| |--Table Spool
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[IX_FAXNET]))
| |--Table Spool
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[IDXZIP]))
| |--Table Spool
|--Assert(WHERE

If NOT(([Expr1006] IS NULL)) then 0 else NULL))
| |--Nested Loops(Left Semi Join, OUTER
REFERENCES

[FAXNET].[UniqueID]), DEFINE

[Expr1006] = [PROBE VALUE]))
| |--Sort(ORDER BY

[FAXNET].[UniqueID] ASC))
| | |--Index
Delete(OBJECT

[MailData].[FaxNetID]=[FAXNET].[UniqueID]))
| |--Clustered Index
Scan(OBJECT

[ABSMain].[dbo].[MailData].[PK_MailData]))
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[NODUPE]))
| |--Table Spool
|--Index
Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
|--Table Spool
|||Does the execution plan not show until the query/statement is
completed?
I'm trying to run this DELETE and it is taking forever, but also, I
don't see an execution plan in the Execution Plan pane - I would think
that the execution plan would be completed before the query actually
begins.
Thanks,
Wes
|||Gert,
Thanks. That makes perfect sense. Putting an index on the MailData
column sped things up dramatically (there were over 268000 records in
MailData, so scanning them was taking a while).
Thanks again,
Wes

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

All,
MS SQL Server 2000
Win 2K SP2
I'm trying to get a better handle on why the performance on a
particular DELETE statement is so poor.
Query: DELETE FROM FAXNET WHERE DataSetID = ?
Here is some basic information on my table:
FAXNET table Size: 3.1M (million) rows
Clustered index on DataSetID
Distinct values of DataSetID in table: 4305
7 other indices on the table including the built-in one for the primary
key, as well as a UNIQUE index on 5 columns
Unusual symptoms:
If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
quickly as possible, do my DELETE, it takes under 5 seconds. However,
if there is any significant delay between the UPDATE STATISTICS command
and the execution of the DELETE, the statement takes minutes (at least
5 - I haven't actually waited for it to complete).
Is the fact that I'm deleting based on the clustered index column value
significant (I'm pretty sure it's significant, but not sure how)?
Does anything about this scenario seem like expected results based on
the table and query setup?
How can I get better feedback on how this is being processed - what is
the best way to look into it? I attempted to look at execution plans
etc. but I haven't done that stuff on MS SQL Server ever (although I
used to be quite good at it 10 years ago on Informix :]).
We also see brutal lock contention for this query with SELECTs being
blocked after about two minutes into the execution. I've definitely
seen an exclusive table lock acquired due by this statement - can
anyone explain why?
Is there any standard maintenance that should be done - does the
clustered index need to be rebuilt periodically? I noticed that both
the "auto create statistics" and "auto update statistics" DB options
were set to ON for this database, so I don't feel like I need to
recommend UPDATE STATISTICS (am I dating myself yet?).
Any suggestions would be appreciated.
Thanks,
Wes Gamble> Is there any standard maintenance that should be done - does the
> clustered index need to be rebuilt periodically? I noticed that both
> the "auto create statistics" and "auto update statistics" DB options
> were set to ON for this database, so I don't feel like I need to
> recommend UPDATE STATISTICS (am I dating myself yet?).
Yes , it is a good practice to rebuild CI periodically
Do you have a CI UNIQUE on DataSetId column? If you don't SQL Server will
rebuild all NCI ( because it has CI Keys as pointers to the actual data)
and it takes time
You may want to INSERT the data ( you do not want to delete) INTO a new
table and then delete the old table and rename a new tabe as an old one
"weyus" <wesgamble@.gmail.com> wrote in message
news:1162943286.659963.188050@.e3g2000cwe.googlegroups.com...
> All,
> MS SQL Server 2000
> Win 2K SP2
> I'm trying to get a better handle on why the performance on a
> particular DELETE statement is so poor.
> Query: DELETE FROM FAXNET WHERE DataSetID = ?
> Here is some basic information on my table:
> FAXNET table Size: 3.1M (million) rows
> Clustered index on DataSetID
> Distinct values of DataSetID in table: 4305
> 7 other indices on the table including the built-in one for the primary
> key, as well as a UNIQUE index on 5 columns
> Unusual symptoms:
> If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
> quickly as possible, do my DELETE, it takes under 5 seconds. However,
> if there is any significant delay between the UPDATE STATISTICS command
> and the execution of the DELETE, the statement takes minutes (at least
> 5 - I haven't actually waited for it to complete).
> Is the fact that I'm deleting based on the clustered index column value
> significant (I'm pretty sure it's significant, but not sure how)?
> Does anything about this scenario seem like expected results based on
> the table and query setup?
> How can I get better feedback on how this is being processed - what is
> the best way to look into it? I attempted to look at execution plans
> etc. but I haven't done that stuff on MS SQL Server ever (although I
> used to be quite good at it 10 years ago on Informix :]).
> We also see brutal lock contention for this query with SELECTs being
> blocked after about two minutes into the execution. I've definitely
> seen an exclusive table lock acquired due by this statement - can
> anyone explain why?
> Is there any standard maintenance that should be done - does the
> clustered index need to be rebuilt periodically? I noticed that both
> the "auto create statistics" and "auto update statistics" DB options
> were set to ON for this database, so I don't feel like I need to
> recommend UPDATE STATISTICS (am I dating myself yet?).
> Any suggestions would be appreciated.
> Thanks,
> Wes Gamble
>|||> If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
> quickly as possible, do my DELETE, it takes under 5 seconds. However,
> if there is any significant delay between the UPDATE STATISTICS command
> and the execution of the DELETE, the statement takes minutes (at least
> 5 - I haven't actually waited for it to complete).
This suggests that you get different execution plans for the delete operations. However, the DELETE
you mention is very straight forward, so it does sound a bit strange. I'd start by determine with
100% certainty that the update of statistics does change things and also check the execution plans.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"weyus" <wesgamble@.gmail.com> wrote in message
news:1162943286.659963.188050@.e3g2000cwe.googlegroups.com...
> All,
> MS SQL Server 2000
> Win 2K SP2
> I'm trying to get a better handle on why the performance on a
> particular DELETE statement is so poor.
> Query: DELETE FROM FAXNET WHERE DataSetID = ?
> Here is some basic information on my table:
> FAXNET table Size: 3.1M (million) rows
> Clustered index on DataSetID
> Distinct values of DataSetID in table: 4305
> 7 other indices on the table including the built-in one for the primary
> key, as well as a UNIQUE index on 5 columns
> Unusual symptoms:
> If I execute "UPDATE STATISTICS FAXNET WITH FULLSCAN" and then as
> quickly as possible, do my DELETE, it takes under 5 seconds. However,
> if there is any significant delay between the UPDATE STATISTICS command
> and the execution of the DELETE, the statement takes minutes (at least
> 5 - I haven't actually waited for it to complete).
> Is the fact that I'm deleting based on the clustered index column value
> significant (I'm pretty sure it's significant, but not sure how)?
> Does anything about this scenario seem like expected results based on
> the table and query setup?
> How can I get better feedback on how this is being processed - what is
> the best way to look into it? I attempted to look at execution plans
> etc. but I haven't done that stuff on MS SQL Server ever (although I
> used to be quite good at it 10 years ago on Informix :]).
> We also see brutal lock contention for this query with SELECTs being
> blocked after about two minutes into the execution. I've definitely
> seen an exclusive table lock acquired due by this statement - can
> anyone explain why?
> Is there any standard maintenance that should be done - does the
> clustered index need to be rebuilt periodically? I noticed that both
> the "auto create statistics" and "auto update statistics" DB options
> were set to ON for this database, so I don't feel like I need to
> recommend UPDATE STATISTICS (am I dating myself yet?).
> Any suggestions would be appreciated.
> Thanks,
> Wes Gamble
>|||Tibor Karaszi wrote:
> This suggests that you get different execution plans for the delete operations. However, the DELETE
> you mention is very straight forward, so it does sound a bit strange. I'd start by determine with
> 100% certainty that the update of statistics does change things and also check the execution plans.
What is the best way to check execution plans for DELETE statements?
Wes|||> What is the best way to check execution plans for DELETE statements?
Same as for a SELECT statement. In query Analyzer either select "display estimated execution plan"
or check "show actual plan" (and execute the query. Or, catch the plan in Profiler.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"weyus" <wesgamble@.gmail.com> wrote in message
news:1163010846.268748.290020@.f16g2000cwb.googlegroups.com...
> Tibor Karaszi wrote:
>> This suggests that you get different execution plans for the delete operations. However, the
>> DELETE
>> you mention is very straight forward, so it does sound a bit strange. I'd start by determine with
>> 100% certainty that the update of statistics does change things and also check the execution
>> plans.
> What is the best way to check execution plans for DELETE statements?
> Wes
>|||Tibor Karaszi wrote:
> > What is the best way to check execution plans for DELETE statements?
> Same as for a SELECT statement. In query Analyzer either select "display estimated execution plan"
> or check "show actual plan" (and execute the query. Or, catch the plan in Profiler.
I see the same execution plan regardless of whether I run UPDATE
STATISTICS on the FAXNET table or not.
The execution plan is below - can anyone help me decipher it - does it
look prohibitively expensive assuming 3M+ rows in the FAXNET table?
Thanks,
Wes
=========================================
|--Sequence
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet0]))
| |--Table Spool
| |--Clustered Index
Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet00]),
WHERE:([FAXNET].[DataSetID]=57242))
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet000]))
| |--Table Spool
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IX_FAXNET]))
| |--Table Spool
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IDXZIP]))
| |--Table Spool
|--Assert(WHERE:(If NOT(([Expr1006] IS NULL)) then 0 else NULL))
| |--Nested Loops(Left Semi Join, OUTER
REFERENCES:([FAXNET].[UniqueID]), DEFINE:([Expr1006] = [PROBE VALUE]))
| |--Sort(ORDER BY:([FAXNET].[UniqueID] ASC))
| | |--Index
Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[PK_FAXNET]))
| | |--Table Spool
| |--Row Count Spool
| |--Index
Spool(SEEK:([MailData].[FaxNetID]=[FAXNET].[UniqueID]))
| |--Clustered Index
Scan(OBJECT:([ABSMain].[dbo].[MailData].[PK_MailData]))
|--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[NODUPE]))
| |--Table Spool
|--Index
Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
|--Table Spool|||Does the execution plan not show until the query/statement is
completed?
I'm trying to run this DELETE and it is taking forever, but also, I
don't see an execution plan in the Execution Plan pane - I would think
that the execution plan would be completed before the query actually
begins.
Thanks,
Wes|||Wes,
There is a foreign key constraint from MailData.FaxNetID to
FAXNET.UniqueID (or vice versa). It seems, this column
MailData(FaxNetID) is not indexed. It could speed up your query
significantly (as could a reduction in the amount of indexes on FAXNET).
The query plan in the poorly performing situation must be different. The
estimated query plan might be enough. Wrap the statement in SET
SHOWPLAN_TEXT ON and SET SHOWPLAN_TEXT OFF and you should get it.
HTH,
Gert-Jan
weyus wrote:
> Tibor Karaszi wrote:
> > > What is the best way to check execution plans for DELETE statements?
> >
> > Same as for a SELECT statement. In query Analyzer either select "display estimated execution plan"
> > or check "show actual plan" (and execute the query. Or, catch the plan in Profiler.
> I see the same execution plan regardless of whether I run UPDATE
> STATISTICS on the FAXNET table or not.
> The execution plan is below - can anyone help me decipher it - does it
> look prohibitively expensive assuming 3M+ rows in the FAXNET table?
> Thanks,
> Wes
> =========================================> |--Sequence
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet0]))
> | |--Table Spool
> | |--Clustered Index
> Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet00]),
> WHERE:([FAXNET].[DataSetID]=57242))
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[faxnet000]))
> | |--Table Spool
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IX_FAXNET]))
> | |--Table Spool
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[IDXZIP]))
> | |--Table Spool
> |--Assert(WHERE:(If NOT(([Expr1006] IS NULL)) then 0 else NULL))
> | |--Nested Loops(Left Semi Join, OUTER
> REFERENCES:([FAXNET].[UniqueID]), DEFINE:([Expr1006] = [PROBE VALUE]))
> | |--Sort(ORDER BY:([FAXNET].[UniqueID] ASC))
> | | |--Index
> Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[PK_FAXNET]))
> | | |--Table Spool
> | |--Row Count Spool
> | |--Index
> Spool(SEEK:([MailData].[FaxNetID]=[FAXNET].[UniqueID]))
> | |--Clustered Index
> Scan(OBJECT:([ABSMain].[dbo].[MailData].[PK_MailData]))
> |--Index Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[NODUPE]))
> | |--Table Spool
> |--Index
> Delete(OBJECT:([ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
> |--Table Spool|||Gert,
Thanks. That makes perfect sense. Putting an index on the MailData
column sped things up dramatically (there were over 268000 records in
MailData, so scanning them was taking a while).
Thanks again,
Wes

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

[ABSMain].[dbo].[FAXNET].[faxnet0]))
| |--Table Spool
| |--Clustered Index
Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[faxnet00]),
WHERE

[FAXNET].[DataSetID]=57242))
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[faxnet000]
))
| |--Table Spool
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[IX_FAXNET]
))
| |--Table Spool
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[IDXZIP]))
| |--Table Spool
|--Assert(WHERE

If NOT(([Expr1006] IS NULL)) then 0 else NULL))
| |--Nested Loops(Left Semi Join, OUTER
REFERENCES

[FAXNET].[UniqueID]), DEFINE

[Expr1006] = [PROB
E VALUE]))
| |--Sort(ORDER BY

[FAXNET].[UniqueID] ASC))
| | |--Index
Delete(OBJECT

[MailData].[FaxNetID]=[FAXNET].[UniqueID]))
| |--Clustered Index
Scan(OBJECT

[ABSMain].[dbo].[MailData].[PK_MailData]))
|--Index Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[NODUPE]))
| |--Table Spool
|--Index
Delete(OBJECT

[ABSMain].[dbo].[FAXNET].[idxMergeInsert]))
|--Table Spool|||Does the execution plan not show until the query/statement is
completed?
I'm trying to run this DELETE and it is taking forever, but also, I
don't see an execution plan in the Execution Plan pane - I would think
that the execution plan would be completed before the query actually
begins.
Thanks,
Wes|||Gert,
Thanks. That makes perfect sense. Putting an index on the MailData
column sped things up dramatically (there were over 268000 records in
MailData, so scanning them was taking a while).
Thanks again,
Wes

Tuesday, March 20, 2012

plus/minus icon

hi,
the plus/minus icon is showing the inverse of the current state of the group
i.e. if the group is open (meaning that the sub rows are visible) the icon
shows as plus instead of minus, and vice versa.
my report is defaulting to the hidden=false option, and probably this is
why. So how can i have my report default to a full view and have the
plus/minus icon show correctly? or is this a bug or something?This can be a little tricky to get set up.
Take a look at the Visibility tab on the Advanced Textbox Properties dialog.
It has a option to control the initial appearance of the toggle item.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Emmanuel" <emmanuel@.email.com> wrote in message
news:OXGYewIpEHA.2784@.TK2MSFTNGP14.phx.gbl...
> hi,
> the plus/minus icon is showing the inverse of the current state of the
group
> i.e. if the group is open (meaning that the sub rows are visible) the icon
> shows as plus instead of minus, and vice versa.
> my report is defaulting to the hidden=false option, and probably this is
> why. So how can i have my report default to a full view and have the
> plus/minus icon show correctly? or is this a bug or something?
>

Monday, March 12, 2012

PLS Help with query

Please help me with complicated query:
eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
value in "a" field is dublicate, but leave one of each. in simple sentence I
need to delete invert distinct of field "a".
Please help me with this query
Rows are NOT duplicated, just value in one of fields are
There are primary key in this table
Following the similar explanation of this table
log_id int(11) UNSIGNED auto_increment //ID
rnd_id int(11) //SOMETHING
option_id int(11) //SOMETHING
timestamp DateTime //SOMETHING
ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
host varchar(70) //SOMETHING
agent varchar(80) //SOMETHING
thxHi
You may want to try something like:
DELETE FROM MyTable
FROM MyTable A
WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
A.log_id > B.log_id )
or
DELETE FROM A
FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
B.log_id
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
> Please help me with complicated query:
> eg I have table(tbl) with 2 fields (a,b) I need to delete ALL rows where
> value in "a" field is dublicate, but leave one of each. in simple sentence
I
> need to delete invert distinct of field "a".
> Please help me with this query
> Rows are NOT duplicated, just value in one of fields are
> There are primary key in this table
> Following the similar explanation of this table
> log_id int(11) UNSIGNED auto_increment //ID
> rnd_id int(11) //SOMETHING
> option_id int(11) //SOMETHING
> timestamp DateTime //SOMETHING
> ip_addr varchar(15) //HERE ARE DUPLICATE VALUES
> host varchar(70) //SOMETHING
> agent varchar(80) //SOMETHING
> thx
>
>|||Thank you for response, but
As far as I see it will delete everything.
I need to leave one of each kind
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
> Hi
> You may want to try something like:
> DELETE FROM MyTable
> FROM MyTable A
> WHERE EXISTS ( SELECT * FROM MyTable B WHERE A.IP_addr = B.IP_addr and
> A.log_id > B.log_id )
> or
> DELETE FROM A
> FROM MyTable A JOIN MyTable B ON A.IP_addr = B.IP_addr and A.log_id >
> B.log_id
> John
> "Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
> news:O44xqCnMEHA.3712@.TK2MSFTNGP10.phx.gbl...
sentence[vbcol=seagreen]
> I
>|||Hi
It will only delete those that have a higher log_id and the same Ip address.
John
"Tamir Khason" <tamir-NOSPAM@.tcon-NOSPAM.co.il> wrote in message
news:Owbc3aoMEHA.3940@.tk2msftngp13.phx.gbl...
> Thank you for response, but
> As far as I see it will delete everything.
> I need to leave one of each kind
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:eu3vAvnMEHA.556@.tk2msftngp13.phx.gbl...
where[vbcol=seagreen]
> sentence
>|||Hi Tamir,
I noticed that the issue was posted in
microsoft.public.sqlserver.programming with the same title. Based on my
test, John's T-SQL statements hit the right answer.
Anyway, if you have follow up questions, please post there and I will be
glad to work with you.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

Wednesday, March 28, 2012

Poor performance after clustered index rebuild

Poor performance after clustered index rebuild

Poor performance after clustered index rebuild

Monday, March 26, 2012

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

Poor DELETE performance - clustered index, 3M+ rows, unusual symptoms

Tuesday, March 20, 2012

plus/minus icon

Monday, March 12, 2012

PLS Help with query

SQL compact edition

Blog Archive

About Me