MS SQL SERVER WITH JAVA help plz?

What r the pros and conditions of using MSsql server ?

what the effect of data on the performance in case of MS SQL server?is it good enough tohandle data overloads?

how good the java ms sql server combo works in comparison to other databases !!

SQL Server works great with Java! More here:

http://msdn2.microsoft.com/en-us/data/aa937724.aspx

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
|||- 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

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

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

Ms SQL server db options for performance issues after db corruption

Now the odd thing here is that although some of the
options will ultimatly improve performance, quite a few of
them will not.
Fistly the update statistics / create statistics. This
will improve performace but only when you change / create
something i.e. and index. I have this on my server.
Recursive triggers only allow as the wording states
recursive triggers and will not do anything to directly to
improve the performance. This is an application level
thing, personally I hate recursion.
Quoted Identifiers will not improve performance but you
may find some of your SQL will not work if you have not
had it on before.
Torn Page detection ?, important of you don't have
anything in place that can detect it, however it will
effect performance.
OK then cursor options. First rule of thumb. If you can
get away from using cursors then don't use them, they are
really slow. There are a lot of other options but its
probably better if you look it up on the net.
Other things you can try...
As you have just put in a new disk your datafiles are
going to need defragging (look up the DBCC DBREINDEX
command)
Also you may want to re-compile your SP after you perform
the re-index.
Anyway there are a LOT of other things you can try, too
many to put here so I surgest you have a look on the web.
Peter
"Choose a job you love, and you will never have to work a
day in your life."
Confucius

