Post reply

How to find out the performace difference of a SP ?

  • June 3, 2008 at 12:10 pm

    #380906

    We have a SP with input parameter of date , but we got huge difference excution time between two date , one ( 5/28/08) return 2 seconds, the other (5/29/08) will take more than 1 minute, anyone has ideas what could be the problem ? where should i check ? Thx.

    Here are what we have done:

    1) runing multiple times, and DBCC FREEPROCCACHE to make sure that both running from scratch.

    2) the returing set almost the same size.

    ddfg

  • June 3, 2008 at 12:20 pm

    #823799

    One key word stands out in the problem statement, "almost." Variations in the results, even small ones, can result in larger variations in execution times.

    However, that's usually when there's problems with the query or the indexes on the database. Can you capture the execution plans of the two queries and compare them? The execution plans are your best bet to understand what's happening within the queries inside the query engine. Once you've got the plan you can determine if you've got good indexes on the database or good TSQL code or both.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • June 4, 2008 at 9:27 am

    #824263

    I would guess from what I'm seeing that your problem is a classic parameter sniffing issue.

    To test this, run each sp with the recompile option. You should see much better results.

    here's how you make the call:

    exec GetOrderforCustomers 'CENTC' WITH RECOMPILE

    What's happening is that the query plan is being cached with the first date and is optimized for that one. Then when the 2nd one gets run it's not using an optimized plan.

    However, I did just look back and noticed you're running freeproccache between and that should take care of that.

    So yeah, compare the execution plans and see what you get. Run them in batch mode so you can compare them together.

    To do that, just run them in the same window at the same time like this:

    exec sp1

    exec sp2

    Then you'll get exec plans relative to each other in cost. And I would do it both with and w/o the recompile option. That will show you if there's a significant difference in the plans.

    I hope all that was clear enough.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply