Sqlparameter And Executenonquery Causing Unrepeatable Time Outs

I recently inherited some code that is having occasional time-out issues. I am mostly familiar with ORM's, so I am having trouble determining if anything is wrong in this code. Whe

Solution 1:

It is likely due to parameter sniffing, causing an inappropriate query plan to be cached.

With so many parameters, you might have to recompile the query each time.

First try rebuilding your indexes and ensuring statistics are up to date: (CAUTION in production environments...)

exec sp_msforeachtable "DBCC DBREINDEX('?')"
go

exec sp_msforeachtable "UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS"
go

Then, examine the query plans for the fast and slow cases of running the SQL in SSMS. If there are no smoking guns, then examine other processes that might be holding locks.

[There are many related questions already answered on StackOverflow.]

Solution 2:

Is this running against SQL Server? You probably are hitting a deadlock when it times out. You'll want to maybe monitor what transactions are running when you encounter a timeout, and/or try running the statement manually in Management Studio and see if you run into a deadlock.

If you have any Agent jobs running that access the same tables, or maybe other processes that might access the same tables, that could be your problem.