Posted by Erland Sommarskog on 07/23/05 10:57

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
> Actually we nailed it down.

At least now you know the query. That makes life a little easier!

> Server A is SQL 2000 Enterprise (SP3) on Windows Server 2003 (no SP at
> this time).
> Clustered, dual 3 gig Xeons with HT ON, 8 gig RAM, 7 Gig for SQL
>
> Server B is SQL 2000 Standard (SP3) on Windows Server 2000 Advanced Server
> (SP4)
> Stand alone, DAS storage. Quad Xeon 550Mhz, 4 gig RAM (2 gig for SQL)
>
> Clearly the the query plan on A is fubar.
>
> I've updated stats and rebuilt most of the indices (rebuilding the rest
> tonight).
>
> But still no joy.
>
> Other suggestions?
>
> I seem to recall a bug in SQL Server views with HT or something. Anything
> ring a bell?

Well, there is a standard recommendation to set "max degree of parallelism"
on an HT machine to at most the number of physical processors, thus for
server A to 2.

That alone may not give you a better query plan, but at least lower
load the CPUs. But since it was read-ahead reads that are killing you,
this would be a minor improvement.

You need to analyse the query plans on A and B. If the plan on B does
not have parallelism, but the plan A has, then try to add
"OPTION (MAXDOP 1)" to the query.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]