Intermittent SQL Server Timeouts and wildly varied execution times for stored procs

Question

I am having a problem with my SQL Server 2005 where stored procs, specifically ones that run Bulk Inserts, are sometimes taking a really long time to execute. This stored proc, let's call it DatabaseUpdate, shold take about 2 secs to complete. However, between 20-40% of the time it takes much more than that and it is very common that it will timeout after 30 secs.

What's so strange about this is that I have a very controlled environment. The logs I am currently looking at, show a period in which the exact same events occur at regular intervals. There are handful of clients and they all make requests based on timers and these machines are all running atomic clock synchronization apps. So I can say with near certainty that the same sequence of events occurs every five minutes, yet my stored proc that imports a 100Kb file into the database varies in its execution time from 1.5-30+ secs.

What I had not noticed before, because I was only seeing the timeout errors, is that the longer execution times are occurring every other update, so the times look like this:

10:30 2062 ms 10:35 24250 ms 10:40 1921 ms 10:45 25703 ms 10:50 1734 ms 10:55 25406 ms 11:00 1625 ms

Any ideas?

Answer 1

Further to Mitch's recommendations I would also suggest that troubleshoot your issue by running a SQL Trace to verify that the same "Actual" not "Estimated" execution plan is being used for each call of the stored procedure.

Also, validate that the same or a similar number of records is being processed with each execution/BULK INSERT because naturally the more records that are inserted by the procedure the longer it will take to execute.

Have you considered that your database may not be adequately sized to support your inserts? For example, does your database data file need to grow in order to accomodate every other procedure execution?

Provided these elements are the same between each execution then the only other element that will be changing is the database data itself and so would require investigations into statistics and index usage.

Answer 2

See if you have any log growth or database growth events. When databases are incorrectly sized they will auto-grow. During an auto-growth event (likely to happen during a bulk insert) the database will freeze until the file is grown and 0 initialized. This operation lasts quite long (tens of seconds), and is getting longer as database grow because the default auto-growth is percent from size, so the auto-grow is growing larger.

To check for auto-growth events, look into the ERRORLOG and into the SQL Server:Database/Log Growth performance counter value.

If you have database growth events you must set the database size to the correct size. The correct value depends on your actual data load. You should also turn on the NTFS initant initialization, see Instant Initialization - What, Why and How?. You can't use this setting for log files.

Answer 3

Could it be [b]locking problem? What does SQL Profiler show (turn errors and deadlocks on too)? For which resources are (hanging) inserts waiting?

Answer 4

Look into checkpoints.

I have seen this same thing when loading a lot of data.

The issue is that the database engine is writing the dirty pages from memory to disk.

Use Perfmon to monitor disk performance and if this is the case you will see a spike in disk usage at the same time your performance decreases.