Performance degraded after DBCC DBReindex

Dear Sir,
I have been tried to Reindex all tables' indexs as I expected this can
improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
'',0). But after that, I found that the overall performance is actually
degraded and the disk I/O seems to be heavier also. The performance is
degraded for three days already, can any one think of any reason that can
explain this issue? I have another question for statistic, do I needed to
update statistics after the reindex?
Pls. help, thanks a lot!
Yours sincerely,
Henry
In my opinion it's very important to update sql stats (proc sp_updatestats)
because like all RDBMS those stats are used by MSSQL to optimize the way it
compute your query.
Philippe RUELLO
DBA MSSQL
|||stats will get auto recomputed for new indexes created when you run dbcc
dbreindex. . .if you have high IO after re-index then you need to
set statistics profile on
set showplan on
set statistic io on
and determine which tables are causing the procedures/queries to run slow. .
.. .
determine where table scans and/or (nested loop joins) are used and
establish if there are useful indexes on these columns.
HTH
"Henry" wrote:

> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>
|||It is possible the reindex forced the recompile of a plan and it was not
optimal for most of the queries. Run profiler to see if you have some bad
queries and try recompiling them.
Andrew J. Kelly SQL MVP
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
>
|||Since you have ran the dbcc reindex with 0, excessive IO signifies lot of
page splits.
so next time if u need to run dbcc reindex(u should have strong reason to
run this)
run this command seperate for each index depending upon its
usability/behavior(unique or non unique) and pass appropriate fillfactorto
avoid excessive page splits.
Thanks
--Harvinder
"Andrew J. Kelly" wrote:

> It is possible the reindex forced the recompile of a plan and it was not
> optimal for most of the queries. Run profiler to see if you have some bad
> queries and try recompiling them.
> --
> Andrew J. Kelly SQL MVP
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:BF0B8C2C-5183-4D32-ACF4-1AD870940995@.microsoft.com...
>
>
|||I am just rephrasing Harvinder here.
A fillfactor of 0 is really only useful for a read-only table. It will
leave absolutely no room for additional rows, or changes of rows that
causes the row to expand.
This means, that if inserts and update occur on the table after the
reindex with fillfactor=0, then page splits will occur. After that,
scanning the table will require more I/O, and because of fragmentation,
true sequential I/O will not be possible.
If you reindex regularly, then you can set the fillfactor accordingly.
For example, let's say 10% of the table is changed in one week. If you
reindex the table on a weekly basis, then a 10% fillfactor should work
quite well. It will avoid unnecessary page splits, because the
fillfactor will allow the table to grow (on average) 10 percent.
Hope this helps,
Gert-Jan
Henry wrote:
> Dear Sir,
> I have been tried to Reindex all tables' indexs as I expected this can
> improve the performance of query e.g. DBCC DBReindex ( 'dbo.<TableName>',
> '',0). But after that, I found that the overall performance is actually
> degraded and the disk I/O seems to be heavier also. The performance is
> degraded for three days already, can any one think of any reason that can
> explain this issue? I have another question for statistic, do I needed to
> update statistics after the reindex?
> Pls. help, thanks a lot!
> Yours sincerely,
> Henry
|||Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
the 0 in the script means just follow whatever the fillfactor being used
during the index being created? Will it automatically change to fill factor
to 0?
But any way, I will beware of it! Thx!
From,
Henry
"Gert-Jan Strik" wrote:

> I am just rephrasing Harvinder here.
> A fillfactor of 0 is really only useful for a read-only table. It will
> leave absolutely no room for additional rows, or changes of rows that
> causes the row to expand.
> This means, that if inserts and update occur on the table after the
> reindex with fillfactor=0, then page splits will occur. After that,
> scanning the table will require more I/O, and because of fragmentation,
> true sequential I/O will not be possible.
> If you reindex regularly, then you can set the fillfactor accordingly.
> For example, let's say 10% of the table is changed in one week. If you
> reindex the table on a weekly basis, then a 10% fillfactor should work
> quite well. It will avoid unnecessary page splits, because the
> fillfactor will allow the table to grow (on average) 10 percent.
> Hope this helps,
> Gert-Jan
>
> Henry wrote:
>
|||Thanks for your email, how do I identify the right percentage of sample data
to be used for updating the statistics? In general, is 30% enought?
From,
Henry
"Philippe RUELLO" wrote:

> In my opinion it's very important to update sql stats (proc sp_updatestats)
> because like all RDBMS those stats are used by MSSQL to optimize the way it
> compute your query.
> Philippe RUELLO
> DBA MSSQL
>
>
|||Henry wrote:
> Thanks for your reply. Isn't the DBCC DBReindex ( 'dbo.<Table Name>', '',0),
> the 0 in the script means just follow whatever the fillfactor being used
> during the index being created? Will it automatically change to fill factor
> to 0?
> But any way, I will beware of it! Thx!
> From,
> Henry
Yes, you are right. I forgot one little step in my explanation, and that
is that if you create an index without fill factor, then by default a
fill factor of 0 is used.
So if you created the indexes without fill factor, and run DBCC
DBREINDEX(..,.., 0), then at the leaf level there will be no space left
after reindexing, and in the index tree just one entry per page.
HTH,
Gert-Jan
sql