Sql stored procedure call hangs from .net
Hello,
I'm really stuck on this one, so if anyone can help it's much appreciated. I've written a stored procedure that takes about 3-4 minutes to run in testing. However, when I call the procedure from a C# application the procedure call just hangs..eventually timing out causing an exception. I set the timeout to 60 mins just to check but it just hangs all the way.
Does anyone have any ideas why this would be happening? As I say, the stored procedure works fine if you run it from Query Analyser in Sql Server.
Please help - any thoughts very welcome
[640 byte] By [
AlHibbs] at [2008-2-14]
I may be wrong, but it is very possible that when the sp is executed from your C# app, it's picking up a different execution plan than when you run it from QueryAnalyzer. I've run into a very similar problem before, and this has been the cause pretty much every time.
if you're running 2005 -- below is a view definition that can help you troubleshoot the execution plan scenario.
select * from CacheObjects where Text like '%YourSP%'
column query_plan has the execution plan in xml. It's harder to read than the graphical plan -- but it's ok. If multiple execution plans is in fact the problem, you might have to optimize your sp or force a good execution plan using hints.
good luck!
--
use master
go
create view CacheObjects
as
select
substring(text, statement_start_offset/2,(case when statement_end_offset = -1 then len(convert(nvarchar(max), text)) * 2 else statement_end_offset end - statement_start_offset)/2) as Statement,
Text, execution_count, query_plan, creation_time, last_execution_time, plan_generation_num, total_elapsed_time, last_elapsed_time, min_elapsed_time, max_elapsed_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes, total_logical_reads, last_logical_reads, min_logical_reads, max_logical_reads, s.dbid, s.objectid, s.number
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) s
cross apply sys.dm_exec_query_plan(plan_handle) In addition to the above suggestion on checking plan change (which may contribute to excessively long execution time), verify that simple stored procs work from your C# application (no permission issues, you consume output of the stored procedure correctly, i.e. not blocked on it). Also, try simplifying your SP in question and see if it completes at all.In general the more detailed info you provide of what you have tried and how it failed or worked, the more specific answer you will get.
