Posted by Dmitri on 04/24/07 14:31

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.

[Back to original message]