>--Original Message--
>Hello SQL forum readers,
>I have a customer using Ms SQL Server 2000 SP3 installed
on optical RAID-5
>Disk Array (IBM Fast200 SAN).
>After a disk failure his production db was corrupted and
since the failed
>disk was replaced, cust. restored a valid backup
>in order to support his business operations (with one day
transcations
>loss).
>We have dome a lot of troubleshooting and investigation
(traces, logs,
>dumps) in order to identify what caused the corruption.
>But now, cust. faces low performance problems.
>I have already suggested to use additionally the
following options :
>A) Auto Options:
>Auot create statistics
>Auto update statistics
>B) Recovery Options:
>Recursive Triggers
>I would be much appreciated If I could have any advise
regararding any
>additional db option setting that could assist :
>a) What about Quoted Identifiers or other SQL option?
>b) What about Torn Page Detection (recovery option)
>c) Any other cursor options that could assist?
>Is there additional MEM/CPU overhead by enabling those
options?
>I have also advised cust, to execute the command SET
SHOWPLAN_ALL in order
>to gather details abour executed statements
>Anything else I can do?
>Any advise will be much appreciated.
>Thanks in Advance
>Regards, Nick Dakoronias
>IBM Athens/Greece
>
>
>.
>
- Peter,
Thanks for your clarifications and the useful tips
- Narayana,
We try to investigate what caused the SQL performance degradation.
We supsect that the HW failure in disk array caused db corruption. This is
also verified by Ms SQL error logs and dumps.
But on the other hand, Fast optical diak array log as well as, management
console indicate that after the replacement of the
defective disk the whole RAID 5 array is healthy and the log is clear of
errors.
Regarding the Performance Benchamrk, it is just enough to mention a
performance degradation of about 70 % either using the
same array (8 x 36.4GB) or another (5 x 74.8 GB). This percentage could not
be justified by using bad indexes or triggers only.
Don't you agree?
- Curt,
This is actually an ERP oriented -business critical- application using SQL
server instances. It is related to Logistics, Products Shipment, Inventory,
etc.
___________________
My additional comments:
I also think that it is essential to determine the db queries performance by
using the SET statement to enable the SHOWPLAN, STATISTICS IO, STATISTICS
TIME, and
STATISTICS profile options. For Example:
SHOWPLAN describes the method chosen by the SQL Server query optimizer to
retrieve data. (SET SHOWPLAN_ALL).
STATISTICS TIME displays the amount of time (in milliseconds) required to
parse, compile, and execute a query. (SET STATISTICS TIME).
STATISTICS IO reports information about the number of scans, logical reads
(pages accessed in cache), and physical reads (number of times the disk was
accessed) for each table referenced in the statement. (SET STATISTICS IO).
STATISTICS PROFILE displays a result set after each executed query
representing a Profile of the execution of the query (SET STATISTICS
PROFILE)
Use Index Tuning Wizard to obtain a recommendation abour the best mix of
indexes for a database given a workload and analyze the effects of the
proposed changes, including index usage
and performance of queries in workload.
Thanks and Regards,
Nick Dakoronias
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:091601c494e4$fce077c0$a401280a@.phx.gbl...[vbcol=seagreen]
> Now the odd thing here is that although some of the
> options will ultimatly improve performance, quite a few of
> them will not.
> Fistly the update statistics / create statistics. This
> will improve performace but only when you change / create
> something i.e. and index. I have this on my server.
> Recursive triggers only allow as the wording states
> recursive triggers and will not do anything to directly to
> improve the performance. This is an application level
> thing, personally I hate recursion.
> Quoted Identifiers will not improve performance but you
> may find some of your SQL will not work if you have not
> had it on before.
> Torn Page detection ?, important of you don't have
> anything in place that can detect it, however it will
> effect performance.
> OK then cursor options. First rule of thumb. If you can
> get away from using cursors then don't use them, they are
> really slow. There are a lot of other options but its
> probably better if you look it up on the net.
> Other things you can try...
> As you have just put in a new disk your datafiles are
> going to need defragging (look up the DBCC DBREINDEX
> command)
> Also you may want to re-compile your SP after you perform
> the re-index.
> Anyway there are a LOT of other things you can try, too
> many to put here so I surgest you have a look on the web.
> Peter
> "Choose a job you love, and you will never have to work a
> day in your life."
> Confucius
>
> on optical RAID-5
> since the failed
> transcations
> (traces, logs,
> following options :
> regararding any
> options?
> SHOWPLAN_ALL in order

MS SQL Server 2005, performance; vs InterBase

Hi,
1/
I would like to ask if SQL Server 2005 Express could be used as a web
database as far as performance (higher number of simultaneous users) is
concerned.
2/
I would like to see the comparison between InterBase from Borland and SQL
Server 2005 Express. Does exist such comparison somewhere on Internet?
3/
Can be MS SQL Server 2005 Express installed on MS Windows Server 2003 Web
Edition? (MS SQL Server 2005 cannot be installed).
Thank you,
Lubomir
Hi
1. Yes, once it is released. The Beta EULA agreement does not allow for use
in production. Look at the scalability limitations on
http://www.microsoft.com/sql/express/
2. As SQL Server 2005 is in Beta, doing benchmarking will not show the true
performance of the released product. It is also a violation of EULA.
3. No. It is a Windows 2003 Web Edition licensing limitation where no
applications may be installed on the machine unless they are used inside IIS.
(http://www.microsoft.com/windowsserv...rview/web.mspx)
Regards
Mike
"Lubomir" wrote:

> Hi,
> 1/
> I would like to ask if SQL Server 2005 Express could be used as a web
> database as far as performance (higher number of simultaneous users) is
> concerned.
> 2/
> I would like to see the comparison between InterBase from Borland and SQL
> Server 2005 Express. Does exist such comparison somewhere on Internet?
> 3/
> Can be MS SQL Server 2005 Express installed on MS Windows Server 2003 Web
> Edition? (MS SQL Server 2005 cannot be installed).
>
> Thank you,
> Lubomir
>

MS SQL performance from 10" to 3 minutes

Hello all !

I am runing from .NET application an SQL Query
it normally return the rows in 10 seconds
but time to time the application turn 2 or 3 minutes and nearlly crash (or crash)

with exactly the same datas in database

what can be the reasons ?

thank youcheck whether the session is getting expired or not if not kill it.|||other reason could be lock put on the table during the transaction which may keep the DB server busy.
Also check if some other query seeks a large resultset from DB.|||ppavan21 if I kill the session and a user is logged-in he will be thrown, I cannot do it , or do you see a solution ?

wash : is there a way to unlock ? ot what can I do ?

on 5 rows it takes normally less than one second, sometimes it can turn a few minutes and crash with exactly the sames datas

thank you|||the reasons for this can vary widely.

things to check...

1. open up the task manager to see if it is the sqlserver process consuming resources. Are you running anything on the machine? IIS? exchange?

2. run sp_who\sp_who2\sp_lock to look for blocking\resource intensive operations or excessive locking.

3. Open up the performance monitor and make sure you disk que length is under 3.

4. Have you looked at the execution plan of the query that varies in execution time? Are there any table\index scan as opposed to index seeks in the plan? If the query can return vastly varying amounts of data, have you tried adding WITH RECOMPILE to the query? Have you recompiled the stored procedure lately? Are the indexes that the query is using heavily fragmented?

That should keep you busy.|||RECOMPILE ? i didn't know it was even possible
how do yo do it ?|||recompiling is sometimes beneficial if there has been a large amount of data added to your database recently which can have the effect of making your execution plan out of date.

see sp_recompile in Books Online.|||Sean,

I believe you assume That this is a sproc

I got Money that it's not|||oh probably not. dude can probably use a little BOL reading anyways.

MS SQL 2005: Performance – normal CPU vs CPU DUO/4 CORE

Hello,Is performance of web application (ASP.NET + SQL Server 2005 Wrg edition + Win Server 2003 Web edition) running on server with one core duo/4 CPU generally comparable to the performance of the same application running on the same server with 2/4 physical CPU's?Thank you for your ideas!Jan

Hi Jan,

Of cource on a multiple core CPU, the ASP.NET and SQL Server performance will be better than a single core CPU.

Because the IIS and SQL Server will optimize the performance according to the core units. In this case, better performance is gain.

MS SQL 2005 performance issues

After migrating to 2005, I started noticing some performance problems with my queries. Here is one of them. The query takes some 40 seconds to execute for the first time with intense disk operation. Next time I run it the result comes back in less than a second. however, if I make a minor change to one of the parameter, it takes ~25 seconds with 100% CPU utilization and 0% of disk utilization. So, I'm assuming that all data has been cached, but I can't imagine why it takes so long to retrieve data from the memory?

BTW execution plans look completely identical for those queries.

Here is the query example:

select quotes.quote_id from indicators indicators_1, quotes, quotes quotes_1, quotes quotes_2, ranges
ranges_1 where not (1. * quotes_1.volume / quotes_2.volume < 3.54 and
indicators_1.RngStdDev25 / ranges_1.RngSMA25 < 1.2 ) and
quotes.quote_id - 1 = indicators_1.quote_id and quotes.quote_id - 1 = quotes_1.quote_id and
quotes.quote_id - 2 = quotes_2.quote_id and quotes.quote_id - 1 = ranges_1.quote_id and quotes.quote_id in
(select quote_id from trades where max_period = 2 and profit_target = 0.1 and strategy_id = 228)

And the parameter I'm changing is in red.

I will really appreciate if someone can help as I've started thinking about moving back to 2000 where no such problems existed.

Andy

I figured out that what actually takes so long is compiling the query. In some cases adding "option (fast 1)" helps, but in others doesn't.
Hell, I really have no idea why compiling the query but not retrieving the actual data is taking so much time.

|||

some more food for thoughts... the query below takes ~10 seconds to compile
all tables have primary key on quote_id column:

select quotes.quote_id
from quotes inner join indicators indicators_1 on quotes.quote_id = indicators_1.quote_id
inner join ranges ranges_1 on quotes.quote_id = ranges_1.quote_id
inner join prices prices_1 on quotes.quote_id = prices_1.quote_id
where quotes.quote_id in (select quote_id from trades where max_period = 2 and profit_target = 0.1 and strategy_id = 228)
option (recompile)

|||

So, now here's something that anyone can reproduce. Seems to be a bug (or feature) in SQL Server 2005.

The following script creates the tables and populates them with data:

CREATE TABLE [dbo].[test1](
[quote_id] [int] NOT NULL,
[ClSMA3] [real] NULL,
[ClSMA5] [real] NULL,
[ClSMA10] [real] NULL,
[ClSMA25] [real] NULL,
[ClSMA50] [real] NULL,
[ClMin3] [real] NULL,
[ClMin5] [real] NULL,
[ClMin10] [real] NULL,
[ClMin25] [real] NULL,
[ClMin50] [real] NULL,
[ClMax3] [real] NULL,
[ClMax5] [real] NULL,
[ClMax10] [real] NULL,
[ClMax25] [real] NULL,
[ClMax50] [real] NULL,
[LoMin3] [real] NULL,
[LoMin5] [real] NULL,
[LoMin10] [real] NULL,
[LoMin25] [real] NULL,
[LoMin50] [real] NULL,
[HiMax3] [real] NULL,
[HiMax5] [real] NULL,
[HiMax10] [real] NULL,
[HiMax25] [real] NULL,
[HiMax50] [real] NULL,
[LoMinBar3] [int] NULL,
[LoMinBar5] [int] NULL,
[LoMinBar10] [int] NULL,
[LoMinBar25] [int] NULL,
[LoMinBar50] [int] NULL,
[HiMaxBar3] [int] NULL,
[HiMaxBar5] [int] NULL,
[HiMaxBar10] [int] NULL,
[HiMaxBar25] [int] NULL,
[HiMaxBar50] [int] NULL
)

CREATE CLUSTERED INDEX [pk_test1] ON [dbo].[test1]
(
[quote_id] ASC
)

CREATE TABLE [dbo].[test2](
[quote_id] [int] NOT NULL,
[max_period] [smallint] NOT NULL,
[profit_target] [real] NOT NULL,
[trade_action] [smallint] NULL,
[open_date] [smalldatetime] NULL,
[open_price] [smallmoney] NULL,
[close_date] [smalldatetime] NULL,
[close_price] [smallmoney] NULL,
[strategy_id] [int] NULL,
[flag] [tinyint] NULL
) ON [PRIMARY]

DECLARE @.C INT
SET @.C = 0
WHILE (@.C < 13000)
BEGIN
INSERT INTO test2 (quote_id, max_period, profit_target) values (@.C * 290, 1, 1.0)
SET @.C = @.C + 1
END

SET @.C = 0
WHILE (@.C < 3800000)
BEGIN
INSERT INTO [test].[dbo].[test1]
([quote_id]
,[ClSMA3]
,[ClSMA5]
,[ClSMA10]
,[ClSMA25]
,[ClSMA50]
,[ClMin3]
,[ClMin5]
,[ClMin10]
,[ClMin25]
,[ClMin50]
,[ClMax3]
,[ClMax5]
,[ClMax10]
,[ClMax25]
,[ClMax50]
,[LoMin3]
,[LoMin5]
,[LoMin10]
,[LoMin25]
,[LoMin50]
,[HiMax3]
,[HiMax5]
,[HiMax10]
,[HiMax25]
,[HiMax50]
,[LoMinBar3]
,[LoMinBar5]
,[LoMinBar10]
,[LoMinBar25]
,[LoMinBar50]
,[HiMaxBar3]
,[HiMaxBar5]
,[HiMaxBar10]
,[HiMaxBar25]
,[HiMaxBar50])
VALUES
(@.C
,@.C/2
,@.C/3
,@.C/4
,@.C/5
,@.C/6
,@.C/7
,@.C/8
,@.C/9
,@.C/10
,@.C/11
,@.C/12
,@.C/13
,@.C/14
,@.C/15
,@.C/16
,@.C/17
,@.C/18
,@.C/19
,@.C/20
,@.C/21
,@.C/22
,@.C/23
,@.C/24
,@.C/25
,@.C/26
,@.C/27
,@.C/28
,@.C/29
,@.C/30
,@.C/31
,@.C/32
,@.C/33
,@.C/34
,@.C/35
,@.C/36)
SET @.C = @.C + 1
END

--

The query below takes too long to compile (10 seconds in my environment), however in 2000 returns in less than a second

select count(test2.quote_id)
from test2
where
test2.quote_id in (select quote_id from test1)
and test2.quote_id - 1 in (select quote_id from test1)
and test2.quote_id - 2 in (select quote_id from test1)
and test2.quote_id - 3 in (select quote_id from test1)
and test2.quote_id - 4 in (select quote_id from test1)
and test2.quote_id - 5 in (select quote_id from test1)

The query below takes forever to execute. Unless you change index pk_test1 from CLUSTERED to NONCLUSTERED. In such case it executes in fraction of a second...

select test2.quote_id
from test2
inner join test1 on test1.quote_id >= test2.quote_id - 6 AND test1.quote_id <= test2.quote_id
group by test2.quote_id
having count(*) = 7

|||

Hi, Andy! I'm glad you've provided a repro case; I was about to write a long note full of questions about your tables, row counts, indexes, and so on.

While I'm waiting for the INSERT script to run, I'm wondering if it is by design that your repro script doesn't have any indexes on test2.

|||

Mike Blaszczak wrote:

While I'm waiting for the INSERT script to run, I'm wondering if it is by design that your repro script doesn't have any indexes on test2.

Hi Mike,

Thank you very much for your help. As to your question, in my original schema I had clustered index for quote_id column on test2 table, but it doesn't seem to affect those queries, this is why I didn't include it. Especially since there are not too many records (13K) in this table.

|||

Record count isn't really relevant; you want to have an index to provide a better access path. If you have an index on the quote_id field of [test2] and the server needs to scan all the quote_id fields, it can do so by reading the index and densely reading only the quote_id fields. Without the index, it has to read the table pages which include a lot of other data besides the needed quote_id field. It does a bunch more I/O that way.

Unfortunately, my machine to test things for other people is dying; the C drive is starting to throw read errors, and so I'm not sure how much more I'll be able to help.

Your queries degenerate to six-way joins. What I believe is happening is that, in 2005, we see a very high query cost so the QO tries to run longer in order to look for a better plan -- one that's cheaper. The QO spends its time looking at all the alternatives to try to find a better way through the query.

It's disappointing that there so much time spent compiling for your query, but anyone who's benefited from the QO eventually finding a better plan certainly thinks that the extra compile time is a feature.

You've apparently tried to find a better way though the query, yourself; writing different SQL that should get the same result. I don't think there's anything you can do to make the query look better--there's no way to help the server know the correlation between the query_id values in one row and the query_id-6 values in the other rows. I've played around with adding computed columns to hold the range, then creating an index on those:

ALTER TABLE TEST2 ADD
MinMatch AS (Quote_ID - 5),
MaxMatch AS (Quote_ID)

select test2.quote_id
from test2
inner join test1 on test1.quote_id between test2.MinMatch AND test2.MaxMatch

but I'm afraid I can't say if it is performing any better as the machine I've been using to play with my queries has a failing drive, and that makes any measurements I take rather invalid.

You might try investigating a query that uses a loop and a cursor; this may be one instance where you can pull off better performance with a cursor than with set-based operations. Or, see what you can do with your model.

Also, please be sure you have current statistics on the tables. I've seen some bad cardinality estimates on my machine, but I can't investigate further with the failing drive and all ...

|||

Thank you so much Mike for such detailed analysis.

yes record count does matter, as my queries take too long to compile only in case if there's many records in test2 table. What I'm going to do is switch back to SQL Server 2000 as I don't really need new features that come with 2005 but those performance issues are killing me.

Sorry to hear you've got hardware problems.

Thanks again and good luck

MS SQL 2005 + Windows Server 2003 SP2

At first I thought it was just me, but has anyone else noticed that SQL 2005 suffers a performance hit when you install Windows Server 2003 SP2?

Since SP2, memory and hard drive usage has gone through the roof on a number of servers I use. It may just be coincidence... but it seems unlikely.

We are running one of our production clusters on Windows Server 2003 x64 SP2 and SQL Server 2005 x64 SP2 (Build 3159) without any trouble.

We also recently upgrade our development servers to Win2K3 x64 SP2 and SQL Server 2005 x64 SP2 (Build 3175) again without any issues so far.

Which build of SQL Server are you running?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||I think really just coincidence. Can you reccomend is the first step to diagnosing a SQL 2005 server's performance issues?|||

Here are a couple of blog posts that might help:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!526.entry

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!356.entry

|||Nice posts, thanks Glenn.

MS SQL 2000 Server counters problem

There is a problem with using MS SQL 2000 Server performance counters.
Performance counters for MS SQL aren't visible in perfmon. I've checke
that there is a entry in registry (according to
http://support.microsoft.com/default.aspx?scid=kb;en-us;152513) and
appropriate dll required to perfmon for MS MSQL seems to be ok.
What can generate this problem ?
Please, advice me,
Regards,
Robert Pater
R_pater@.hotmail.comOne is SP4, seconf is SP2.
I'm curently off site so I can't run yours query :-(
R
Edgardo Valdez, MCTS / MCITP
<EdgardoValdezMCTSMC...@.discussions.microsoft.com> napisal(a):
> Do you get any results for the following query?
> use master
> go
> select * from sysperfinfo
> What service pack is your SQL 2000 server?
> "Madroy" wrote:
> > There is a problem with using MS SQL 2000 Server performance counters.
> > Performance counters for MS SQL aren't visible in perfmon. I've checke
> > that there is a entry in registry (according to
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;152513) and
> > appropriate dll required to perfmon for MS MSQL seems to be ok.
> >
> > What can generate this problem ?
> >
> > Please, advice me,
> >
> > Regards,
> > Robert Pater
> > R_pater@.hotmail.com
> >
> >|||Yes,
Edgardo napisal(a):
> Are they both having the same problem?
> "Madroy" wrote:
> > One is SP4, seconf is SP2.
> > I'm curently off site so I can't run yours query :-(
> >
> >
> > R
> >
> > Edgardo Valdez, MCTS / MCITP
> > <EdgardoValdezMCTSMC...@.discussions.microsoft.com> napisal(a):
> > > Do you get any results for the following query?
> > >
> > > use master
> > > go
> > > select * from sysperfinfo
> > >
> > > What service pack is your SQL 2000 server?
> > >
> > > "Madroy" wrote:
> > >
> > > > There is a problem with using MS SQL 2000 Server performance counters.
> > > > Performance counters for MS SQL aren't visible in perfmon. I've checke
> > > > that there is a entry in registry (according to
> > > > http://support.microsoft.com/default.aspx?scid=kb;en-us;152513) and
> > > > appropriate dll required to perfmon for MS MSQL seems to be ok.
> > > >
> > > > What can generate this problem ?
> > > >
> > > > Please, advice me,
> > > >
> > > > Regards,
> > > > Robert Pater
> > > > R_pater@.hotmail.com
> > > >
> > > >
> >
> >

MS SQL 2000 Server counters problem

There is a problem with using MS SQL 2000 Server performance counters.
Performance counters for MS SQL aren't visible in perfmon. I've checke
that there is a entry in registry (according to
http://support.microsoft.com/defaul...kb;en-us;152513) and
appropriate dll required to perfmon for MS MSQL seems to be ok.
What can generate this problem ?
Please, advice me,
Regards,
Robert Pater
R_pater@.hotmail.comDo you get any results for the following query?
use master
go
select * from sysperfinfo
What service pack is your SQL 2000 server?
"Madroy" wrote:

> There is a problem with using MS SQL 2000 Server performance counters.
> Performance counters for MS SQL aren't visible in perfmon. I've checke
> that there is a entry in registry (according to
> http://support.microsoft.com/defaul...kb;en-us;152513) and
> appropriate dll required to perfmon for MS MSQL seems to be ok.
> What can generate this problem ?
> Please, advice me,
> Regards,
> Robert Pater
> R_pater@.hotmail.com
>|||One is SP4, seconf is SP2.
I'm curently off site so I can't run yours query :-(
R
Edgardo Valdez, MCTS / MCITP
<EdgardoValdezMCTSMC...@.discussions.microsoft.com> napisal(a):[vbcol=seagreen]
> Do you get any results for the following query?
> use master
> go
> select * from sysperfinfo
> What service pack is your SQL 2000 server?
> "Madroy" wrote:
>|||Are they both having the same problem?
"Madroy" wrote:

> One is SP4, seconf is SP2.
> I'm curently off site so I can't run yours query :-(
>
> R
> Edgardo Valdez, MCTS / MCITP
> <EdgardoValdezMCTSMC...@.discussions.microsoft.com> napisal(a):
>|||Yes,
Edgardo napisal(a):[vbcol=seagreen]
> Are they both having the same problem?
> "Madroy" wrote:
>

MS SQL 2000 Server counters problem

There is a problem with using MS SQL 2000 Server performance counters.
Performance counters for MS SQL aren't visible in perfmon. I've checke
that there is a entry in registry (according to
http://support.microsoft.com/default.aspx?scid=kb;en-us;152513) and
appropriate dll required to perfmon for MS MSQL seems to be ok.
What can generate this problem ?
Please, advice me,
Regards,
Robert Pater
R_pater@.hotmail.com
Do you get any results for the following query?
use master
go
select * from sysperfinfo
What service pack is your SQL 2000 server?
"Madroy" wrote:

> There is a problem with using MS SQL 2000 Server performance counters.
> Performance counters for MS SQL aren't visible in perfmon. I've checke
> that there is a entry in registry (according to
> http://support.microsoft.com/default.aspx?scid=kb;en-us;152513) and
> appropriate dll required to perfmon for MS MSQL seems to be ok.
> What can generate this problem ?
> Please, advice me,
> Regards,
> Robert Pater
> R_pater@.hotmail.com
>
|||One is SP4, seconf is SP2.
I'm curently off site so I can't run yours query :-(
R
Edgardo Valdez, MCTS / MCITP
<EdgardoValdezMCTSMC...@.discussions.microsoft.com> napisal(a):[vbcol=seagreen]
> Do you get any results for the following query?
> use master
> go
> select * from sysperfinfo
> What service pack is your SQL 2000 server?
> "Madroy" wrote:
|||Are they both having the same problem?
"Madroy" wrote:

> One is SP4, seconf is SP2.
> I'm curently off site so I can't run yours query :-(
>
> R
> Edgardo Valdez, MCTS / MCITP
> <EdgardoValdezMCTSMC...@.discussions.microsoft.com> napisal(a):
>
|||Yes,
Edgardo napisal(a):[vbcol=seagreen]
> Are they both having the same problem?
> "Madroy" wrote: