Ms SQL server db options for performance issues after db corruption

From: Peter The Spate (anonymous_at_discussions.microsoft.com)
Date: 09/07/04 

Date: Tue, 7 Sep 2004 07:14:28 -0700

Now the odd thing here is that although some of the
options will ultimatly improve performance, quite a few of
them will not.

Fistly the update statistics / create statistics. This
will improve performace but only when you change / create
something i.e. and index. I have this on my server.

Recursive triggers only allow as the wording states
recursive triggers and will not do anything to directly to
improve the performance. This is an application level
thing, personally I hate recursion.

Quoted Identifiers will not improve performance but you
may find some of your SQL will not work if you have not
had it on before.

Torn Page detection ???, important of you don't have
anything in place that can detect it, however it will
effect performance.

OK then cursor options. First rule of thumb. If you can
get away from using cursors then don't use them, they are
really slow. There are a lot of other options but its
probably better if you look it up on the net.

Other things you can try...

As you have just put in a new disk your datafiles are
going to need defragging (look up the DBCC DBREINDEX
command)

Also you may want to re-compile your SP after you perform
the re-index.

Anyway there are a LOT of other things you can try, too
many to put here so I surgest you have a look on the web.

Peter

"Choose a job you love, and you will never have to work a
day in your life."
Confucius

>-----Original Message-----
>Hello SQL forum readers,
>
>I have a customer using Ms SQL Server 2000 SP3 installed
on optical RAID-5
>Disk Array (IBM Fast200 SAN).
>After a disk failure his production db was corrupted and
since the failed
>disk was replaced, cust. restored a valid backup
>in order to support his business operations (with one day
transcations
>loss).
>We have dome a lot of troubleshooting and investigation
(traces, logs,
>dumps) in order to identify what caused the corruption.
>
>But now, cust. faces low performance problems.
>I have already suggested to use additionally the
following options :
>A) Auto Options:
>Auot create statistics
>Auto update statistics
>B) Recovery Options:
>Recursive Triggers
>
>I would be much appreciated If I could have any advise
regararding any
>additional db option setting that could assist :
>a) What about Quoted Identifiers or other SQL option?
>b) What about Torn Page Detection (recovery option)
>c) Any other cursor options that could assist?
>Is there additional MEM/CPU overhead by enabling those
options?
>
>I have also advised cust, to execute the command SET
SHOWPLAN_ALL in order
>to gather details abour executed statements
>Anything else I can do?
>
>Any advise will be much appreciated.
>Thanks in Advance
>
>Regards, Nick Dakoronias
>IBM Athens/Greece
>
>
>
>
>.
>