Tuning a stored procedure
Results 1 to 2 of 2

Thread: Tuning a stored procedure

  1. #1
    Join Date
    Aug 2009
    Posts
    23

    Tuning a stored procedure

    Hello,

    I have a procedure that was taking around 11-12 seconds to run on SQL Server 2012 Enterprise edition.

    There is table named fs.performance that has 21995968 records and it is referenced a couple of times in the procedure.

    I analyzed the procedure in DETA and it suggested a couple of indexes on table fs.performance. I created those indexes and the longest this procedure takes to run (tested with different paramaters) is around 5 seconds now.

    This procedure gets called from application using different parameters. It runs fast for some parameters and slow for some other. Please suggest some ways to tune this procedure so that I get the results within under a second

    Attached are the details.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,898
    Do you get different performance based on parameter values?. If yes, you can create the procedure using WITH RECOMPILE option so it generates a new plan for every execution.

    I see that you are using a temp table #results, you can convert that to a table variable as well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •