Post reply

Why query is performing good on recreating a function??

  • November 6, 2013 at 2:11 am

    #279732

    I have attached a script file with 2 queries and their execution plans for your reference.

    You can see a clear performance difference between both the queries.

    But when I delete and recreate the function, Both the queries perform equally good (as plan 2).

    I have tried same thing on 2 different copies of database and findings are same :-

    Query 1 :

    Table 'job_seeker'. Scan count 1, logical reads 8846

    Table 'work_order'. Scan count 0, logical reads 4

    Query 2 :

    Table 'job_seeker'. Scan count 0, logical reads 4

    Table 'work_order'. Scan count 0, logical reads 4

    After recreating function :

    Table 'job_seeker'. Scan count 0, logical reads 4

    Table 'work_order'. Scan count 0, logical reads 4

    I am unable to find out what is happening on recreating the function that helps first query to perform good?

    Regards,

  • November 6, 2013 at 6:03 am

    #1664428

    I suspect parameter sniffing.

    http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • November 6, 2013 at 12:30 pm

    #1664543

    For the two queries listed, one shows an actual row count of 10,000+ rows. The other shows an actual count of 1 row. You can also see a difference in estimated rows. Since the parameter values are the same, but the estimated statistics are way off, I suspect something changed the statistics between one execution to the next.

    One other difference I can see is that you have a parameter value in the first query, because it has a compile value in the SELECT operator, but the second query is using a local variable, suggesting that might be the cause of the changes in execution plans.

    So, one query is using a value and performing parameter sniffing, the other using a local variable and sampling the statistics. It looks like sampled statistics are better in this case.

    Also, I'd be remiss if I didn't mention that NOLOCK on everything can absolutely lead to incorrect data, including extra or missing rows, especially when you have scans. I strongly recommend not using it.

    "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

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

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