Geek Answers Handbook

SQL Server 2005 Stored Procedure Performance Issue

I have the following problem: when a stored process is called from my application, from time to time (for example, 1 time out of 1000 calls), it takes 10-30 seconds to complete. Typically, sproc runs for a second. This is pretty simple proc with one choice that links a couple of tables. All table names are set using the (NOLOCK) hint, so it probably does not block. Indexes are also in place, otherwise it will be slow all the time.

The problem is that I can not replicate this problem in SSMS (since it always works for a second), no matter how many times it launches sproc, but I see a problem when I tell the profiler to the user who runs my application, Query Plan in SSMS seems correct, but the problem persists.

Where do I go next? How to debug this problem?

+4
source share
8 answers
  • Startups in the database? Check the messages in the SQL error logs.
  • Page split due to inserted records? Mark table fragmentation with DBCC SHOWCONTIG
  • Antivirus scan? Not necessary.
  • Outdated statistics? Do not rely on statistics that automatically update tables that change dramatically.
  • Do not rule out problems on the client side or the network between them.
  • Run the profiler with a filter by duration, only capture events lasting> 10 seconds, search for patterns in parameters, clients, time of day.
+4
source

Some options:

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

  • The engine decides that the statistics are out of date. Are the tables changed by changing the number of rows by 10% (rule of thumb). To check:

     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: rebuilding an index: not locking, just resource intensive.

I usually suggest a sniffing option, but you say the options are the same for every call. I also expected this to happen more often.

+5
source

I would set up tracing in SQL Server Profiler to find out which SET parameter settings your application uses to connect, and which settings are used in SSMS. In SET settings, I mean

 ARITHABORT ANSI_NULLS CONCAT_NULL_YIELDS_NULL //etc

See MSDN for parameter table

I saw a problem before where the dial options used between SSMS and the application were different (in this particular case it was ARITHABORT ) and the performance difference was huge (in fact, the application will time out for certain requests, depending on parameter values).

I would recommend starting an investigation. By setting up a trace, you can see which specific calls take longer and the parameters that are used.

+2
source

Is there something else in slow runs in the parameters passed to proc?

+1
source

Are you absolutely sure that this is a database query, and not some other related logic in your code? (i.e. did you put temporary “trace” statements immediately before and after?)

+1
source

The Rus proposal is of the greatest importance to me, because it seems that you looked at the profiler to make sure that the plan is optimized and so on.

I would also observe data type enforcement. those. I saw similar problems when comparing varchar (60) parameter and index with varchar (80) data. In some cases, SQL Server faints and checks for strength instead of searching - although, I believe that in such cases you usually see that this happens in terms of execution.

Unfortunately, another potential culprit (and I'm a little nasty to throwing it out because it could be red herring) is hyper-threading. I have seen how this has done 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

+1
source

Recompile the saved Proc, and then see what happens. It really helps.

0
source

I also have a similar performance issue. Added support for WITH RECOMPILE in SP.

This is not the solution I was looking for, but have not yet found the best ...

See: Slow performance of SqlDataReader

0
source

More articles:


All Articles