Board index MS SQL Server

SQL Server Performance Problem - Good query performance, bad update performance

SQL Server Performance Problem - Good query performance, bad update performance

Post by R Bake » Tue, 07 Nov 2000 04:00:00



We've run into a situation with a client who has just moved to the SQL
Server version of our application in which query performance is acceptable,
but updates are at least an order of magnitude too slow. Any thoughts on
what I might check for update-related performance problems? Their machine is
a dual-CPU 600MHZ monster, and my old 200MHZ single CPU Pentium Pro NT
machine is running circles around theirs. I can do 10 transactions in 10
seconds here, while theirs takes over 2 minutes to do a similar transaction
set.

Any thoughts?

Thanks,

Randy

 
 
 
Top

SQL Server Performance Problem - Good query performance, bad update performance

Post by Tibor Karasz » Tue, 07 Nov 2000 04:00:00


A couple of things to check...

Make sure that you haven't over-indexed the table(s).
Make sure that you *do* have an index(es) for the column(s) which you are using in the
UPDATE's WHERE clause.

--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.
FAQ at: http://www.sqlserverfaq.com


Quote:> We've run into a situation with a client who has just moved to the SQL
> Server version of our application in which query performance is acceptable,
> but updates are at least an order of magnitude too slow. Any thoughts on
> what I might check for update-related performance problems? Their machine is
> a dual-CPU 600MHZ monster, and my old 200MHZ single CPU Pentium Pro NT
> machine is running circles around theirs. I can do 10 transactions in 10
> seconds here, while theirs takes over 2 minutes to do a similar transaction
> set.

> Any thoughts?

> Thanks,

> Randy


 
 
 
Top

SQL Server Performance Problem - Good query performance, bad update performance

Post by R Bake » Tue, 07 Nov 2000 04:00:00


Thanks, Tibor.

Problem is that the databases are identical (except for hard-drive
organization), and my much slower machine is running circles around theirs.

Also, it appears that the number of round-trips to the server on his network
may also be an issue. Is one of the network libraries a better choice than
others in this regard?

Randy



> A couple of things to check...

> Make sure that you haven't over-indexed the table(s).
> Make sure that you *do* have an index(es) for the column(s) which you are
using in the
> UPDATE's WHERE clause.

> --
> Tibor Karaszi, SQL Server MVP
> Please reply to the newsgroup only, not by email.
> FAQ at: http://www.sqlserverfaq.com



> > We've run into a situation with a client who has just moved to the SQL
> > Server version of our application in which query performance is
acceptable,
> > but updates are at least an order of magnitude too slow. Any thoughts on
> > what I might check for update-related performance problems? Their
machine is
> > a dual-CPU 600MHZ monster, and my old 200MHZ single CPU Pentium Pro NT
> > machine is running circles around theirs. I can do 10 transactions in 10
> > seconds here, while theirs takes over 2 minutes to do a similar
transaction
> > set.

> > Any thoughts?

> > Thanks,

> > Randy

 
 
 
Top

SQL Server Performance Problem - Good query performance, bad update performance

Post by Tibor Karasz » Tue, 07 Nov 2000 04:00:00


Quote:> Problem is that the databases are identical (except for hard-drive
> organization), and my much slower machine is running circles around theirs.

Are they really identical? Sorry for questioning, but I've seen so many posts where
the turned out to not be the case. Load a backup from "machine A to machine B" to make
sure. Statistics might, for instance, make a huge difference...

Quote:> Problem is that the databases are identical (except for hard-drive
> organization),

Do you have transaction log on slow device (perhaps even RAID 5 without write cache)?
I don't think that it should matter that much, but worth checking.

Quote:> Also, it appears that the number of round-trips to the server on his network
> may also be an issue. Is one of the network libraries a better choice than
> others in this regard?

Can be in some cases. Does the same difference apply for read operations. If not, then
the netlib should not be the problem...
--
Tibor Karaszi, SQL Server MVP
Please reply to the newsgroup only, not by email.
FAQ at: http://www.sqlserverfaq.com


> Thanks, Tibor.

> Problem is that the databases are identical (except for hard-drive
> organization), and my much slower machine is running circles around theirs.

> Also, it appears that the number of round-trips to the server on his network
> may also be an issue. Is one of the network libraries a better choice than
> others in this regard?

> Randy



> > A couple of things to check...

