i have strange problem @ 1 of our clients. when client runs stored procedure using ado.net sqldatareader (called desktop application located on same server sqlserver) sqldatareader takes lot of time execute , eventualy times out. if execute same stored procedure (same parameters) ssms, runs instantly (the tables involved not large).
the tables involved indexed, index rebuild night before.
if run "alter procedure procname..", sqldatareader starts running instantly also, time (aroun 1-2 weeks), start hanging again.
the issue happening on client server (which sql 2005), , if take backup of db, on our server working without issues.
can please suggest measures should take prevent this?
is recompiling procedure @ interval of time "normal" way fix this?
thank you!
each time alter/create procedure works fast, right?
you can use recompile option when declaring procedure , make test:
create/alter (name) (parameters) recompile as
you can use exec statement:
execute procedure_name recompile
hope helps
Comments
Post a Comment