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 sp3. Show all posts
Showing posts with label sp3. 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 23, 2012
MS SQL Server 2k standard - insuffient system resources or memory
Hi,
I have sql 2000 with sp3, on w2k with sp4. It has 3 GB RAM. I haven't set u
p the maximum memory for instance, so they are defaults from 0 to 3072 mb.
My former server worked fine.: It was sql 2000 no sp, on w2k with sp2. It ha
d 1 GB RAM.
I had't set up the maximum memory for instance, so they are defaults from 0
to 1024 mb.
and that worked fine.
My 3rd party programs are finnish accounting programs and they give me very
much error and other conflict messages. I have already checked out everythin
g from setttings and I don't believe it could be any collation problem or so
mething.
I am now wondering what happens when there's no limits of the memory usage.
Will sql use over 2 gb though it can't and give problems after or does it re
alize limits?
Does anyone know?Standard Edition of SQL Server 2000 will not use more than 2G.
It's easy to confirm this with Performance Monitor. What errros are you
getting?
Brian
"Mikko Oksanen" <anonymous@.discussions.microsoft.com> wrote in message
news:70923497-8A0B-43A1-B9FE-608215BC91CA@.microsoft.com...
> Hi,
> I have sql 2000 with sp3, on w2k with sp4. It has 3 GB RAM. I haven't set
up the maximum memory for instance, so they are defaults from 0 to 3072 mb.
> My former server worked fine.: It was sql 2000 no sp, on w2k with sp2. It
had 1 GB RAM.
> I had't set up the maximum memory for instance, so they are defaults from
0 to 1024 mb.
> and that worked fine.
> My 3rd party programs are finnish accounting programs and they give me
very much error and other conflict messages. I have already checked out
everything from setttings and I don't believe it could be any collation
problem or something.
> I am now wondering what happens when there's no limits of the memory
usage. Will sql use over 2 gb though it can't and give problems after or
does it realize limits?
> Does anyone know?
>
>|||Hello,
I looked from the performance monitor. I Noticed it also. Thank you
I get this kind of errors:
Insufficient resources to create UMS scheduler
Run-time error'-2147217900 (80040e14)':
The CREATE DATABASE process is allocating 0,75 MB on disk 'pl3135kk'.
(here the pl3135 is some temp database)
Insufficient system memory to run this query
This kind of problem. Anykind of help could do if you know or have heard thi
s kind of problems
-Mikko-
Standard Edition of SQL Server 2000 will not use more than 2G.
It's easy to confirm this with Performance Monitor. What errros are you
getting?
Brian
"Mikko Oksanen" <anonymous@.discussions.microsoft.com> wrote in message
news:70923497-8A0B-43A1-B9FE-608215BC91CA@.microsoft.com...
> Hi,
up the maximum memory for instance, so they are defaults from 0 to 3072 mb.
had 1 GB RAM.
> I had't set up the maximum memory for instance, so they are defaults from
0 to 1024 mb.
> and that worked fine.
very much error and other conflict messages. I have already checked out
everything from setttings and I don't believe it could be any collation
problem or something.
usage. Will sql use over 2 gb though it can't and give problems after or
does it realize limits?sql
I have sql 2000 with sp3, on w2k with sp4. It has 3 GB RAM. I haven't set u
p the maximum memory for instance, so they are defaults from 0 to 3072 mb.
My former server worked fine.: It was sql 2000 no sp, on w2k with sp2. It ha
d 1 GB RAM.
I had't set up the maximum memory for instance, so they are defaults from 0
to 1024 mb.
and that worked fine.
My 3rd party programs are finnish accounting programs and they give me very
much error and other conflict messages. I have already checked out everythin
g from setttings and I don't believe it could be any collation problem or so
mething.
I am now wondering what happens when there's no limits of the memory usage.
Will sql use over 2 gb though it can't and give problems after or does it re
alize limits?
Does anyone know?Standard Edition of SQL Server 2000 will not use more than 2G.
It's easy to confirm this with Performance Monitor. What errros are you
getting?
Brian
"Mikko Oksanen" <anonymous@.discussions.microsoft.com> wrote in message
news:70923497-8A0B-43A1-B9FE-608215BC91CA@.microsoft.com...
> Hi,
> I have sql 2000 with sp3, on w2k with sp4. It has 3 GB RAM. I haven't set
up the maximum memory for instance, so they are defaults from 0 to 3072 mb.
> My former server worked fine.: It was sql 2000 no sp, on w2k with sp2. It
had 1 GB RAM.
> I had't set up the maximum memory for instance, so they are defaults from
0 to 1024 mb.
> and that worked fine.
> My 3rd party programs are finnish accounting programs and they give me
very much error and other conflict messages. I have already checked out
everything from setttings and I don't believe it could be any collation
problem or something.
> I am now wondering what happens when there's no limits of the memory
usage. Will sql use over 2 gb though it can't and give problems after or
does it realize limits?
> Does anyone know?
>
>|||Hello,
I looked from the performance monitor. I Noticed it also. Thank you
I get this kind of errors:
Insufficient resources to create UMS scheduler
Run-time error'-2147217900 (80040e14)':
The CREATE DATABASE process is allocating 0,75 MB on disk 'pl3135kk'.
(here the pl3135 is some temp database)
Insufficient system memory to run this query
This kind of problem. Anykind of help could do if you know or have heard thi
s kind of problems
-Mikko-
Standard Edition of SQL Server 2000 will not use more than 2G.
It's easy to confirm this with Performance Monitor. What errros are you
getting?
Brian
"Mikko Oksanen" <anonymous@.discussions.microsoft.com> wrote in message
news:70923497-8A0B-43A1-B9FE-608215BC91CA@.microsoft.com...
> Hi,
up the maximum memory for instance, so they are defaults from 0 to 3072 mb.
had 1 GB RAM.
> I had't set up the maximum memory for instance, so they are defaults from
0 to 1024 mb.
> and that worked fine.
very much error and other conflict messages. I have already checked out
everything from setttings and I don't believe it could be any collation
problem or something.
usage. Will sql use over 2 gb though it can't and give problems after or
does it realize limits?sql
Wednesday, March 21, 2012
MS SQL Server 2000 replication deployment
Hi
I want to implement transactional replication on 2 MS SQL Servers 2000
(SP3). Is there any good guide available that discusses crucial points in
this endeavour (e.g. possible topologies, what and how needs to be monitored,
potential issues to avoid, what to do in case of failures etc.)? I need a
document that focuses on moving the replication into production environment.
I'm especially interested in alternative means of monitoring the replication
rather than the built-in one.
Many thanks,
Oskar
You can check BOL, do a search on MSDN for transactional replication. You
might want to consider my book
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:3F9E5C48-79C8-4D76-87C8-B9C2ABC6C840@.microsoft.com...
> Hi
> I want to implement transactional replication on 2 MS SQL Servers 2000
> (SP3). Is there any good guide available that discusses crucial points in
> this endeavour (e.g. possible topologies, what and how needs to be
monitored,
> potential issues to avoid, what to do in case of failures etc.)? I need a
> document that focuses on moving the replication into production
environment.
> I'm especially interested in alternative means of monitoring the
replication
> rather than the built-in one.
> --
> Many thanks,
> Oskar
>
|||Yeah, but are you completely sure that these sources give answers to the
specific questions I have? So far I was unable to find answers to my
questions in BOL.
"Hilary Cotter" wrote:
> You can check BOL, do a search on MSDN for transactional replication. You
> might want to consider my book
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:3F9E5C48-79C8-4D76-87C8-B9C2ABC6C840@.microsoft.com...
> monitored,
> environment.
> replication
>
>
I want to implement transactional replication on 2 MS SQL Servers 2000
(SP3). Is there any good guide available that discusses crucial points in
this endeavour (e.g. possible topologies, what and how needs to be monitored,
potential issues to avoid, what to do in case of failures etc.)? I need a
document that focuses on moving the replication into production environment.
I'm especially interested in alternative means of monitoring the replication
rather than the built-in one.
Many thanks,
Oskar
You can check BOL, do a search on MSDN for transactional replication. You
might want to consider my book
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:3F9E5C48-79C8-4D76-87C8-B9C2ABC6C840@.microsoft.com...
> Hi
> I want to implement transactional replication on 2 MS SQL Servers 2000
> (SP3). Is there any good guide available that discusses crucial points in
> this endeavour (e.g. possible topologies, what and how needs to be
monitored,
> potential issues to avoid, what to do in case of failures etc.)? I need a
> document that focuses on moving the replication into production
environment.
> I'm especially interested in alternative means of monitoring the
replication
> rather than the built-in one.
> --
> Many thanks,
> Oskar
>
|||Yeah, but are you completely sure that these sources give answers to the
specific questions I have? So far I was unable to find answers to my
questions in BOL.
"Hilary Cotter" wrote:
> You can check BOL, do a search on MSDN for transactional replication. You
> might want to consider my book
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:3F9E5C48-79C8-4D76-87C8-B9C2ABC6C840@.microsoft.com...
> monitored,
> environment.
> replication
>
>
Saturday, February 25, 2012
MS SQL 2000 sp3 problem
Recently we upgrade sql2000 with sp3 and had a problem with database full, we find that even thou "autogrow" was set database was full.
Any ideas?
Thanks,
alexIt's a long shot but, was your hard disk full? This would then not allow for your db to grow.|||I have found the answer to my question
A Timeout Occurs When a Database Is Automatically Expanding
http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
A database does not expand automatically although you have the autogrow option on.
The Errorlog file does not contain any messages that indicate that the database is full.
Client computers that try to modify data in this database receive a "Timeout Expired" message on their user transaction.
Thanks,
alex
Any ideas?
Thanks,
alexIt's a long shot but, was your hard disk full? This would then not allow for your db to grow.|||I have found the answer to my question
A Timeout Occurs When a Database Is Automatically Expanding
http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
A database does not expand automatically although you have the autogrow option on.
The Errorlog file does not contain any messages that indicate that the database is full.
Client computers that try to modify data in this database receive a "Timeout Expired" message on their user transaction.
Thanks,
alex
MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.
I am currently encounter a problem whereby my sql 2000 server repetitively generates error 1501 (severity 20) -- Sort Failure.
The information available on the web only covers MS SQL 6.5. Apparently, I was informed that this error should not have encountered in MS SQL 2000.
So, if there is anybody out there that can help me in troubleshooting this problem, please do help me as I am really stuck.Hi,
which state does the error message tell you ?
http://www.lcard.ru/~nail/sybase/error/13243.htm
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||The state of the error message is 1. The database is MS SQL 2000 with SP3.
Unfortunately, the error state is not listed in the URL document you specified.|||
What is the usage of TEMPDB in this environment?
I guess that couldbe a problem check this KBA http://support.microsoft.com/kb/826433 that explains bits and piece of these errors. Confirm the service pack level on SQL Server.
MS SQL 2000 SP3 (8.00.856) -- Error 1501 Severity 20.
I am currently encounter a problem whereby my sql 2000 server repetitively generates error 1501 (severity 20) -- Sort Failure.
The information available on the web only covers MS SQL 6.5. Apparently, I was informed that this error should not have encountered in MS SQL 2000.
So, if there is anybody out there that can help me in troubleshooting this problem, please do help me as I am really stuck.Hi,
which state does the error message tell you ?
http://www.lcard.ru/~nail/sybase/error/13243.htm
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||The state of the error message is 1. The database is MS SQL 2000 with SP3.
Unfortunately, the error state is not listed in the URL document you specified.|||
What is the usage of TEMPDB in this environment?
I guess that couldbe a problem check this KBA http://support.microsoft.com/kb/826433 that explains bits and piece of these errors. Confirm the service pack level on SQL Server.
Monday, February 20, 2012
MS SQL 2000 after patching with SP3
I have patched my MS SQL 2000 DB from SP2 to SP3.
I noticed from the enterprise manager the patch level indicates
8.00.760 (SP3).
However, using microsofts type 4 JDBC driver to connect to SQL2000, I got the following info when I used one of the APIs to retrieve the database version :
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3) .
Now... which one is having the bug? MS JDBC driver or MS SQL 2000 SP3 ??8.00.760 = 2000 SP3. JDBC was written before SP3.
Look at %windir%\sqlsp.log to see SP3 is correctly installed.|||Originally posted by ispaleny
8.00.760 = 2000 SP3. JDBC was written before SP3.
Look at %windir%\sqlsp.log to see SP3 is correctly installed.
Indeed Type 4 MS JDBC driver is written b4 service pack 3.
However, after patching MS SQL 2000, the driver should correctly retrieve the info from SQL 2000. Don't suppose that the JDBC driver has that value hard-coded in its coding?
Probably I would temporary revert to the JDBC-ODBC bridge to do a simple check..|||I tried to patch SP3 on MS sql 2000 standard edition, the JDBC driver reports it correctly.
Thus, seems like the only problem occurs on my MS Sql2000 Enterprise Edition.
I noticed from the enterprise manager the patch level indicates
8.00.760 (SP3).
However, using microsofts type 4 JDBC driver to connect to SQL2000, I got the following info when I used one of the APIs to retrieve the database version :
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3) .
Now... which one is having the bug? MS JDBC driver or MS SQL 2000 SP3 ??8.00.760 = 2000 SP3. JDBC was written before SP3.
Look at %windir%\sqlsp.log to see SP3 is correctly installed.|||Originally posted by ispaleny
8.00.760 = 2000 SP3. JDBC was written before SP3.
Look at %windir%\sqlsp.log to see SP3 is correctly installed.
Indeed Type 4 MS JDBC driver is written b4 service pack 3.
However, after patching MS SQL 2000, the driver should correctly retrieve the info from SQL 2000. Don't suppose that the JDBC driver has that value hard-coded in its coding?
Probably I would temporary revert to the JDBC-ODBC bridge to do a simple check..|||I tried to patch SP3 on MS sql 2000 standard edition, the JDBC driver reports it correctly.
Thus, seems like the only problem occurs on my MS Sql2000 Enterprise Edition.
Subscribe to:
Posts (Atom)