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
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
|||- 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
Showing posts with label san. Show all posts
Showing posts with label san. Show all posts
Monday, March 26, 2012
Ms SQL server db options for performance issues after db corruption
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/GreeceNow 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
>
>
>.
>|||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|||- 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...
> 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
> >
> >
> >
> >
> >.
> >sql
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/GreeceNow 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
>
>
>.
>|||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|||- 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...
> 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
> >
> >
> >
> >
> >.
> >sql
Ms SQL server db options for performance issues after db corruption
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
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
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
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
Friday, March 9, 2012
MS SQL Cluster w/ Clariion SAN
Okay, assume that I have read every technical document from MS and Dell about installing SQL into a cluster on a Clariion SAN (I haven't actually read them all yet, but I'm getting there).
What gotcha's are waiting to bite me? I am using:
1. An Active/Passive 2-node cluster
2. Windows 2000 Advanced Server
3. SQL 2000 Enterprise Server
4. EMC Clariion CX400 15-drive array
I have never before configured a cluster server. I got dropped into this project. No one else in the project has any real experience with clusters either.
I have the step-by-step instructions from both Dell and MS: I plan to follow these very carefully. I'm not looking for step-by-step instructions here, I'm hoping for some, "when I tried this, it did that..." or "watch out, the instruction says to do x, but you should really do y..." sort of thing. Also, if you have any additional documentation outside of the vendor required reading, I wuld be grateful for a link.
I understand that there are issues around:
1. Snapview backup from EMC (doesn't work on multi-volume DBs)
2. SQL Mail and SQL Agent Mail (Outlook is not cluster-aware)
I realize this is kind of open-ended; but I really would like to get this right the first time around since it appears that I will be supporting this installation long term.
Regards,
hmscott- http://support.microsoft.com/default.aspx?scid=kb;EN-US;254321 - cluster do's and dont's.
- http://www.sql-server-performance.com/sqlserver2000_clustering_install_part1.asp to stop unnecessary services during setup.
- http://support.microsoft.com/default.aspx?scid=/support/sql/content/70papers/70clstr.asp from the step by step guide pay attention on post installation topics.
- http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/scconbp.mspx though this refers to cluster on windows2003 best practices, for information.
HTH
What gotcha's are waiting to bite me? I am using:
1. An Active/Passive 2-node cluster
2. Windows 2000 Advanced Server
3. SQL 2000 Enterprise Server
4. EMC Clariion CX400 15-drive array
I have never before configured a cluster server. I got dropped into this project. No one else in the project has any real experience with clusters either.
I have the step-by-step instructions from both Dell and MS: I plan to follow these very carefully. I'm not looking for step-by-step instructions here, I'm hoping for some, "when I tried this, it did that..." or "watch out, the instruction says to do x, but you should really do y..." sort of thing. Also, if you have any additional documentation outside of the vendor required reading, I wuld be grateful for a link.
I understand that there are issues around:
1. Snapview backup from EMC (doesn't work on multi-volume DBs)
2. SQL Mail and SQL Agent Mail (Outlook is not cluster-aware)
I realize this is kind of open-ended; but I really would like to get this right the first time around since it appears that I will be supporting this installation long term.
Regards,
hmscott- http://support.microsoft.com/default.aspx?scid=kb;EN-US;254321 - cluster do's and dont's.
- http://www.sql-server-performance.com/sqlserver2000_clustering_install_part1.asp to stop unnecessary services during setup.
- http://support.microsoft.com/default.aspx?scid=/support/sql/content/70papers/70clstr.asp from the step by step guide pay attention on post installation topics.
- http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/clustering/scconbp.mspx though this refers to cluster on windows2003 best practices, for information.
HTH
Subscribe to:
Posts (Atom)