Re: Ms SQL server db options for performance issues after db corruption

From: Narayana Vyas Kondreddi (answer_me_at_hotmail.com)
Date: 09/07/04

  • Next message: Curt Spanburgh: "RE: Ms SQL server db options for performance issues after db corruptio" 
    Date: Tue, 7 Sep 2004 21:07:35 +0100

    I think you need to pin-point, what exactly has slowed, since the database
    was restored. Is it a specific screen/query?

    I'd start with Profiler to identify long running stored procedures and CPU
    intensive queries, and start tuning those that come up as slow and
    expensive.

    Also, do you have a performance benchmark of the system, before the problem?
    If so, you can compare that benchmark to the current system performance, to
    validate how good/bad the system is compared to the old benchmark. 

    -- 
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Nick Dakoronias" <dakoroni@gr.ibm.com> wrote in message
news:%23qEVH7NlEHA.2820@TK2MSFTNGP15.phx.gbl...
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
  • Next message: Curt Spanburgh: "RE: Ms SQL server db options for performance issues after db corruptio"

    Relevant Pages

    • Re: Ms SQL server db options for performance issues after db corruption
      ... Also, do you have a performance benchmark of the system, before the problem? ... I have a customer using Ms SQL Server 2000 SP3 installed on optical RAID-5 ... Disk Array. ... But now, cust. ...
      (microsoft.public.sqlserver.server)
    • Re: Ms SQL server db options for performance issues after db corruption
      ... Also, do you have a performance benchmark of the system, before the problem? ... I have a customer using Ms SQL Server 2000 SP3 installed on optical RAID-5 ... Disk Array. ... But now, cust. ...
      (microsoft.public.sqlserver.tools)
    • Re: Ms SQL server db options for performance issues after db corruption
      ... Also, do you have a performance benchmark of the system, before the problem? ... I have a customer using Ms SQL Server 2000 SP3 installed on optical RAID-5 ... Disk Array. ... But now, cust. ...
      (microsoft.public.sqlserver.programming)
    • Re: Determine Disk Block Size ?
      ... As always, thanks Andrew. ... Most of the documentation for 2000 is wrong in that it may state you need multiple files to spawn multiple threads. ... >> The MS SQL Server Operations Manual makes a recommendation of using 64-KB>> cluster sizes. ... We have done so with a dramatic total disk I/O>> throughput. ...
      (microsoft.public.sqlserver.server)
    • Re: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
      ... Hugo. ... > tempdb (used by SQL Server to store intermediate result sets) is on the ... My Windows page file is on that hard disk as well (though ... elapsed time was disk I/O latency vs. CPU time. ...
      (comp.object)