> > Make sure that you haven't over-indexed the table(s).
> > Make sure that you *do* have an index(es) for the column(s) which you are
> using in the
> > UPDATE's WHERE clause.

> > --
> > Tibor Karaszi, SQL Server MVP
> > Please reply to the newsgroup only, not by email.
> > FAQ at: http://www.sqlserverfaq.com



> > > We've run into a situation with a client who has just moved to the SQL
> > > Server version of our application in which query performance is
> acceptable,
> > > but updates are at least an order of magnitude too slow. Any thoughts on
> > > what I might check for update-related performance problems? Their
> machine is
> > > a dual-CPU 600MHZ monster, and my old 200MHZ single CPU Pentium Pro NT
> > > machine is running circles around theirs. I can do 10 transactions in 10
> > > seconds here, while theirs takes over 2 minutes to do a similar
> transaction
> > > set.

> > > Any thoughts?

> > > Thanks,

> > > Randy

 
 
 
Top

SQL Server Performance Problem - Good query performance, bad update performance

Post by R Bake » Tue, 07 Nov 2000 04:00:00




Quote:> > Problem is that the databases are identical (except for hard-drive
> > organization), and my much slower machine is running circles around
theirs.
> Are they really identical? Sorry for questioning, but I've seen so many
posts where
> the turned out to not be the case. Load a backup from "machine A to
machine B" to make
> sure. Statistics might, for instance, make a huge difference...

Actually, this is exactly how the database was created -- I migrated their
old Access Database to SQL Server (using DTS and a SQL Server database
created via a script, not upsizing wizard), backed up the database, and sent
them the backup.

> > Problem is that the databases are identical (except for hard-drive
> > organization),
> Do you have transaction log on slow device (perhaps even RAID 5 without
write cache)?
> I don't think that it should matter that much, but worth checking.

> > Also, it appears that the number of round-trips to the server on his
network
> > may also be an issue. Is one of the network libraries a better choice
than
> > others in this regard?
> Can be in some cases. Does the same difference apply for read operations.
If not, then
> the netlib should not be the problem...
> --
> Tibor Karaszi, SQL Server MVP
> Please reply to the newsgroup only, not by email.
> FAQ at: http://www.sqlserverfaq.com



> > Thanks, Tibor.

> > Problem is that the databases are identical (except for hard-drive
> > organization), and my much slower machine is running circles around
theirs.

> > Also, it appears that the number of round-trips to the server on his
network
> > may also be an issue. Is one of the network libraries a better choice
than
> > others in this regard?

> > Randy

> > "Tibor Karaszi"


- Show quoted text -


> > > A couple of things to check...

> > > Make sure that you haven't over-indexed the table(s).
> > > Make sure that you *do* have an index(es) for the column(s) which you
are
> > using in the
> > > UPDATE's WHERE clause.

> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Please reply to the newsgroup only, not by email.
> > > FAQ at: http://www.sqlserverfaq.com



> > > > We've run into a situation with a client who has just moved to the
SQL
> > > > Server version of our application in which query performance is
> > acceptable,
> > > > but updates are at least an order of magnitude too slow. Any
thoughts on
> > > > what I might check for update-related performance problems? Their
> > machine is
> > > > a dual-CPU 600MHZ monster, and my old 200MHZ single CPU Pentium Pro
NT
> > > > machine is running circles around theirs. I can do 10 transactions
in 10
> > > > seconds here, while theirs takes over 2 minutes to do a similar
> > transaction
> > > > set.

> > > > Any thoughts?

> > > > Thanks,

> > > > Randy

 
 
 
Top

SQL Server Performance Problem - Good query performance, bad update performance

Post by Terry Harri » Tue, 07 Nov 2000 04:00:00


What kind of database contention is your customer experiencing?  Something can
run very well on a single user system but be slow with multiple users.

Have statistics been updated on the cutsomer's system recently?

How do the query plans compare between your system and the customer's?

I have also seen stored procedures suddenly change their performance and be
fixed with a re-compile.


>A couple of things to check...

>Make sure that you haven't over-indexed the table(s).
>Make sure that you *do* have an index(es) for the column(s) which you are using
>in the
>UPDATE's WHERE clause.

>--
>Tibor Karaszi, SQL Server MVP
>Please reply to the newsgroup only, not by email.
>FAQ at: http://www.sqlserverfaq.com



