Re: Regular Maintainence (Index Creation)

#1

www.edbarlow.com
KRV wrote:

Quote:

We have a remote database where there is no connectivity (Even dial-up
facility is not available). Transactions are very high and data gets
transferred to primary site thru tape device. We cannot afford to put a DBA
for maintenance activity.

For regular maintenance activity we are planning to follow these methods on
monthly basis (All tables are all pages locking scheme)

1. Write a script to drop all the references (foreign keys)
2. Drop all the clustered indexes
3. Re-create indexes and recreate references.
4. Execute the script on schedule basis (Monthly)
5. run dbcc on these tables and dump the database

We cannot do bcp out or reorg rebuild.. also we cannot keep visiting the
site frequently.

This part is only covering for indexes.... We need experts advise on this
scheduled tasks, is it feasible & workable solution?

Thanks
KRV

#2

What do you wish to achieve by this maintenance task? Is it to
defragment the data pages? For updating statistics? Your plan is
feasible but may be very slow.

--ian

KRV wrote:

Quote:

We have a remote database where there is no connectivity (Even dial-up
facility is not available). Transactions are very high and data gets
transferred to primary site thru tape device. We cannot afford to put a DBA
for maintenance activity.

For regular maintenance activity we are planning to follow these methods on
monthly basis (All tables are all pages locking scheme)

1. Write a script to drop all the references (foreign keys)
2. Drop all the clustered indexes
3. Re-create indexes and recreate references.
4. Execute the script on schedule basis (Monthly)
5. run dbcc on these tables and dump the database

We cannot do bcp out or reorg rebuild.. also we cannot keep visiting the
site frequently.

This part is only covering for indexes.... We need experts advise on this
scheduled tasks, is it feasible & workable solution?

Thanks
KRV

#3

KRV wrote:

Quote:

We have a remote database where there is no connectivity (Even dial-up
facility is not available). Transactions are very high and data gets
transferred to primary site thru tape device. We cannot afford to put a DBA
for maintenance activity.

For regular maintenance activity we are planning to follow these methods on
monthly basis (All tables are all pages locking scheme)

1. Write a script to drop all the references (foreign keys)
2. Drop all the clustered indexes
3. Re-create indexes and recreate references.
4. Execute the script on schedule basis (Monthly)
5. run dbcc on these tables and dump the database

I see no real reason to do some of this in this way.

Firstly, what version of ASE is it?

Quote:

We cannot do bcp out or reorg rebuild.. also we cannot keep visiting the
site frequently.

You cannot do reorgs on APL tables anyway.

Quote:

This part is only covering for indexes.... We need experts advise on this
scheduled tasks, is it feasible & workable solution?

I would have to ask first, what issues are you seeing, if any?

I would run update stats regularly and either the traditional
dbcc checks or the new ones.

I would run these more frequently than monthly. Perhaps weekly
or more frequently if required.

I would do dumps regularly. You say that data is transferred
through a tape device. If these are dumps you can load, you
can do offsite checks by loading the dumps into a separate
database and running as many checks as you like there. If you
notice problems, you can have the original site run specific
scripts to do certain tasks (e.g. index rebuilds if you notice
that fragmentation has become unacceptable).

If the transaction volume is high, I would recommend dumping
the transaction logs frequently. Ensure that the site also
has some form of hardware mirroring enabled on the disks.

-am © MMVI

#4

KRV wrote:

Quote:

Yes defragmentation, updating statistics.. and this will be used max on 18
tables and we can afford to allocate 12-14 hrs time for this particular
task. At this point of time there are no maintenance tasks are run. even
server is not configured properly.. client has installed and running the
server there are few things to be done which improves response time for
reports.. Applications uses extensive reporting and we are targeting @
(network packet size, memory increase, cache binding, update
statistics,etc ) we also found open objects are less and few procedure are
failing...DB Design is one of the best I have ever come across... might be
this is the one of the reason without DBA the applications and DB are
running on its own.. Apart for dumping the DB no other activities are
currently run on this database

Yes, you'll need to do more than that. Running update stats
will help the optimiser with index selection. I'm surprised
that they aren't complaining about performance already if its
never been run.

Your tuning will help but it won't prevent the optimiser table
scanning if the stats aren't suitable.

-am © MMVI

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Regular Maintainence (Index Creation)

sybase.public.ase.performance+tuning sybase.public.ase.performance+tuning

Re: Regular Maintainence (Index Creation) - 06-15-2006 , 12:59 PM

Re: Regular Maintainence (Index Creation) - 06-15-2006 , 03:19 PM

Re: Regular Maintainence (Index Creation) - 06-15-2006 , 11:02 PM

Re: Regular Maintainence (Index Creation) - 06-15-2006 , 11:05 PM