Stored procedures are interrupted periodically!

Question

I have several stored procedures that I call from code with ExecuteNonQuery .

Everything was fine, but 2 of my stored procedures started timing intermittently today:

Timed out. The timeout period expired before completion or the server does not respond to requests. The application was discontinued.

If I run sp manually from the management studio, everything is fine anyway.

Nothing has changed in my db recently - my command timeout is standard.

Any clue?

EDIT

table against SP works with huge → 15 Gigs. Reload the field - the same problem, but this time it is not possible to start sp from Management Studio.

Thanks!

Answer 1

Ok - that’s how I fixed it at the end.

A clustered index in a table with 45 million records killed my SQL server - each insertion from the code led to the unpleasant timeouts described in the answer. Increasing the timeout tolerance would not help solve my scalability problems, so I played with the indexes and made the clustered primary key index nonclustered unlocked.

I will be grateful for comments on this in order to better understand how this fixed the problem.

Answer 2

The studio management studio sets an infinite timeout in the requests / commands that it runs. The connection to the database from the code will have a default timeout, which you can change to the command object .

Answer 3

This can often be due to:

• poor query plans due to overuse of plan reuse ( sniffing parameter )
• different SET parameters - in particular ANSI_NULLS and CONCAT_NULL_YIELDS_NULL
• blocking (you may have a higher isolation level)
• indexing needs to update / update / etc

SET parameters may make it impossible to use certain types of indexes (for example, indexes on constant calculated columns, including "advanced" xml / udf queries)

Answer 4

Try recompiling these procedures. I have such problems several times and have not found the cause of the problem, but recompiling always helps.

EDIT:

To recompile proc, go to the management studio, open the procedure for editing and press F5 or execute: EXEC sp_recompile 'proc_name'

Answer 5

Is the team timeout set? Something in your db has recently changed, which makes this process take longer?

If you need to diagnose locking issues, you will need to use something like sp_lock.

Can you share the source of one of your processes?

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

Answer 6

You may need to update statistics on the database. Also indexing on the table recently changed?

Check sp's execution plan to see if you can find a bottleneck. Even if it works fine, it may be configured to work more efficiently.

Also, how much data do you return? We had problems with poorly designed SQL in the past that were not displayed until the aggregate report began to have more data in the result set. Not knowing what you have, it's hard to say if this is possible, but it's worth noting that you should research.

Answer 7

SQL Server will wait indefinitely before returning to the user. Most likely, the client side timeout property was set. For example, you can set the timeout property for an ADO command object .

Answer 8

Get the SQL profiler on it, compare the results between running it in Studio Management and through your application.