odd timeout problem

  • Thread starter Thread starter guy
  • Start date Start date
G

guy

c#2008 / sql server 2005

I have a stored proc that takes 2 parameters
if run it through management studio
either with pramaeters x,y or a,b it correctly returns the same set of data
(about 100 rows) in about 1 (one) second

if i run it through my c# test harness (using a dataadapter fill)
with parameters x,y again i get the correct set of data in about 1 second
however if i run it with parameters a,b it times out (timeout is set to 30
seconds)

any idea how i can track this one down?

Guy
 
You can profile SQL Server and watch what is happening. Most likely, you are
using a Reader and keeping it open, which is locking. Or leaving some other
SQL object open that locks the table.

If it is a deadlock, you can view it in SQL Management Studio, by looking at
running processes. A deadlock is created when you have two commands
entangling. You can also find the stuck process and find out why it is stuck
here.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

*************************************************
| Think outside the box!
|
*************************************************
 
This behavior is typically caused by the fact that the FIRST time you run a
SP the query plan is constructed based on the input parameter values
provided. Each subsequent invocation uses this cached query plan--even if it
does not make sense based on the passed parameter values. As I discuss in
the book, the trick here is to construct the SP so the parameters do NOT
impact the query plan.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
 
Back
Top