SQL compact edition: index

Showing posts with label index. 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

poor index performance

Hey guys,

Having some trouble with indexes on sql server 2005. I'll explain it with a simplified example.
I have a customers table, and a sp to list customers :

create table Customers(
CusID int not null,
Name varchar(50) null,
Surname varchar(50) null,
CusNo int not null,
Deleted bit not null
)

create proc spCusLs (
@.CusID int = null,
@.Name varchar(50) = null,
@.Surname varchar(50) = null,
@.CusNo int = null
)
as

select
CusID,
Name,
Surname,
CusNo
from
Customers
where
Deleted = 0
and CusID <> 1000
and (@.CusID is null or CusID = @.CusID)
and (@.CusNo is null or CusNo = @.CusNo)
and (@.Name is null or Name like @.Name)
and (@.Surname is null or Surname like @.Surname)
order by
Name,
Surname

create nonclustered index ix_customers_name on customers ([name] asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_surname on customers (surname asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

create nonclustered index ix_customers_cusno on customers (cusno asc)
with (sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off) on primary

I've recently noticed that some tables, including 'Customers' don't have indexes except primary keys. And I have added indexes to "name", "surname" and "cusno" columns. This has dropped the number of IO reads. But the strange thing is; one time it works with name / surname searches like ('joh%' '%') but when CusNo is included, it does a full scan. And vice versa when the SP is recompiled using 'alter', works ok with CusNo, but not with name/surname. Recompile it, and it's reversed again. When run as a single query, the execution plan looks different.

What's happening? Perhaps something to do with statistics? This doesn't have a big payload on the server, but there are some other procs suffering from this on heavy queries, making server performance worse than before...

You will get the best performance if you can create a "covering" index for this query, which is a non-clustered index that includes all of the columns needed to satisfy or "cover" the query.

In this case, I would try a unique, non-clustered index on Deleted, CusID, CusNo, Name, and SurName (all of these in a single NC index). You may have to play around with the order of the columns in this index (based on their selectivity) to get the best results.

Also, the fact that you are using OR and LIKE in your WHERE clause will cause performance issues. I would consider splitting this into two SP's instead of trying to use an "all-purpose" SP for this.

sql

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

Monday, March 12, 2012

Pls help. Cannot index the view...It contains one or more disallowed constructs

Thank you for your help.
What am I missing here?
I read the BOL. I just dont see what is the problem.
SET ANSI_PADDING ON
GO
SET ANSI_PADDING ON
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OrderLineSAItems]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[OrderLineSAItems] (
[olnID] [int] NOT NULL ,
[saiID] [int] NOT NULL ,
[olnsQuantity] [int] NOT NULL
CONSTRAINT [PK_OrderLineSAItems_olnID] PRIMARY KEY CLUSTERED
(
[olnID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OrderLineOptions]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[OrderLineOptions] (
[olnID] [int] NOT NULL ,
[optID] [int] NOT NULL ,
[olnoValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[olnoEngValue] [decimal](19, 6) NULL ,
CONSTRAINT [PK_OrderLineOptions_olnID_optID] PRIMARY KEY CLUSTERED
(
[olnID],
[optID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
IF OBJECT_ID('dbo.OrderLineOptionChecksums') IS NOT NULL and
objectproperty(OBJECT_ID('dbo.OrderLineOptionChecksums'),'IsView') = 1
DROP VIEW dbo.OrderLineOptionChecksums
GO
CREATE VIEW dbo.OrderLineOptionChecksums
WITH SCHEMABINDING
AS
SELECT
olns.olnID
,CHECKSUM_AGG(
binary_checksum (
olns.saiID
, olno.optID
, olno.olnoValue
, olno.olnoEngValue
)
)
as OptionsChecksum
,count_big(*) as CntBig
FROM dbo.OrderLineSAItems olns
JOIN dbo.OrderLineOptions olno ON olno.olnID = olns.olnID
GROUP BY olns.olnID
GO
CREATE UNIQUE CLUSTERED INDEX UX_OrderLineOptionChecksums_olnID ON
dbo.OrderLineOptionChecksums ( olnID )
go
CREATE NONCLUSTERED INDEX IX_OrderLineOptionChecksums_OptionsCheck
sum ON
dbo.OrderLineOptionChecksums ( OptionsChecksum )
go
-- $Log$
GOThank you, Jacco, for your explanation. It's not what I wanted to hear
though. I think it's also quite a limitation. That means that I need to use
trigger to maintain this checksum.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>I think the problem is with CHECKSUM_AGG. The idea behind the
>implementation of indexed views is that when a row is
>inserted/updated/deleted in one of the underlying tables, the new values in
>the indexed views can be calculated from just the changes in the underlying
>tables, without having to access any other rows in the table(s). I don't
>think this is the case with CHECKSUM_AGG, or in other words, if for example
>you delete a row from a table, you can't calculate the new value for the
>CHECKSUM_AGG by deducting the checksum for the deleted row from the value
>that was in the view previously. You would have to access the other rows in
>the table to recalculate the checksum.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23YeZeqyUFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||Jacco
I was just reading more on indexed views and I have a question.
How would you explain
SUM(X), COUNT_BIG(X)
being allowed then? It has to go and re-read all other rows to get a new sum
if a row is deleted/updated/inserted.
Thanks
Vlad
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>I think the problem is with CHECKSUM_AGG. The idea behind the
>implementation of indexed views is that when a row is
>inserted/updated/deleted in one of the underlying tables, the new values in
>the indexed views can be calculated from just the changes in the underlying
>tables, without having to access any other rows in the table(s). I don't
>think this is the case with CHECKSUM_AGG, or in other words, if for example
>you delete a row from a table, you can't calculate the new value for the
>CHECKSUM_AGG by deducting the checksum for the deleted row from the value
>that was in the view previously. You would have to access the other rows in
>the table to recalculate the checksum.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23YeZeqyUFHA.1044@.TK2MSFTNGP10.phx.gbl...
>|||No, because if you add a row (or multiple rows) to a base table for an
indexed view you can calculate the new values for SUM and BIG_COUNT from the
existing values for SUM and BIG_COUNT and the values for the inserted
row(s).
If you have a table
CREATE TABLE t(i INT IDENTITY PRIMARY KEY, v INT NOT NULL)
with an indexed view SELECT SUM(v) as sum_v, COUNT_BIG(*) as cnt
If you have 2 rows in the table:
1,2 and 2,3
The indexed view will have the values: 5, 2
When you insert another row into the table with v = 4, you can update the
view by adding 4 to the value for sum_v that is already there (5), and
increasing the value for cnt by 1. There is no need to revisit the rows that
are already in the table. This doesn't work for CHECKSUM_AGG though.
Jacco Schalkwijk
SQL Server MVP
"Farmer" <someone@.somewhere.com> wrote in message
news:%23IgX3PYVFHA.544@.TK2MSFTNGP15.phx.gbl...
> Jacco
> I was just reading more on indexed views and I have a question.
> How would you explain
> SUM(X), COUNT_BIG(X)
> being allowed then? It has to go and re-read all other rows to get a new
> sum if a row is deleted/updated/inserted.
> Thanks
> Vlad
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23eiAV$0UFHA.3076@.TK2MSFTNGP12.phx.gbl...
>|||Thank you.
Your explanation makes total sense and your logic is very sound.
However, I am still disapointed that this is the case, even though you are
right, that SQL does not deal with this problem.
It would have been such a powerful feature.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:OvC5WzYVFHA.3544@.TK2MSFTNGP12.phx.gbl...
> No, because if you add a row (or multiple rows) to a base table for an
> indexed view you can calculate the new values for SUM and BIG_COUNT from
> the existing values for SUM and BIG_COUNT and the values for the inserted
> row(s).
> If you have a table
> CREATE TABLE t(i INT IDENTITY PRIMARY KEY, v INT NOT NULL)
> with an indexed view SELECT SUM(v) as sum_v, COUNT_BIG(*) as cnt
> If you have 2 rows in the table:
> 1,2 and 2,3
> The indexed view will have the values: 5, 2
> When you insert another row into the table with v = 4, you can update the
> view by adding 4 to the value for sum_v that is already there (5), and
> increasing the value for cnt by 1. There is no need to revisit the rows
> that are already in the table. This doesn't work for CHECKSUM_AGG though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23IgX3PYVFHA.544@.TK2MSFTNGP15.phx.gbl...
>|||Yes, but it wouldn't have been possible to have all the performance
optimizations that indexed views provide if constructs like CHECKSUM_AGG
were allowed. And you can still do what you want with a non-indexed view or
a trigger.
Jacco Schalkwijk
SQL Server MVP
"Farmer" <someone@.somewhere.com> wrote in message
news:efPG8QZVFHA.548@.tk2msftngp13.phx.gbl...
> Thank you.
> Your explanation makes total sense and your logic is very sound.
> However, I am still disapointed that this is the case, even though you are
> right, that SQL does not deal with this problem.
> It would have been such a powerful feature.
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:OvC5WzYVFHA.3544@.TK2MSFTNGP12.phx.gbl...
>

Wednesday, March 28, 2012

Poor performance after clustered index rebuild

Poor performance after clustered index rebuild

Poor performance after clustered index rebuild

poor index performance

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

Monday, March 12, 2012

Pls help. Cannot index the view...It contains one or more disallowed constructs

SQL compact edition

Blog Archive

About Me