SQL Server execution plans

[Christine Wolak]

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.
Thanks for any help you might have for me!
Christine Wolak -- SPL WorldGroup --
ch***********************@splwgREMOVETHISTOO.Com

[louis]

I don't think lack of parallelism is a concern. I think the nested
loop is more of concern. Ideally, the execution plan should show only
"index seeks" meaning MSSQL will perform a single pass on an index.
The nested loop means it will procedurally loop over something (look
for number of executes to see how many times it loops). I would run
DBCC SHOWCONTIG to see if your indexes are fragmented, assuming you
have indexes on that machine. I would also try running SP_UPDATESTATS
before executing the query.

[Gary]

Compare the indexes on the tables in each database, make sure they are
the same.

[Erland Sommarskog]

Christine Wolak (ca*****@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, es****@sommarskog.se

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

[Gary]

How could connection settings on different clients create different
execution plans on the server?

[Erland Sommarskog]

Gary (ga**********@wcc.ml.com) writes:

How could connection settings on different clients create different
execution plans on the server?

CREATE TABLE #null(a int NULL)
go
INSERT #null(a) VALUES (NULL)
go
SET ANSI NULLS OFF
go
SELECT * FROM #null WHERE a = NULL
go
SET ANSI NULLS ON
go
SELECT * FROM #null WHERE a = NULL

And, yes, those are different plans. When ANSI_NULLS are ON (as it should
be), the optimizer can transform the query to a no-op, but for the other
case it may have to scan the table.

More generally, a execution plan is associated with a set of SET options,
and if a process does not match that setting, another plan will be used.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Christine Wolak]

Thanks everyone for the assistance.
Regarding the questions about the server, this was a situation where I
take a single database backup file, restore it on machine A and machine
B, and the execution plans are different for the same query. Assume A
is bad and B is good. If I take a different backup file (same basic DB
just from a different date), and put it on A and B, I get the same
results. Meaning it's something about the MACHINE, and not something
about the database.

So, it turns out that I overlooked the most obvious answer to the
question, and that is that the bad machine actually had only one CPU!
So parallelism is not used. I never even checked for this because I
thought I already knew that it had two CPUs. My bad. They must have
been just barely sneaking in under the timeout value earlier, but the
extra month of data pushed it over the brink.

I did find the OPTION (maxdop 2) hint, and also OPTION (hash join),
which would help if I could actually use them!
Thanks again everyone.
Christine