Stored procedure call is very slow from Entity Framework 4 due to inexplicable reason

Question

Stored procedure (query ) when run in Management Studio takes 4-8 seconds. However, when launched inside WCF service via Entity Framework it may take more than minute. WCF runs in VS 2010 in debugging mode with ASP.Net Development Web Server.

The actual call

requests = transactionEntities.spRequestsForRescreening(cutoffDate).ToArray();

Since all SQL code encapsulated in a stored procedure don't know what I could look at to understand the reason causing this slowness.

Answer 1

While the call is running from the WCF call, you have a full minute to investigate what's going on. Check sys.dm_exec_requests and inspect the WCF request. The relevant columns are:

• wait_time, wait_type and wait_resource to see what is blocking the execution (IO, locking, resource contention, slow client etc)
• blocking_session_id to see what other session is blocking execution
• sql_handle, statement_start_offset and statement_end_offset to understand which statement inside the SP is slow (cross apply sys.dm_exec_sql_text)
• plan_handle to inspect the execution plan (cross apply sys.dm_exec_query_plan)

Inspect these values several times during execution, from an SSMS query window, to get a grasp of what is the call issues from WCF doing and why it takes so long.