>> We've run into a situation with a client who has just moved to the SQL
>> Server version of our application in which query performance is acceptable,
>> but updates are at least an order of magnitude too slow. Any thoughts on
>> what I might check for update-related performance problems? Their machine is
>> a dual-CPU 600MHZ monster, and my old 200MHZ single CPU Pentium Pro NT
>> machine is running circles around theirs. I can do 10 transactions in 10
>> seconds here, while theirs takes over 2 minutes to do a similar transaction
>> set.

>> Any thoughts?

>> Thanks,

>> Randy

 
 
 
Top

SQL Server Performance Problem - Good query performance, bad update performance

Post by Erland Sommarsk » Tue, 07 Nov 2000 04:00:00



>We've run into a situation with a client who has just moved to the SQL
>Server version of our application in which query performance is
>acceptable, but updates are at least an order of magnitude too slow. Any
>thoughts on what I might check for update-related performance problems?
>Their machine is a dual-CPU 600MHZ monster, and my old 200MHZ single CPU
>Pentium Pro NT machine is running circles around theirs. I can do 10
>transactions in 10 seconds here, while theirs takes over 2 minutes to do a
>similar transaction set.

Obviously there is an index which is not being used on the big
machine. UPDATE STATISTICS. Use SET STATISTICS IO ON to catch
where the bottleneck is.

I don't know how much the optimizer takes the hardward in regard,
but there are different numbers of processors on the machines, and
this can affect the query plan. Sometimes the plans using parallellisms
are worse than those without...

--

This is signature isn't half as witty as it used to be.

 
 
 
Top

SQL Server Performance Problem - Good query performance, bad update performance

Post by R Bake » Wed, 08 Nov 2000 04:00:00


I fixed this by changing the client network library from Named Pipes to
TCP/IP. Has anyone seen anything like this before?

After making the change, a screen that used to take almost 2 minutes to
close (probably 50-100 update statements) closed in less than 2 seconds.

Randy



> A couple of things to check...

> Make sure that you haven't over-indexed the table(s).
> Make sure that you *do* have an index(es) for the column(s) which you are
using in the
> UPDATE's WHERE clause.

> --
> Tibor Karaszi, SQL Server MVP
> Please reply to the newsgroup only, not by email.
> FAQ at: http://www.sqlserverfaq.com



> > We've run into a situation with a client who has just moved to the SQL
> > Server version of our application in which query performance is
acceptable,
> > but updates are at least an order of magnitude too slow. Any thoughts on
> > what I might check for update-related performance problems? Their
machine is
> > a dual-CPU 600MHZ monster, and my old 200MHZ single CPU Pentium Pro NT
> > machine is running circles around theirs. I can do 10 transactions in 10
> > seconds here, while theirs takes over 2 minutes to do a similar
transaction
> > set.

> > Any thoughts?

> > Thanks,

> > Randy

 
 
 
Top

1. Bad SQL Server performance on new W2K Server

Hello,

We are experiencing bad performance on a new W2K Server OS with SQL Server
7.0.  We are comparing the performance against SQL Server on a networked
Workstation running W2K (not Server OS) using identical hardware specs.

It appears that for some reason, at a certain point in the process execution
we get a large number of continuous "Disconnect then ExistingConnection"
events/traces and then the query performance falls apart for approximately 1
minute, then resumes at its expected performance level.  When comparing the
trace files to the same SQL Server database running on a Workstation with
identical hardware specifications. we don't see the problem with
"Disconnection then ExistingConnection" events in the trace file and the
query performance is as expected.

Can anybody shed light on why the W2K Server with SQL Server 7.0 is
exhibiting this kind of behavior?

Best Regards,

Bill

2. Q:Is there I-7.1 for Sun/Solaris

3. Thanks for responses on System Performance - it is update performance

4. Recommendation required for database application

5. Dual CPU box has worse SQL Server performance than single

6. Close an ADO control

7. cascade update performance problem in SQL Server 2000

8. Accessing SQL 7.0 through ASP using SQL authentication rather than NT authentication

9. Will it remarkablly affect performance of SQL Server if there is a bad disk amon

10. Bad SQL Server Performance

11. Bad SQL performance on a NT Primary Domain Server

12. Sql server page locks and bad performance ?

13. MS SQL Server 2000 - More powerful machine...worst performance


All times are UTC
Board index