Microsoft states that dbcc DBREINDEX automatically updates statistics but INDEXDEFRAG does not. If this is the case, does MS mean that only the affected statistics are updated or all statistics? Also, is it a good idea to run 'Update Statistics' after doing INDEXDEFRAG?
CliveAll statistics for that table are updated on DBREINDEX. I would run UPDATE STATISTICS anytime after running INDEXDEFRAG.
Showing posts with label statistics. Show all posts
Showing posts with label statistics. Show all posts
Thursday, March 8, 2012
Wednesday, March 7, 2012
DBCC DBReindex and Update Statistics
Thanks for the replies. I think they all get updated
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van
>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>
On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van
>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>
On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>
DBCC DBReindex and Update Statistics
Does doing a DBReindex on each table also update all the
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
Van
Hi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
Thanks
Yogish
|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish
|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
Van
Hi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
Thanks
Yogish
|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish
|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
DBCC DBReindex and Update Statistics
Thanks for the replies. I think they all get updated
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van
>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van
>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>
DBCC DBReindex and Update Statistics
Does doing a DBReindex on each table also update all the
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
VanHi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
Thanks
Yogish|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
VanHi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
Thanks
Yogish|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
DBCC DBReindex and Update Statistics
Does doing a DBReindex on each table also update all the
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
VanHi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
--
Thanks
Yogish|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish|||Thanks for the replies. I think they all get updated
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van
>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>|||On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>
> >--Original Message--
> >Does doing a DBReindex on each table also update all the
> >statistics on that table as well? I think there can be
> >index specific statistics as well as non index related
> >statistics on tables. Do all of these get updated with
> >DBReindex?
> >
> >Thanks,
> >
> >Van
> >.
> >|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
statistics on that table as well? I think there can be
index specific statistics as well as non index related
statistics on tables. Do all of these get updated with
DBReindex?
Thanks,
VanHi,
Yes, dbcc dbreindex rebuilds the index and updates the statistics.
BTW, what do you mean by "I think there can be index specific statistics as
well as non index related statistics on tables." ? FYI, statistics are
created only on index.
--
Thanks
Yogish|||As far as I know all stats get updated after a dbcc dbreindex of the
clustered index. You can verify this yourself, by using the DBCC
SHOW_STATISTICS command.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:077401c52a66$9fecbc50$a501280a@.phx.gbl...
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van|||With autocreate stats option, SQL Server creates statistics on non-indexed
columns also.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Yogish" <yogishkamathg@.icqmail.com> wrote in message
news:5F149C27-5E46-4CE9-8C4F-C65F84E26988@.microsoft.com...
> Hi,
> Yes, dbcc dbreindex rebuilds the index and updates the statistics.
> BTW, what do you mean by "I think there can be index specific statistics
as
> well as non index related statistics on tables." ? FYI, statistics are
> created only on index.
> --
> Thanks
> Yogish|||Thanks for the replies. I think they all get updated
too. Basically I have a weekly maintenance job that
rebuilds all indexes with DBReindex and I was wondering if
I needed to set up a separate Update Statistics step in it
so that Statistics are updated weekly too. Sounds like
DBReindex will take care of it.
Thanks again,
Van
>--Original Message--
>Does doing a DBReindex on each table also update all the
>statistics on that table as well? I think there can be
>index specific statistics as well as non index related
>statistics on tables. Do all of these get updated with
>DBReindex?
>Thanks,
>Van
>.
>|||On some tables, on which query performance is very important to me, I
schedule UPDATE STATISTICS everyday, with a reindex every week. I do this,
even when auto stats are on.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:086101c52a6f$34788fe0$a401280a@.phx.gbl...
> Thanks for the replies. I think they all get updated
> too. Basically I have a weekly maintenance job that
> rebuilds all indexes with DBReindex and I was wondering if
> I needed to set up a separate Update Statistics step in it
> so that Statistics are updated weekly too. Sounds like
> DBReindex will take care of it.
> Thanks again,
> Van
>
> >--Original Message--
> >Does doing a DBReindex on each table also update all the
> >statistics on that table as well? I think there can be
> >index specific statistics as well as non index related
> >statistics on tables. Do all of these get updated with
> >DBReindex?
> >
> >Thanks,
> >
> >Van
> >.
> >|||If you specify the index in the DBCC DBREINDEX command, then only this
index (and statistics of that index) are updated. If you specify DBCC
DBREINDEX without an index name, then all indexes and all non-index
statistics of the table are updated.
HTH,
Gert-Jan
Van Jones wrote:
> Does doing a DBReindex on each table also update all the
> statistics on that table as well? I think there can be
> index specific statistics as well as non index related
> statistics on tables. Do all of these get updated with
> DBReindex?
> Thanks,
> Van
dbcc dbreindex and update of statistics
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?
Index stats are updated automatically when you do a DBCC DBREINDEX.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.comcast.com. ..
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?
|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/ar...A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?
Index stats are updated automatically when you do a DBCC DBREINDEX.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.comcast.com. ..
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?
|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/ar...A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
dbcc dbreindex and update of statistics
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?Index stats are updated automatically when you do a DBCC DBREINDEX.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.comcast.com...
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That looks like a great script..
I been trying to run dbcc show contig and then decide which ones need to
have dbcc redindex on them.
What the different from dbcc reindex and DBCC INDEXDEFRAG
is there a way to write this to a report so i can see which ones it does
during the process...thanks
"Tracy McKibben" wrote:
> Dodo Lurker wrote:
> > I have to set up a nightly job on my database to drop and reindex indexes
> > on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> > also update the statistics on the rebuilt index or do I have to then issue
> > an update statistics on the table?
> >
> >
> Consider using this script, it will rebuild only those indexes that are
> badly fragmented, and you can control the method used...
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?Index stats are updated automatically when you do a DBCC DBREINDEX.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.comcast.com...
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||That looks like a great script..
I been trying to run dbcc show contig and then decide which ones need to
have dbcc redindex on them.
What the different from dbcc reindex and DBCC INDEXDEFRAG
is there a way to write this to a report so i can see which ones it does
during the process...thanks
"Tracy McKibben" wrote:
> Dodo Lurker wrote:
> > I have to set up a nightly job on my database to drop and reindex indexes
> > on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
> > also update the statistics on the rebuilt index or do I have to then issue
> > an update statistics on the table?
> >
> >
> Consider using this script, it will rebuild only those indexes that are
> badly fragmented, and you can control the method used...
> http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
dbcc dbreindex and update of statistics
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?Index stats are updated automatically when you do a DBCC DBREINDEX.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.co
mcast.com...
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreinde
x
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/a...realsqlguy.com|||That looks like a great script..
I been trying to run dbcc show contig and then decide which ones need to
have dbcc redindex on them.
What the different from dbcc reindex and DBCC INDEXDEFRAG
is there a way to write this to a report so i can see which ones it does
during the process...thanks
"Tracy McKibben" wrote:
> Dodo Lurker wrote:
> Consider using this script, it will rebuild only those indexes that are
> badly fragmented, and you can control the method used...
> http://realsqlguy.com/serendipity/a.....htm
l
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?Index stats are updated automatically when you do a DBCC DBREINDEX.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Dodo Lurker" <none@.noemailplease> wrote in message
news:s76dnQMf_dnbF4nYnZ2dnUVZ_rqdnZ2d@.co
mcast.com...
I have to set up a nightly job on my database to drop and reindex indexes
on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreindex
also update the statistics on the rebuilt index or do I have to then issue
an update statistics on the table?|||Dodo Lurker wrote:
> I have to set up a nightly job on my database to drop and reindex indexes
> on my tables. I am going to use dbcc dbreindex. Does the dbcc dbreinde
x
> also update the statistics on the rebuilt index or do I have to then issue
> an update statistics on the table?
>
Consider using this script, it will rebuild only those indexes that are
badly fragmented, and you can control the method used...
http://realsqlguy.com/serendipity/a...realsqlguy.com|||That looks like a great script..
I been trying to run dbcc show contig and then decide which ones need to
have dbcc redindex on them.
What the different from dbcc reindex and DBCC INDEXDEFRAG
is there a way to write this to a report so i can see which ones it does
during the process...thanks
"Tracy McKibben" wrote:
> Dodo Lurker wrote:
> Consider using this script, it will rebuild only those indexes that are
> badly fragmented, and you can control the method used...
> http://realsqlguy.com/serendipity/a.....htm
l
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
Saturday, February 25, 2012
DBCC DBREINDEX and Statistics
In SQL Server 2000 when you create an Index, statistics are created with fullscan. My question is when you issue the DBCC DBREINDEX command, are statistics updated with a fullscan or the default sample size? If the answer is fullscan, then I am curious
as to why the DBCC DBREINDEX runs faster than an UPDATE STATISTICS WITH FULLSCAN?
Thanks
Donnie
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?
> Thanks
> Donnie
|||Yes, The reindex takes about 4 hours on the table, the update statistics with a fullscan was killed after 11 hours. The size of the table is about 200 million rows.
-- Paul S Randal [MS] wrote: --
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?[vbcol=seagreen]
|||Hi Donnie,
An index rebuild always has a stats recreate piggy-backed on top of it.
I can only guess that you have a concurrent workload while you're doing the
update statistics (which is an online operation), whereas the index rebuild
is effectively offline (as it takes a table S or X lock).
Also, an index rebuild will do an allocation-order scan of the data whereas
an update statistics is probably doing a logical-order scan. Does the index
have high logical scan fragmentation?
According to a colleague in the query optimizer team, a full scan update of
such a large index is a highly unusual operation. Are you certain that a
lower sampling rate is producing bad statistics?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:379598EC-0F22-4BC0-A070-B30B860090A3@.microsoft.com...
> Yes, The reindex takes about 4 hours on the table, the update statistics
with a fullscan was killed after 11 hours. The size of the table is about
200 million rows.
> -- Paul S Randal [MS] wrote: --
> Can you provide some timing details along with index size etc?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Donnie" <anonymous@.discussions.microsoft.com> wrote in message
> news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
with
> fullscan. My question is when you issue the DBCC DBREINDEX command,
are
> statistics updated with a fullscan or the default sample size? If
the
> answer is fullscan, then I am curious as to why the DBCC DBREINDEX
runs
> faster than an UPDATE STATISTICS WITH FULLSCAN?
>
>
|||Ok, That makes since as to why the Update Statistics takes longer.
Just so I understand, when a reindex is run, the statistics are re-created for the table that is being reindexed? Is that correct?
Thanks for your help. It is much appreciated.
Thanks
Donnie
|||Yes, correct. And you're most welcome.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:EB8918BF-B938-47A8-8D88-7E308D748340@.microsoft.com...
> Ok, That makes since as to why the Update Statistics takes longer.
> Just so I understand, when a reindex is run, the statistics are re-created
for the table that is being reindexed? Is that correct?
>
> Thanks for your help. It is much appreciated.
> Thanks
> Donnie
as to why the DBCC DBREINDEX runs faster than an UPDATE STATISTICS WITH FULLSCAN?
Thanks
Donnie
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?
> Thanks
> Donnie
|||Yes, The reindex takes about 4 hours on the table, the update statistics with a fullscan was killed after 11 hours. The size of the table is about 200 million rows.
-- Paul S Randal [MS] wrote: --
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?[vbcol=seagreen]
|||Hi Donnie,
An index rebuild always has a stats recreate piggy-backed on top of it.
I can only guess that you have a concurrent workload while you're doing the
update statistics (which is an online operation), whereas the index rebuild
is effectively offline (as it takes a table S or X lock).
Also, an index rebuild will do an allocation-order scan of the data whereas
an update statistics is probably doing a logical-order scan. Does the index
have high logical scan fragmentation?
According to a colleague in the query optimizer team, a full scan update of
such a large index is a highly unusual operation. Are you certain that a
lower sampling rate is producing bad statistics?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:379598EC-0F22-4BC0-A070-B30B860090A3@.microsoft.com...
> Yes, The reindex takes about 4 hours on the table, the update statistics
with a fullscan was killed after 11 hours. The size of the table is about
200 million rows.
> -- Paul S Randal [MS] wrote: --
> Can you provide some timing details along with index size etc?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Donnie" <anonymous@.discussions.microsoft.com> wrote in message
> news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
with
> fullscan. My question is when you issue the DBCC DBREINDEX command,
are
> statistics updated with a fullscan or the default sample size? If
the
> answer is fullscan, then I am curious as to why the DBCC DBREINDEX
runs
> faster than an UPDATE STATISTICS WITH FULLSCAN?
>
>
|||Ok, That makes since as to why the Update Statistics takes longer.
Just so I understand, when a reindex is run, the statistics are re-created for the table that is being reindexed? Is that correct?
Thanks for your help. It is much appreciated.
Thanks
Donnie
|||Yes, correct. And you're most welcome.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:EB8918BF-B938-47A8-8D88-7E308D748340@.microsoft.com...
> Ok, That makes since as to why the Update Statistics takes longer.
> Just so I understand, when a reindex is run, the statistics are re-created
for the table that is being reindexed? Is that correct?
>
> Thanks for your help. It is much appreciated.
> Thanks
> Donnie
DBCC DBREINDEX and Statistics
In SQL Server 2000 when you create an Index, statistics are created with ful
lscan. My question is when you issue the DBCC DBREINDEX command, are statis
tics updated with a fullscan or the default sample size? If the answer is f
ullscan, then I am curious
as to why the DBCC DBREINDEX runs faster than an UPDATE STATISTICS WITH FULL
SCAN?
Thanks
DonnieCan you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?
> Thanks
> Donnie|||Yes, The reindex takes about 4 hours on the table, the update statistics wit
h a fullscan was killed after 11 hours. The size of the table is about 200
million rows.
-- Paul S Randal [MS] wrote: --
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?[vbcol=seagreen]|||Hi Donnie,
An index rebuild always has a stats recreate piggy-backed on top of it.
I can only guess that you have a concurrent workload while you're doing the
update statistics (which is an online operation), whereas the index rebuild
is effectively offline (as it takes a table S or X lock).
Also, an index rebuild will do an allocation-order scan of the data whereas
an update statistics is probably doing a logical-order scan. Does the index
have high logical scan fragmentation?
According to a colleague in the query optimizer team, a full scan update of
such a large index is a highly unusual operation. Are you certain that a
lower sampling rate is producing bad statistics?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:379598EC-0F22-4BC0-A070-B30B860090A3@.microsoft.com...
> Yes, The reindex takes about 4 hours on the table, the update statistics
with a fullscan was killed after 11 hours. The size of the table is about
200 million rows.
> -- Paul S Randal [MS] wrote: --
> Can you provide some timing details along with index size etc?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Donnie" <anonymous@.discussions.microsoft.com> wrote in message
> news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
with[vbcol=seagreen]
> fullscan. My question is when you issue the DBCC DBREINDEX command,
are
> statistics updated with a fullscan or the default sample size? If
the
> answer is fullscan, then I am curious as to why the DBCC DBREINDEX
runs
> faster than an UPDATE STATISTICS WITH FULLSCAN?
>
>|||Ok, That makes since as to why the Update Statistics takes longer.
Just so I understand, when a reindex is run, the statistics are re-created f
or the table that is being reindexed? Is that correct?
Thanks for your help. It is much appreciated.
Thanks
Donnie|||Yes, correct. And you're most welcome.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:EB8918BF-B938-47A8-8D88-7E308D748340@.microsoft.com...
> Ok, That makes since as to why the Update Statistics takes longer.
> Just so I understand, when a reindex is run, the statistics are re-created
for the table that is being reindexed? Is that correct?
>
> Thanks for your help. It is much appreciated.
> Thanks
> Donnie
lscan. My question is when you issue the DBCC DBREINDEX command, are statis
tics updated with a fullscan or the default sample size? If the answer is f
ullscan, then I am curious
as to why the DBCC DBREINDEX runs faster than an UPDATE STATISTICS WITH FULL
SCAN?
Thanks
DonnieCan you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?
> Thanks
> Donnie|||Yes, The reindex takes about 4 hours on the table, the update statistics wit
h a fullscan was killed after 11 hours. The size of the table is about 200
million rows.
-- Paul S Randal [MS] wrote: --
Can you provide some timing details along with index size etc?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
> In SQL Server 2000 when you create an Index, statistics are created with
fullscan. My question is when you issue the DBCC DBREINDEX command, are
statistics updated with a fullscan or the default sample size? If the
answer is fullscan, then I am curious as to why the DBCC DBREINDEX runs
faster than an UPDATE STATISTICS WITH FULLSCAN?[vbcol=seagreen]|||Hi Donnie,
An index rebuild always has a stats recreate piggy-backed on top of it.
I can only guess that you have a concurrent workload while you're doing the
update statistics (which is an online operation), whereas the index rebuild
is effectively offline (as it takes a table S or X lock).
Also, an index rebuild will do an allocation-order scan of the data whereas
an update statistics is probably doing a logical-order scan. Does the index
have high logical scan fragmentation?
According to a colleague in the query optimizer team, a full scan update of
such a large index is a highly unusual operation. Are you certain that a
lower sampling rate is producing bad statistics?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:379598EC-0F22-4BC0-A070-B30B860090A3@.microsoft.com...
> Yes, The reindex takes about 4 hours on the table, the update statistics
with a fullscan was killed after 11 hours. The size of the table is about
200 million rows.
> -- Paul S Randal [MS] wrote: --
> Can you provide some timing details along with index size etc?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Donnie" <anonymous@.discussions.microsoft.com> wrote in message
> news:94BBF0E3-894C-4702-BC24-F7760A0CF40C@.microsoft.com...
with[vbcol=seagreen]
> fullscan. My question is when you issue the DBCC DBREINDEX command,
are
> statistics updated with a fullscan or the default sample size? If
the
> answer is fullscan, then I am curious as to why the DBCC DBREINDEX
runs
> faster than an UPDATE STATISTICS WITH FULLSCAN?
>
>|||Ok, That makes since as to why the Update Statistics takes longer.
Just so I understand, when a reindex is run, the statistics are re-created f
or the table that is being reindexed? Is that correct?
Thanks for your help. It is much appreciated.
Thanks
Donnie|||Yes, correct. And you're most welcome.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Donnie" <anonymous@.discussions.microsoft.com> wrote in message
news:EB8918BF-B938-47A8-8D88-7E308D748340@.microsoft.com...
> Ok, That makes since as to why the Update Statistics takes longer.
> Just so I understand, when a reindex is run, the statistics are re-created
for the table that is being reindexed? Is that correct?
>
> Thanks for your help. It is much appreciated.
> Thanks
> Donnie
DBCC DBREINDEX / INDEXDEFRAG
Does SQL Server 2000 after executing DBCC DBREINDEX or
DBCC INDEXDEFRAG run the sp_updatestats?
Are the Logical Fragmentation statistics updated after
execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
procedures?
Thanks,
Dan
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
STATS_DATE returns the date that the statistics for the specified index
were last updated.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan
|||for dbcc DBREINDEX Stats are automatically updated.
for dbcc INDEXDEFRAG, they are not
Greg Jackson
PDX, Oregon
|||> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
SQL Server doesn't "run" sp_updatestats. Create statistics is an inherent effect of creating an index, which
in the end is what DBCC DBREINDEX does. INDEXDEFRAG does not update the statistics information, though.
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
There's no such thing as "Logical Fragmentation *statistics*". The reason you defragment an index is to reduce
fragmentation. Logical Scan fragmentation is one type of fragmentation. So, yes, this value will be reduced.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan
DBCC INDEXDEFRAG run the sp_updatestats?
Are the Logical Fragmentation statistics updated after
execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
procedures?
Thanks,
Dan
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
STATS_DATE returns the date that the statistics for the specified index
were last updated.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan
|||for dbcc DBREINDEX Stats are automatically updated.
for dbcc INDEXDEFRAG, they are not
Greg Jackson
PDX, Oregon
|||> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
SQL Server doesn't "run" sp_updatestats. Create statistics is an inherent effect of creating an index, which
in the end is what DBCC DBREINDEX does. INDEXDEFRAG does not update the statistics information, though.
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
There's no such thing as "Logical Fragmentation *statistics*". The reason you defragment an index is to reduce
fragmentation. Logical Scan fragmentation is one type of fragmentation. So, yes, this value will be reduced.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan
DBCC DBREINDEX / INDEXDEFRAG
Does SQL Server 2000 after executing DBCC DBREINDEX or
DBCC INDEXDEFRAG run the sp_updatestats?
Are the Logical Fragmentation statistics updated after
execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
procedures?
Thanks,
Dan> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
STATS_DATE returns the date that the statistics for the specified index
were last updated.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1af2101c44f33$df425810$a101280a@.phx
.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan|||for dbcc DBREINDEX Stats are automatically updated.
for dbcc INDEXDEFRAG, they are not
Greg Jackson
PDX, Oregon|||> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
SQL Server doesn't "run" sp_updatestats. Create statistics is an inherent ef
fect of creating an index, which
in the end is what DBCC DBREINDEX does. INDEXDEFRAG does not update the stat
istics information, though.
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
There's no such thing as "Logical Fragmentation *statistics*". The reason yo
u defragment an index is to reduce
fragmentation. Logical Scan fragmentation is one type of fragmentation. So,
yes, this value will be reduced.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1af2101c44f33$df425810$a10
1280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan
DBCC INDEXDEFRAG run the sp_updatestats?
Are the Logical Fragmentation statistics updated after
execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
procedures?
Thanks,
Dan> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
STATS_DATE returns the date that the statistics for the specified index
were last updated.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1af2101c44f33$df425810$a101280a@.phx
.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan|||for dbcc DBREINDEX Stats are automatically updated.
for dbcc INDEXDEFRAG, they are not
Greg Jackson
PDX, Oregon|||> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
SQL Server doesn't "run" sp_updatestats. Create statistics is an inherent ef
fect of creating an index, which
in the end is what DBCC DBREINDEX does. INDEXDEFRAG does not update the stat
istics information, though.
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
There's no such thing as "Logical Fragmentation *statistics*". The reason yo
u defragment an index is to reduce
fragmentation. Logical Scan fragmentation is one type of fragmentation. So,
yes, this value will be reduced.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1af2101c44f33$df425810$a10
1280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan
DBCC DBREINDEX / INDEXDEFRAG
Does SQL Server 2000 after executing DBCC DBREINDEX or
DBCC INDEXDEFRAG run the sp_updatestats?
Are the Logical Fragmentation statistics updated after
execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
procedures?
Thanks,
Dan> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
STATS_DATE returns the date that the statistics for the specified index
were last updated.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan|||for dbcc DBREINDEX Stats are automatically updated.
for dbcc INDEXDEFRAG, they are not
Greg Jackson
PDX, Oregon|||> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
SQL Server doesn't "run" sp_updatestats. Create statistics is an inherent effect of creating an index, which
in the end is what DBCC DBREINDEX does. INDEXDEFRAG does not update the statistics information, though.
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
There's no such thing as "Logical Fragmentation *statistics*". The reason you defragment an index is to reduce
fragmentation. Logical Scan fragmentation is one type of fragmentation. So, yes, this value will be reduced.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan
DBCC INDEXDEFRAG run the sp_updatestats?
Are the Logical Fragmentation statistics updated after
execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
procedures?
Thanks,
Dan> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
STATS_DATE returns the date that the statistics for the specified index
were last updated.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan|||for dbcc DBREINDEX Stats are automatically updated.
for dbcc INDEXDEFRAG, they are not
Greg Jackson
PDX, Oregon|||> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
SQL Server doesn't "run" sp_updatestats. Create statistics is an inherent effect of creating an index, which
in the end is what DBCC DBREINDEX does. INDEXDEFRAG does not update the statistics information, though.
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
There's no such thing as "Logical Fragmentation *statistics*". The reason you defragment an index is to reduce
fragmentation. Logical Scan fragmentation is one type of fragmentation. So, yes, this value will be reduced.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan
DBCC DBREINDEX - Update with FULLSCAN or without?
I've read
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
and I understand that "Updating statistics is a side effect of DBCC
DBREINDEX". But is that update done the equivalent of a WITH FULLSCAN
update, or the default sampling update?
Thanks!
Stephen SkrzydloYes, it is same as FULLSCAN. Since all rows are touched when doing REBUILD,
it would be foolish not
to base the new statistics on all the data.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephen Skrzydlo" <Stephen Skrzydlo@.discussions.microsoft.com> wrote in mes
sage
news:2582A848-B6ED-4B85-8ACD-10A846CB7C15@.microsoft.com...
> I've read
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> and I understand that "Updating statistics is a side effect of DBCC
> DBREINDEX". But is that update done the equivalent of a WITH FULLSCAN
> update, or the default sampling update?
> Thanks!
> Stephen Skrzydlo
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
and I understand that "Updating statistics is a side effect of DBCC
DBREINDEX". But is that update done the equivalent of a WITH FULLSCAN
update, or the default sampling update?
Thanks!
Stephen SkrzydloYes, it is same as FULLSCAN. Since all rows are touched when doing REBUILD,
it would be foolish not
to base the new statistics on all the data.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephen Skrzydlo" <Stephen Skrzydlo@.discussions.microsoft.com> wrote in mes
sage
news:2582A848-B6ED-4B85-8ACD-10A846CB7C15@.microsoft.com...
> I've read
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> and I understand that "Updating statistics is a side effect of DBCC
> DBREINDEX". But is that update done the equivalent of a WITH FULLSCAN
> update, or the default sampling update?
> Thanks!
> Stephen Skrzydlo
DBCC DBREINDEX - Update or Create Statistics
Hello All,
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
Mitra
Mitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
Mitra
Mitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
DBCC DBREINDEX - Update or Create Statistics
Hello All,
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our Java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexe
s
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data fil
e
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our Java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our Java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexe
s
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data fil
e
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our Java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
DBCC DBREINDEX - Update or Create Statistics
Hello All,
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
I have a 12-15 GB database that is performing poorly when we try to apply
database schema changes using our java application. The schema changes
include adding new columns to several tables that have seveal million rows ,
dropping and recreating several constraints, and changing some of the indexes
from unique to regular indexes, etc.
Are changes to the database schema a logged operation? If yes, how about
changing the database from Full recovery to Simple recovery mode before
applying schema changes? Will this help the performance?
I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
operation? If yes, how much free disk space do we need for both the data file
and the transaction log file? Do I need to run Update or Create Statistics
after a DBCC DBREINDEX operation?
Thank you so much,
MitraMitra,
Schema changes are logged. Switching to SIMPLE recovery mode may help the
performance. DBCC DBREINDEX is logged. Space required will vary depending
on the number of records and indexes. Statistics are updated as part of a
DBCC DBREINDEX operation. For more information see the following:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Jerry
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:037B09E8-D868-49FC-8C0A-1922807B3B64@.microsoft.com...
> Hello All,
> I have a 12-15 GB database that is performing poorly when we try to apply
> database schema changes using our java application. The schema changes
> include adding new columns to several tables that have seveal million rows
> ,
> dropping and recreating several constraints, and changing some of the
> indexes
> from unique to regular indexes, etc.
> Are changes to the database schema a logged operation? If yes, how about
> changing the database from Full recovery to Simple recovery mode before
> applying schema changes? Will this help the performance?
> I'am thinking of running DBCC DBREINDEX. Is DBCC DBREINDEX a logged
> operation? If yes, how much free disk space do we need for both the data
> file
> and the transaction log file? Do I need to run Update or Create Statistics
> after a DBCC DBREINDEX operation?
> Thank you so much,
> Mitra
Subscribe to:
Posts (Atom)