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.
Same execution plans on both server?
Also, when you say "data is exactly the same", does that mean whole database is identical, to the
point that one is a backup or attach of the other? If not, things like statistics can cause
different execution plans.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Baby Dragon" <nienna.gaia@.gmail.com> wrote in message
news:1177442708.864293.299400@.o40g2000prh.googlegr oups.com...
> 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.
>
|||I will take a look at the execution plans and any statistics that are
captured.
Thanks!
