SQL Server Performance

Variable query performance

Discussion in 'T-SQL Performance Tuning for Developers' started by geebee2, Jun 12, 2008.

  1. geebee2 New Member

    I have a stored procedure that does a fairly complicated search.

    The first time it runs, it takes about 3 or 4 seconds.

    Subsequent repeated calls can be very fast - show as zero seconds in query manager, or a similar time to the first run, about 3 seconds.

    It's not predictable, it may run in zero seconds 3 or 4 times, then slow again.

    Any ideas what is going on, or how I could try to diagnose what is happening?
    The procedure uses a temporary table, which seems to stop a query execution plan being viewed.

    It reports "Invalid object name '#mytable'."
    Thanks for any ideas.
  2. Adriaan New Member

    Sometimes sproc performance can suffer from a feature called parameter sniffing, which is supposed to help improve performance but often has an adverse effect.
    Inside the sproc definition, add a local variable for each of the filter parameters, and copy the parameter values into the local variables. Throughout the sproc code, replace the parameter references with variable references.
    This won't help if your sproc compiles a dynamic SQL statement. In that case, it is best to execute the statement through sp_ExecuteSQL, with proper parameters.
  3. geebee2 New Member

    Adriaan

    Many thanks, that does seem to have helped.

Share This Page