Automating the Reindex HQ database

Posted by Jim Early on March 24, 2008, 12:21 pm

Please log in for more thread options

We're running RMS HQ 2.0.0113 on SQL Server 2k5. Each morning a maintenance
plan is run on the HQ database that does (in order): a full backup, Check DB
Integrity (includes indexes), Update Statistics (tables and views, All
existing statistics, Full scan), Rebuild Indexes (tables and views, original
amount of free space), and then sends an e-mails reporting success/failure.

Most Mondays, and occasionally in the middle of the week, though the staff
has problems generating reports (i.e. Detail Sales for the day before).
Sometimes the report will just be very slow (taking 10 min rather than 10
seconds), but often the reports will just time out.

Using the Reindex function in HQ Administrator cleans it up. Is there
something different about the way HQ does a reindex as opposed to the Rebuild
Index Task? If so, is there a way to automate it and include that in our
morning maintenance?

Posted by Jason on March 24, 2008, 4:05 pm

Please log in for more thread options

A bit off topic, but I have NEVER reindexed any store or HQ after hundreds
of thousands of transactions inseveral stores over three years. All I have
ever done is delete logs and worksheets to free up space.

Is there anything wrong with this? Why would I need to reindex if nothing is
wrong? Are there any downsides/risks to reindexing?

> We're running RMS HQ 2.0.0113 on SQL Server 2k5. Each morning a

> maintenance

> plan is run on the HQ database that does (in order): a full backup, Check

> DB

> Integrity (includes indexes), Update Statistics (tables and views, All

> existing statistics, Full scan), Rebuild Indexes (tables and views,

> original

> amount of free space), and then sends an e-mails reporting

> success/failure.

> Most Mondays, and occasionally in the middle of the week, though the staff

> has problems generating reports (i.e. Detail Sales for the day before).

> Sometimes the report will just be very slow (taking 10 min rather than 10

> seconds), but often the reports will just time out.

> Using the Reindex function in HQ Administrator cleans it up. Is there

> something different about the way HQ does a reindex as opposed to the

> Rebuild

> Index Task? If so, is there a way to automate it and include that in our

> morning maintenance?

Posted by Jim Early on March 24, 2008, 8:27 pm

Please log in for more thread options

I'm sure exactly why the indexes have become so fragile. I would have
expected a more gradual degregation in performance than we are seeing. If I
backup the HQ database and restore it on test server and then allow parallel
plan execution (MAXDOP = 0) the reports run fine too. MAXDOP = 1 on the
production server. I'm still trying to learn why there is such a huge
difference.

We did run for months without needing to reindex, then several weeks, now we
need to do it once or twice a week. I _think_ the rebuild index process in
the maintenance task helps, but it doesn't work every time where the
'Reindex' in HQ actually does.

"Jason" wrote:

> A bit off topic, but I have NEVER reindexed any store or HQ after hundreds

> of thousands of transactions inseveral stores over three years. All I have

> ever done is delete logs and worksheets to free up space.

> Is there anything wrong with this? Why would I need to reindex if nothing is

> wrong? Are there any downsides/risks to reindexing?

> > We're running RMS HQ 2.0.0113 on SQL Server 2k5. Each morning a

> > maintenance

> > plan is run on the HQ database that does (in order): a full backup, Check

> > DB

> > Integrity (includes indexes), Update Statistics (tables and views, All

> > existing statistics, Full scan), Rebuild Indexes (tables and views,

> > original

> > amount of free space), and then sends an e-mails reporting

> > success/failure.

> >

> > Most Mondays, and occasionally in the middle of the week, though the staff

> > has problems generating reports (i.e. Detail Sales for the day before).

> > Sometimes the report will just be very slow (taking 10 min rather than 10

> > seconds), but often the reports will just time out.

> >

> > Using the Reindex function in HQ Administrator cleans it up. Is there

> > something different about the way HQ does a reindex as opposed to the

> > Rebuild

> > Index Task? If so, is there a way to automate it and include that in our

> > morning maintenance?

> >