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

On the runs that are slow is there anything different about the parameters passed to the proc?

Answer 2

I would set up a trace in SQL Server Profiler to see what SET options settings your application is using for the connection, and what settings are being used in SSMS. By SET options settings, I mean

ARITHABORT
ANSI_NULLS
CONCAT_NULL_YIELDS_NULL
//etc

Take a look at MSDN for a table of options

I have seen the problem before where the set options used between SSMS and an application were different (in that particular case, it was ARITHABORT) and the performance difference was huge (in fact, the application would time out for certain queries, depending on the parameter values).

This would be where I would recommend starting an investigation. By setting up a trace, you'll be able to see which particular calls are taking longer and the parameters that are being used.

Answer 3

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 4

• 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.

Answer 5

Are you absolutely sure it's the database query, and not some other adjacent logic in your code? (i.e. have you put timestamped "trace" statements immediately before and after?)

Answer 6

Russ' suggestion makes the most sense to me so far as it sounds like you've looked into profiler to verify that the plan is optimized and so on.

I'd also watch for data-type coercion. i.e. I've seen similar problems when a varchar(60) parameter is being compared against and index with varchar(80) data. In some cases like that, SQL Server loses its mind and forces scans instead of seeks - though, I believe that in cases like that, you usually see this kind of thing happening in the execution plan.

Sadly, another potential culprit (and I'm a bit leery of throwing it out because it might be a red herring) is hyper-threading. I've seen it do VERY similar things in the past [[1]].

1 http://sqladvice.com/blogs/repeatableread/archive/2007/02/13/Burned-again-by-HyperThreading-on-SQL-Server-2000.aspx

Answer 7

Recompile the Stored Proc then see what happens. This actually helps.

Answer 8

I have also similar performance problem. Adding WITH RECOMPILE to SP helped.

This is not the solution I've looked for but I didn't find better so far...

See: http://stackoverflow.com/questions/663050/slow-performance-of-sqldatareader