Odd Performance Problem

Odd Performance Problem

Post by Roy Harv » Tue, 17 Dec 1996 04:00:00



Russel,

When execution times are so wildly different, look for a difference in
the execution plan.  This can be caused by differences in index
definitions, or out of date index statistics.  Use showplan to see
what is really happening in the different instances.

If I were to take a wild guess it would be that on the slow server the
keys on the two tables are of a different data type.  This difference
could be as subtle as one allowing NULLs, while the other does not.
Such a difference will cause the optimizer to dismiss any index on the
column from consideration.

Unrelated trivial question.  Machine Two is described as a Dual-486
Compaq Proliant.  Did they actually make any ProLiants with 486
processors?  I thought the ProLiants started with 60 or 66MHz
Pentiums.

Roy


>We have a puzzling performance problem with a simple SQL join of two tables
>on a production SQL Server.  This is a primary key to primary key join,
>both tables are indexed uniquely on the primary key.
>TableA   (  3,200 rows, 28 bytes wide)
>    KeyNum  int,
>    four more int and datetime fields.
>TableB    (21,000 rows, max 850 bytes wide)
>    KeyNum  int,
>    Name            varchar(30),
>    eightly-some more fields, about half are varying character.
>Select a.keynum, b.name from tablea a, tableb b where a.keynum = b.keynum
>The query on three machines runs as follows, all with minimal disk
>activity:
>    Machine One     45 minutes at 100% CPU load
>    Machine Two     3 seconds at 30% CPU load peak
>    Machine Three   3 seconds at 30% CPU load peak
>Machine One
>Single-Pentium Pro Compaq Prosignia, 112 Meg RAM, NT 3.51 (1057), SQL
>Server 6.00.151 (SP 3),
>Internet Information Server, SNA Server,    DataMirror (tool for AS/400 to SQL
>Server Replication)
>Machine Two
>Dual-486 Compaq Proliant, 64 Meg RAM, NT 4.00 (1381), SQL Server 6.00.151
>Machine Three
>Single Pentium Compaq Prolinea, 32 Meg RAM,         NT 3.51 (1057), SQL Server
>6.00.151
>SQL configurations are as close to the same as is possible, given the
>differing hardware.  Non-joined selects on these tables both return
>quickly, even on Machine One.
>What we have tried that did NOT help:  
>    Removed SNA Server and DataMirror temporarily from Machine One with no
>change in behavior.  (Have not yet tried removing IIS.  This is a
>production server, but we will try that next.)  
>    Created clustered indexes, used  forceplan and index hints to control the
>join, and so forth.  Some options were even worse.
>What we tried that DID make a difference:
>    Redefined TableB, changing all varying character strings to fixed length
>character strings.  The query came in at about 3 seconds.
>It is almost certain that we are missing something here, since it is
>unlikely that we should change a varying length fields to fixed length.  My
>assumption is some configuration mistake that we cannot see because of (a)
>ignorance or (b) foolishness.  If anyone has some insight, we would
>appreciate hearing from you.

 
 
 

Odd Performance Problem

Post by Russell Field » Wed, 18 Dec 1996 04:00:00


Roy was right, matching the key types caused the problem on Machine One to
go away.

Now, why didn't the same problem manifest itself on other servers?

 
 
 

Odd Performance Problem

Post by Russell Field » Wed, 18 Dec 1996 04:00:00


Will double check the execution plan.  We did create the plans and examined
them closely, but did not see anything anomalous between machines.
Certainly a reexamination would not hurt.  Key columns are indeed both
integer and neither allows nulls.  The indexes are unique, but not
clustered.  

Machine Two is a Proliant 2000, which did come with 486 or Pentium
processors back when we bought it.  (It was a development server, so we cut
cost by buying the 486 version.) The current Proliants only offer Pentiums
and Pentium Pros.

 
 
 

Odd Performance Problem

Post by Russell Field » Sat, 21 Dec 1996 04:00:00


Machine Two and Three are joining Int with Decimal( 18 ,0)
Machine One joined Int with Decimal( 6 ,0).

 
 
 

Odd Performance Problem

Post by Roy Harv » Sat, 21 Dec 1996 04:00:00


Russel,

That is interesting.  Apparently SQL Server is starting to get a
little bit smarter and consider an index in at least some
circumstances where the comparison is of different types.

Thanks for following up on that.

Roy


>Machine Two and Three are joining Int with Decimal( 18 ,0)
>Machine One joined Int with Decimal( 6 ,0).

 
 
 

Odd Performance Problem

Post by David Solomo » Tue, 31 Dec 1996 04:00:00


Hi, Roy!

Just a thought ... Joining an integer to a decimal(6,0) is different
from joining to a decimal (18,0), right? In one case, the integer can't
be converted to the decimal, in the other it can (no possible
overflows). So that *could* limit the direction in which the server
could use an index, right?

David Solomon
metis technologies


> Russel,

> That is interesting.  Apparently SQL Server is starting to get a
> little bit smarter and consider an index in at least some
> circumstances where the comparison is of different types.

> Thanks for following up on that.

> Roy


> >Machine Two and Three are joining Int with Decimal( 18 ,0)
> >Machine One joined Int with Decimal( 6 ,0).

 
 
 

Odd Performance Problem

Post by Roy Harv » Wed, 01 Jan 1997 04:00:00


David,

True.  The interesting point to me was that I understood (possibly
incorrectly) that any difference in type, even one a simple as char(3)
vs. varchar(3), or varchar(3) vs. varchar(4), or int vs. smallint,
would cause the optimizer to not even consider an index.  I am happy
to see that this is not so.

Roy


>Hi, Roy!
>Just a thought ... Joining an integer to a decimal(6,0) is different
>from joining to a decimal (18,0), right? In one case, the integer can't
>be converted to the decimal, in the other it can (no possible
>overflows). So that *could* limit the direction in which the server
>could use an index, right?
>David Solomon
>metis technologies

>> Russel,

>> That is interesting.  Apparently SQL Server is starting to get a
>> little bit smarter and consider an index in at least some
>> circumstances where the comparison is of different types.

>> Thanks for following up on that.

>> Roy


>> >Machine Two and Three are joining Int with Decimal( 18 ,0)
>> >Machine One joined Int with Decimal( 6 ,0).

 
 
 

1. Odd performance problem

Hi,

I'm running SQL Server 7 SP3 on Windows 2000, using an ODBC-driven
client application.

I start Enterprise Manager on the local machine running SQL Server and I
navigate until it is showing me the list of tables for my database. Then
I execute my application in another window on the same machine. It
works, and performance is good.

Then I close Enterprise Manager, and performance degrades significantly,
for no apparent reason. If I restart Enterprise Manager and navigate to
the table display, performance returns to normal.

Starting the application first doesn't change the behavior. I tried
rebooting several times.

Has anyone seen this before?

2. Selling Warehouse Architect $350 bid. less than an hour left!

3. Very odd performance problem

4. Matched rount count ????

5. Odd view performance

6. Isql behaviour

7. odd performance

8. Generic function to test for a returned value in a select statement?

9. Odd Performance Issue

10. Odd performance dropoff inside a cursor

11. Online 7.11 Select Performance - Odd Results

12. Odd Performance Spikes in ASE 11.9.2

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