Database Maintenance Plan - Update Data Optimization

Should we check the box "Reorganize data and index pages" on Database
Maintenance Plan wizard ? What's the benefit ?
Thanks.This will essentially do a DBCC DBREINDEX on each of your tables (assuming a
clustered index exists) and non-clustered indexes. See DBCC DBREINDEX in
BooksOnLine and
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/Optimize/SS2KIDBP.asp
for more details.
Andrew J. Kelly
SQL Server MVP
"John Smith" <someone@.nospam.com.us> wrote in message
news:Ogr%23fQOnDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Should we check the box "Reorganize data and index pages" on Database
> Maintenance Plan wizard ? What's the benefit ?
> Thanks.
>|||And if you are running a 24x7 critical application that really must be
available all the time, you should be very careful with that check box. DBCC
DBREINDEX is not an 'online' operation (from the end user perspective) -- it
can result in severe blocking on a large table, making the table effectively
not available to the end user.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"John Smith" <someone@.nospam.com.us> wrote in message
news:Ogr%23fQOnDHA.2268@.TK2MSFTNGP12.phx.gbl...
> Should we check the box "Reorganize data and index pages" on Database
> Maintenance Plan wizard ? What's the benefit ?
> Thanks.
>

database maintenance plan - reorganize data pages and indexes

I've set up a monthly maint. plan that will re-org the db. I'm hoping that since this is a very high tran vol that I'm being proactive.

But, when I do this against a matching test db (exact same one but the name) I get a SET QUOTED IDENTIFIER incorrect on the optimization step. I've checked the properties of the DB and the 'Use quoted identifiers' box is checked and needs to be. I created the maint plan using the wizard and the option I've tried it with the option of reorg to orig amt of free space and change free space selected.

Help!!!! Oh, this is a PeopleSoft DB, so I can't change the prop of the DB..

Try putting the following statements at the top of the step with the SQL Agent Job that the DB Maintenance Plan creates to schedule execution of the plan:

set nocount on
set arithabort on
set ansi_nulls on
set ansi_null_dflt_on on
set ansi_padding on
set ansi_warnings on
set cursor_close_on_commit off
set implicit_transactions off
set quoted_identifier on
set concat_null_yields_null on
set numeric_roundabort off

That should be directly followed by the statement that already existed within the step to execute the master.dbo.xp_sqlmaint procedure.

HTH

|||Sorry to take so long, I had to find out how to add the commands. Which I did, but I'm still getting the same error. I c&p'd them into the 'edit' of the job step and applied. Then I went back and checked that they were still there. I started the job (which I've set up on a copy of the prod db) and it died w/the same error.
As you can tell, this is new stuff to me.
What else can I try?|||Well, try changing the 'on' in the 'set quoted identifier on' statement to 'off' instead...all comes down to differences between these settings in the msdb, master, and your user database...|||I couldn't chg the flag. But did get the suggestion you sent to work. I removed the line returns at the end of each line and replaced them with just a space, so that the entries where all strung together when I edited the step. So that worked.

So thank you. Now I just have to learn how to keep them when the job is refreshed.

as you've probably guessed, I'm learning as I go.|||

I'm back! Thought I had this resolved, but am still having the same error. The bad thing is that it only occurs on some of the DBs in the same instance of SQL Server. I've checked the options on the DBs and they match between the ones that can be integrity checked and the ones that can not. The ones that can be i ckd will run w/o any of the overrides. For the ones that the job gets an error, I've tried quoted_identifier on and off, with the other options and w/o. All DBs have ANSI NULL default, Auto update statistics, torn page, auto create statistics and use quoted identifiers checked, others not. (sql 2000). Db compatibility level 80 is selected on the compatibility. Some Dbs are model simple, some are full. That doesn't seem to matter.

I'd appreciate any more ideas.........

Database Maintenance Plan

Hi,
Please tell me why isn't it possible to switch in one maintenance plan
Reorganize data and index pages and Update the statistics used by the query
optimizer?
May be reorganizing data and indexes also updates statistics? but if yes,
updates it all statistics or only some of them?
ThanksI am not sure if I understand your question 100% but when you reindex with
the maintenance plan it issues a DBCC DBREINDEX. That command updates the
statistics automatically for you.
--
Andrew J. Kelly SQL MVP
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:7164A315-4775-4D80-BE09-0C06EE5112E5@.microsoft.com...
> Hi,
> Please tell me why isn't it possible to switch in one maintenance plan
> Reorganize data and index pages and Update the statistics used by the
> query
> optimizer?
> May be reorganizing data and indexes also updates statistics? but if yes,
> updates it all statistics or only some of them?
> Thanks

Database Maintenace Plan

None of my jobs run the "Update statistics used by the
query optimizer" task, but I do run the "Reorganize data
and index pages". If this is selected I can't run Update
statistics used by the query optimizer. "Do I need a
specific job to update index stats? Is it necessary to
run both jobs?No, DBCC DBREINDEX includes a full update statistics.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?|||If you run the DBCC very infrequently. You may wish to run another
maintenance plan that updates statistics..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?

Database Maintenace Plan

None of my jobs run the "Update statistics used by the
query optimizer" task, but I do run the "Reorganize data
and index pages". If this is selected I can't run Update
statistics used by the query optimizer. "Do I need a
specific job to update index stats? Is it necessary to
run both jobs?
No, DBCC DBREINDEX includes a full update statistics.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?
|||If you run the DBCC very infrequently. You may wish to run another
maintenance plan that updates statistics..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?

Database Maintenace Plan

None of my jobs run the "Update statistics used by the
query optimizer" task, but I do run the "Reorganize data
and index pages". If this is selected I can't run Update
statistics used by the query optimizer. "Do I need a
specific job to update index stats? Is it necessary to
run both jobs?No, DBCC DBREINDEX includes a full update statistics.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx
.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?|||If you run the DBCC very infrequently. You may wish to run another
maintenance plan that updates statistics..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Lee" <anonymous@.discussions.microsoft.com> wrote in message
news:23fb501c45f78$02af65a0$a601280a@.phx
.gbl...
> None of my jobs run the "Update statistics used by the
> query optimizer" task, but I do run the "Reorganize data
> and index pages". If this is selected I can't run Update
> statistics used by the query optimizer. "Do I need a
> specific job to update index stats? Is it necessary to
> run both jobs?