Post reply

Execute SQL Task hangs when calling a stored procedure

  • October 16, 2008 at 3:00 am

    #194690

    I have a SSIS Execute SQL Task which calls a stored procedure in a SQL Server 2005 database using an OLE DB connection.

    When I run the proc from SSMS the proc takes about 15 seconds to complete and consistently performs around 1.6 million reads.

    When I run the Execute SQL Task in SSIS the above proc will continue to run until I finally stop execution. The reads are also much higher than in SSMS - I've had it as high as 40 million reads when I finally stopped execution of the task. I call the proc with exactly the same parameters in both scenarios.

    Two questions:

    1) Why won't the proc complete in the Execute SQL Task while it completes successfully everytime I run it from SSMS?

    2) Why are the reads so high when calling the proc from SSIS?

  • October 16, 2008 at 4:11 am

    #885707

    could you run a trace in profiler to make sure the code being called is exactly the same?

    tom

    Life: it twists and turns like a twisty turny thing

  • October 16, 2008 at 4:17 am

    #885710

    Hi Tom

    I've copied the T-SQL which the task generated from the profiler and ran it in SSMS. It is running fine in SSMS.

    Gideon

  • October 16, 2008 at 5:19 am

    #885728

    This doesn't make any sense to me, but it solved the problem.

    Just some background on what the stored proc in the task does: It does validations against data in a view. The records which contain erroneous data are inserted into an error table (in the tempdb), and the records which pass the validations are inserted into another table (also in the tempdb).

    This second table had a non-unique clustered index on one of its columns. I figured that it would make more sense to have a non-clustered index on the table since a lot of inserts are done into this table by the proc and the data isn't sorted before the inserts (maybe my assumption was wrong, but it helped).

    So, I changed the index to a non-clustered index and now things are working. I've done several test runs and the results are consistent. The task completes in roughly the same time as when I run the proc in SSMS. And the performance of the proc was not impacted by the index change.

    I am very relieved to have the task working again, but I am baffled as to why changing an index from clustered to non-clustered should solve the problem. SSIS does not dictate the query execute plan and therefore should not impact execution times for the stored procedure. Does anyone have an explanation?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply