SQL Server takes a long time to return data to ColdFusion using Flex

Question

I am working on a Flex application that connects via Flash Remoting to ColdFusion 8 with a SQL Server 2005 database. In most cases, everything is fine. However, from time to time, SQL Server takes an extremely long time to return data from a stored procedure call to ColdFusion; data is returned from CF to Flex very quickly. When this happens, I can make the same call from Management Studio on SQL Server or on the ColdFusion page on the CF server and immediately get the results. The last occurrence of the problem took about 90 seconds to return the data to CF. During the 90 second window, I was able to run the stored procedure several times in Management Studio.

I tried using different drivers, and that doesn't seem to matter. I also monitored the server and did not notice anything unusual while this was happening. Has anyone seen this behavior before? Any ideas as to what I should look for.

Answer 1

While it is running slowly, can you run sp_who2 against your SQL Server? If this is a blocking problem, you will see rows that have a value in the “BlkBy” column, which means they are waiting for another process to complete before they can continue.

, , , . . , , !

Answer 2

, , sp, ? , , , ? , 9 10, , 10- b/c , , , ?

SQL Trace ( sql-) . , , . , sp