SQL Server timeouts

I'm getting a lot of SQL Server timeouts. I get health events mailed to me, and the ones in question look like this:-

I went through the problem with support, but they have said it's an application problem.

I've been trying to pin the problem down, but it's hard as it's very sporadic. I want to do some statistical analysis on the error logs but I'll need to do some juggling as they are all in emails, so that will have to follow.

The errors always seem to occur with queries relating to one specific table, and it's the only really large table so that is not a huge surprise.

I put a diagnostic page in my app that used a raw SQLDataReader to run a stored procedure, and used my DAL to do the same, and time both. Generally they come out about the same. A few times, the SQLDataReader on the stored proc timed out. The request returns about 15 rows.

Looking at the execution plan and stats in SSMSE 2008 shows nothing odd, but I've not been able to catch the "long execution" scenario from there. I did manage to catch it using tsql + freetds, a Linux based MSSQL client.

In short, this request for 15 rows using a stored proc sometimes takes 3-5 seconds, and sometimes takes over 30 seconds.

I can't see why this would be unless the DB server has a problem - CPU load, disc load, shortage of RAM. I don't know how much isolation there is to prevent one DB user hogging resources, because my DB shares the server with 480 others. Because access to the server profiling tools requires lower level access than we have as users, I can't tell if it's my app/db or another that is causing the problem.

What can I do to resolve this issue? I'm stumped!

 

Hi Bruce.. not 100% reliable, but I can normally create it with a few tries. What do you suggest?