SQL Server Performance Forum – Threads Archive
60 times slower to call SP from DTS
I have a stored procedure which takes about 5 seconds to run. If I call it from a DTS step it takes about 5 minutes to run. The code is like this. (except that I have three loops through the cursor calling 3 different stored procedures) The stored procedures perform updates / inserts and deletes <code>DECLARE CursorName CURSOR FOR
SELECT Name
FROM Table OPEN CursorName FETCH NEXT FROM CursorName
INTO @name WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Output = @Name + ‘stuff’
EXEC (@Output) FETCH NEXT FROM CursorName
INTO @name
END CLOSE CursorName
DEALLOCATE CursorName </code> From my initital use of performance monitoring it’s showing very few log flushes when run from the DTS package (I get a burst then nothing for 30 seconds or so). I also get 180k lock requests/ sec. I’m not geting any lock timeouts or waits or deadlocks. Anyone got an idea as to what might be happening here?
In general execution from DTS is slow compared to run from QA.
HOw about execution plan for the code? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Everything is using indexes as you’d expect. Is there a way to find the execution plan of code run from within DTS using Execute Package. Why is code slower to run using Execute Package from with a DTS rather than within Query Analyzer?
More information. If I run the Stored Procedure from a web page it acts in the same way as running from a DTS Package. (i.e. very slow).
As the performance of cursor is slower than executing the query directly. Because the ability of the optimal query plan is not used while using cursor. And on top on that executing this from DTS. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
<i> As the performance of cursor is slower than executing the query directly. Because the ability of the optimal query plan is not used while using cursor.</i> I don’t understand. I have a stored procedure (call it Fred) which uses a cursor to execute a number of other stored procedures. What do you mean? I execute Fred from Query Analyzer and it’s quick. I execute Fred from a web page (using ADO.NET connection) or from a DTS package and it’s slow.
I’ve found the cause of the problem but I don’t understand why it happens the way it does. When my SP was executed from within DTS or using ADO.NET it was using different cached execution plans (for the SPs called within the cursor) to when it was executed from Query Analyzer. I’ve added WITH RECOMPILE to all of the SPs and it’s showing the same performance no matter where it runs. Now of my next question is how and why does this happen? In my initial test (before I found a solution) I was connecting in Query Analyer using Windows Authentication, DTS using UserA, and ADO.NET using UserB. I change to connect using UserA in Query Analyzer and that made no difference. So I think I can eliminate that as a cause.
Your question is the answer, being SPs called within cursor doesn’t use optimal query plan wher it lacks performance. And due to the RECOMPILE option it takes every action to refresh the plan. I suggest you to refer to INSIDE SQL Server 2000 book by Kalen Delaney for more information or recent SQLMAG articles refers the similar execution plan discussion. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I am also experiencing slow execution of stored procedures executed from DTS. The problem does not appear to be due to the fact that the procedure call is coming from DTS, but more due to the way the call is integrated into the package. 8 sec completion when the Execute SQL task is executed as an independent step from within the package 480 sec completion for the Execute SQL task when the package as a whole is run The Execute SQL task is started conditionally on the success of some preceeding Transform Data tasks. It appears to start immediately on completion of the prior tasks, but then hangs…and takes…a long time. Is there anything I need to be aware of when creating a package which includes calls to stored procedures? Dave, you said ‘I’ve added WITH RECOMPILE to all of the SPs and it’s showing the same performance no matter where it runs’.
Do you mean that your proc is now executing in 5 secs via both DTS and QA, or 5 minutes? I tried your suggestion and I still get good performance using QA and poor performance via DTS. I would not have thought forcing a recreate of the execution plan could account for the big performances differences we have been seeing though.
Check recent article in SQLMag about DTS. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
quote:Originally posted by Jeremy Selby
Dave, you said ‘I’ve added WITH RECOMPILE to all of the SPs and it’s showing the same performance no matter where it runs’.
Do you mean that your proc is now executing in 5 secs via both DTS and QA, or 5 minutes? I tried your suggestion and I still get good performance using QA and poor performance via DTS. I would not have thought forcing a recreate of the execution plan could account for the big performances differences we have been seeing though.
It’s now executing in 6 or 7 seconds no matter where it is called from. Takes a little longer than the 5 in order to work out the execution plan. Remember, I had the same poor performance calling the SP from a web page (using ADO.NET) so my problem had nothing to do with DTS but was down to cached execution plans being wrong.
Dave, you said ‘I’ve added WITH RECOMPILE to all of the SPs and it’s showing the same performance no matter where it runs’.
Do you mean that your proc is now executing in 5 secs via both DTS and QA, or 5 minutes? I tried your suggestion and I still get good performance using QA and poor performance via DTS. I would not have thought forcing a recreate of the execution plan could account for the big performances differences we have been seeing though.
Hi Satya, Can you please be more specific. A search on DTS in SQL Mag returns thousands of hits. What recent article?
Search for Aug’2003 magazine.
And this linkhttp://vyaskn.tripod.com/sql_server_dts_best_practices.htm about DTS best practices. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Hi Dave, I was experiencing the same problems as you described untill I stumbled on the following link:
http://www.dotnetboards.com/viewtopic.php?t=8391 It solved all my problems.
Hi! Sorry for the cross-post, but thought you might find this useful.
I encountered a similar problem recently. I had the exact same stored proc run fine in QA but it took a while in my asp.net web page. Check my post on this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41378&whichpage=2 Hope this helps, also please let me know if you can think of a better implementation. Thanks,
Ian
]]>