Christine Wolak (catnmus@gmail.com) writes:
> I'm looking for assistance on a problem with SQL Server. We have a
> database where a particular query returns about 3000 rows. This query
> takes about 2 minutes on most machines, which is fine in this
> situation. But on another machine (just one machine), it can run for
> over 30 minutes and not return. I ran it in Query Analyzer and it was
> returning about 70 rows every 45-90 seconds, which is completely
> unacceptable.
> (I'm a developer, not a DBA, so bear with me here.)
> I ran an estimated execution plan for this database on each machine,
> and the "good" one contains lots of parallelism stuff, in particular
> the third box in from the left. The "bad" one contains a "Nested Loop"
> at that position, and NO parallelism.
> We don't know exactly when this started happening, but we DO know that
> some security updates have been installed on this machine (it's at the
> client location), and also SP1 for Office 2003.
> So it looks like parallelism has been turned off by one of these fixes.
> Where do we look for how to turn it back on? This is on SQL Server
> 2000 SP3.

Interesting. In many cases it's the other way round. The parallel plan
is bad, and the non-parallel plan is good.

I find it unlikely that the security fixes for Office would affect SQL
Server. Then again, Office on a machine that runs SQL Server?

Or do are we talking about the same server/database in both cases? Well,
whatever is one the client machines does not affect SQL Server at all.
I first wrote an answer based on the assumption that this happened on
two servers. But reading closer, it seems we are talking the same database.

The likely reason for a difference is connection settings. Run DBCC
USEROPTIONS in QA one the problematic client and on a client where
everything works fine, and compare. You can change connection settings
for QA under Tools->Options. Or simply issue SET commands.



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

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