Ms SQL server db options for performance issues after db corruption

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
>
>
>.
>
- Peter,
Thanks for your clarifications and the useful tips
- Narayana,
We try to investigate what caused the SQL performance degradation.
We supsect that the HW failure in disk array caused db corruption. This is
also verified by Ms SQL error logs and dumps.
But on the other hand, Fast optical diak array log as well as, management
console indicate that after the replacement of the
defective disk the whole RAID 5 array is healthy and the log is clear of
errors.
Regarding the Performance Benchamrk, it is just enough to mention a
performance degradation of about 70 % either using the
same array (8 x 36.4GB) or another (5 x 74.8 GB). This percentage could not
be justified by using bad indexes or triggers only.
Don't you agree?
- Curt,
This is actually an ERP oriented -business critical- application using SQL
server instances. It is related to Logistics, Products Shipment, Inventory,
etc.
___________________
My additional comments:
I also think that it is essential to determine the db queries performance by
using the SET statement to enable the SHOWPLAN, STATISTICS IO, STATISTICS
TIME, and
STATISTICS profile options. For Example:
SHOWPLAN describes the method chosen by the SQL Server query optimizer to
retrieve data. (SET SHOWPLAN_ALL).
STATISTICS TIME displays the amount of time (in milliseconds) required to
parse, compile, and execute a query. (SET STATISTICS TIME).
STATISTICS IO reports information about the number of scans, logical reads
(pages accessed in cache), and physical reads (number of times the disk was
accessed) for each table referenced in the statement. (SET STATISTICS IO).
STATISTICS PROFILE displays a result set after each executed query
representing a Profile of the execution of the query (SET STATISTICS
PROFILE)
Use Index Tuning Wizard to obtain a recommendation abour the best mix of
indexes for a database given a workload and analyze the effects of the
proposed changes, including index usage
and performance of queries in workload.
Thanks and Regards,
Nick Dakoronias
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:091601c494e4$fce077c0$a401280a@.phx.gbl...[vbcol=seagreen]
> 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
>
> on optical RAID-5
> since the failed
> transcations
> (traces, logs,
> following options :
> regararding any
> options?
> SHOWPLAN_ALL in order