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.
