Different UPDATE behaviors across servers

[Dmitri]

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(some Values)
FROM myTable a
JOIN otherTable b
ON a.column1=b.col umn1
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(hostn ame, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysp rocesses where blocked <0
order by waittime desc

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

select spid AS BlockingFromAbo ve, blocked AS TrueBlockingQue ry,
waittime, cmd, substring (nt_username, 1, 15), dbid, physical_io,
substring(hostn ame, 1, 15), program_name, lastwaittype, waitresource,
memusage
from master.dbo.sysp rocesses where spid in (select blocked from
master.dbo.sysp rocesses 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.

[Erland Sommarskog]

Dmitri (ni*********@gm ail.com) writes:

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.

--step 3
UPDATE a
SET rating=AVG(some Values)
FROM myTable a
JOIN otherTable b
ON a.column1=b.col umn1
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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx