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
Showing posts with label dbreindex. Show all posts
Showing posts with label dbreindex. Show all posts
Monday, March 26, 2012
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,
HenryIn 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...
> > 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
> >
>
>|||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:
> >
> > 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 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
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,
HenryIn 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...
> > 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
> >
>
>|||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:
> >
> > 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 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
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,
HenryIn 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 i
t
> 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 facto
r
> 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
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,
HenryIn 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 i
t
> 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 facto
r
> 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
Wednesday, March 21, 2012
Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000
In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David
|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan
.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan.
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David
|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan
.Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan
.>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>
Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000
In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds
,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan.
>
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds
,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan
.Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:[vbcol=seagreen]
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan
.>
>[quoted text clipped - 21 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
Andrew J. Kelly SQL MVP
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>
Performance comparison using Compability 8.0 on SQL 2005 vs. SQL 2000
In production we are running SQL Server 2000, SP4, on Windows 2003, using 4
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan:).
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003, using
>> 4
>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
>>Did you update the stats after the restore on the dev machine? If not the
>>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003,
>> using
>> 4
>>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
processors with 6 GB AWE Memory.
I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
all indexes.
A stored procedure with a complex query is timing out.
SP_Who shows that the procedure is not getting blocked, but if anything, is
doing the blocking.
We restore the production database to our DEV environment which is running
SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
The database from production, when restored, is left at Compatability 8.0.
When this same stored procedure is run in DEV it executes in under 2 seconds,
with an entirely different execution plan than production.
Is it possible, that the procedure, when run in DEV is utilizing some
components of SQL 2005 to build a better plan, even though the Compatibility
is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
>
Yes, in fact, it's certain. Even in 8.0 Compatibility mode you get get the
(enhanced) SQL Server 2005 query optimizer. In 8.0 Compatibility mode
you're still running SQL Server 2005.
David|||Did you update the stats after the restore on the dev machine? If not the
optimizer may not have the correct info even if it is a better plan:).
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69a40194e9cbc@.uwe...
> In production we are running SQL Server 2000, SP4, on Windows 2003, using
> 4
> processors with 6 GB AWE Memory.
> I run DBCC DBReindex on all indexes and Update Statistics With FullScan on
> all indexes.
> A stored procedure with a complex query is timing out.
> SP_Who shows that the procedure is not getting blocked, but if anything,
> is
> doing the blocking.
> We restore the production database to our DEV environment which is running
> SQL Server 2005, SP1, on Windows 2003, using 2 processors and 3 GB Memory.
> The database from production, when restored, is left at Compatability 8.0.
> When this same stored procedure is run in DEV it executes in under 2
> seconds,
> with an entirely different execution plan than production.
> Is it possible, that the procedure, when run in DEV is utilizing some
> components of SQL 2005 to build a better plan, even though the
> Compatibility
> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>|||I did not run update stats on the DEV machine, since it was a restore of a
database that just had updated stats run on it, and additionally, the
procedure ran like a champ on the DEV machine.
Andrew J. Kelly wrote:
>Did you update the stats after the restore on the dev machine? If not the
>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003, using
>> 4
>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1|||You should as a matter of practice update the stats after a restore
regardless of when they were ran last.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:69acf56bf722a@.uwe...
>I did not run update stats on the DEV machine, since it was a restore of a
> database that just had updated stats run on it, and additionally, the
> procedure ran like a champ on the DEV machine.
> Andrew J. Kelly wrote:
>>Did you update the stats after the restore on the dev machine? If not the
>>optimizer may not have the correct info even if it is a better plan:).
>> In production we are running SQL Server 2000, SP4, on Windows 2003,
>> using
>> 4
>>[quoted text clipped - 21 lines]
>> Compatibility
>> is left at 8.0?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>
Subscribe to:
Posts (Atom)