Different UPDATE behaviors across servers

Hi!

I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:

Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.

Stored procedure:

BEGIN TRANSACTION

--step 1
TRUNCATE myTable

--step 2
INSERT INTO myTable VALUES ('myValues')

--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns

COMMIT TRANSACTION

The update statement on the problem server is the only step that takes
forever. While it is running, I don't see anything that could be
blocking the statement. I used the following queries to determine if
there was another process blocking it:

select spid AS Blocked, blocked AS Blocking, waittime, cmd, substring
(nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where blocked <0
order by waittime desc

select dbid, name from sysdatabases where dbid in (select dbid from
master.dbo.sysprocesses where blocked <0)

select spid AS BlockingFromAbove, blocked AS TrueBlockingQuery,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostname, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysprocesses where spid in (select blocked from
master.dbo.sysprocesses where blocked <0)
order by waittime desc

When I change the UPDATE statement to a SELECT, it still takes longer
than it does on the test server (1 min 35 sec vs. several
milliseconds).

What could be causing the UPDATE to take forever on one server/
database, and run without a problem on another?

I am at a loss! Any help would be greatly appreciated.Dmitri (nienna.gaia@.gmail.com) writes:

Quote:

Originally Posted by

I have a stored procedure that takes 22 minutes to run in one
environment, that only takes 1 sec or so to run in another
environment. Here is the exact situation:
>
Database 1 on Server 1 vs. Database 2 on Server 2 - the data is
exactly the same, and the tables and index structures are exactly the
same. Implicit transactions are turned off on both databases.

Apparently the query plans are different. This could be because there
are differences in statistics between the databases. Fragmentation
could also matter. I would recommend that you run DBCC DBREINDEX on
the tables in both environments. If you are lucky, the query runs
quickly in both databases. If you are less lucky, the query will now
run slowly in both databases.

If the machines has a different number of processors, this could also
matter. Maybe one machine is a single-CPU machine, whereas the other
is an 8-way box, so there is a parallel plan on server and a non-parallel
plan on the other. Parallel plans are sometimes really amazing -
either amazingly fast or amazingly slow.

Quote:

Originally Posted by

--step 3
UPDATE a
SET rating=AVG(someValues)
FROM myTable a
JOIN otherTable b
ON a.column1=b.column1
GROUP BY someColumns

Not that it matters for the discussion since I don't see the table
definition and the indexes, but this syntax is not legal.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx