Very Simple Stored Procedure Will Time Out.

Question

I have a problem with this one stored procedure that works 99% of the time throughout our application, but will time out when called from a particular part of the application.

The table only has 3 columns and contains about 300 records. The stored proc will only bring back one record and looks like this

"Select * from Table Where Column = @parameter"

When the sp is executed in management studio it takes :00 seconds.

The stored procedure is used a lot in our application, but only seems to time out in one particular part of our program. I can't think of any reason why such a simple sp would time out. Any ideas?

This is a vb.net desktop application and using sql server 2005.

Answer 1

You've got some code that's already holding a lock on the table so it can't be read.

Answer 2

you need to get performance metrics. Use the sql profiler to confirm that the SP is slow at that time or something else. If it is the sql that's slow at that time - consider things like locks that may be forcing your query to wait. Lets us know and we might be able to give more specific information at that point.

If it not the SP but say the VB code, a decent profile like RedGate's Ants or JetBrains' DotTrace may help.

Answer 3

try

SELECT * FROM Table WITH (NOLOCK) WHERE Column = @parameter