SQL Server 2005 stored procedure performance problem

Question

I have the following issue: when a stored proc is called from my application, every now and then (like 1 time out of 1000 calls), it takes 10-30 seconds to finish. Typically, the sproc runs in under a second. It's a fairly simply proc with a single select that ties together a couple of tables. All the table names are set with a (NOLOCK) hint, so it probably isn't locking. The indexes are all in place too, otherwise it would be slow all the time.

The problem is that I can't replicate this issue in SSMS (as it always runs subsecond) no matter how many times it runs the sproc, yet I see the problem when I point the profiler to the user who's running my app. The query plan in SSMS seems correct, yet the problem persists.

Where do I go from here? How do I debug this issue?

Answer 1

Some options:

• What does profiler or SET STATISTICS xx ON say? Is there simply resource starvation, say CPU

• The engine decides statistics are out of date. Are the tables changing by 10% row count change (rule of thumb). To test:

  SELECT
      name AS stats_name, 
      STATS_DATE(object_id, stats_id) AS statistics_update_date
  FROM
      sys.stats 
  WHERE
      object_id IN (OBJECT_ID('relevanttable1'), OBJECT_ID('relevanttable2'))
  

• What else is happening on the server? example: Index rebuild: not blocking, just resource intensive.

Usually I'd suggest parameter sniffing but you say the parameters are the same for every call. I'd also expect it to happen more often.

Answer 2

• Autogrows on the database? Check for messages in the SQL error logs.
• Page splits due to inserted records? Check table fragmentation with DBCC SHOWCONTIG
• Antivirus scans? Don't.
• Out of date statistics? Don't rely on auto-update statistics on tables that change a lot.
• Don't rule out a problem on the client end, or the networking between them.
• Run profiler with a filter on duration, only capturing events with duration > 10 seconds, look for patterns in parameters, clients, time of day.