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
>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
> 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
>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
>Dual-486 Compaq Proliant, 64 Meg RAM, NT 4.00 (1381), SQL Server 6.00.151
>Single Pentium Compaq Prolinea, 32 Meg RAM, NT 3.51 (1057), SQL Server
>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.