Post reply

sp execution plans

  • June 8, 2009 at 3:50 pm

    #210345

    hi very basic question..

    i hvae one SP running daily on my prod server for more than 100 times but each time it has different value for parameter from business object application. So u can say one SP is running 100 times but each time we are supplying different value(100 values) as parameters.

    How sql server will create execution plans for this? how to optimize so that i can get best execution plans everytime?its not direct on sql but its coming from business objects..

    Thanks

  • June 9, 2009 at 3:35 am

    #1006537

    One cached execution plan that will be reused for all calls to the procedure.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 9, 2009 at 8:13 am

    #1006711

    Now if i try the same thing with select statement and supply 100 different parameters then wht will be the result?

    sql will create 100 different execution plans and that will take more time as compared to Procedure?

    Thanks for the feedback

  • June 9, 2009 at 9:18 am

    #1006777

    dallas13 (6/9/2009)

    Now if i try the same thing with select statement and supply 100 different parameters then wht will be the result?

    sql will create 100 different execution plans and that will take more time as compared to Procedure?

    Depends. If SQL can parameterise the query (very likely if it's a simple query) you'll have a small number of plans, maybe 1 maybe not. How many depend on the types that SQL assigns to the parameters.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 9, 2009 at 10:53 am

    #1006878

    I didnt get that. but here is the question.I have one select statement in my environment

    running since last 6 months with 100 different parameters for 100 times in the morning

    say between 6 to 8 AM. and I was not getting any complains.Everything was fine.

    All of a sudden developers started complaining that we didnt get all the reports today

    with the same select statement.then the problem continued and its 2 weeks n still they

    are complaining. we didnt do anything on that server no patches, no Service packs, no modifications

    we just reboot server 1 week before this started.its all of a sudden started.

    Also i am able to see these messages in my error log for that particular time period.

    http://www.sqlservercentral.com/Forums/Topic724534-5-1.aspx

    and when i asked them wht they are executing they gave me a select statement

    so i embeded this select statement in procedure and tested on dev and looks fine.

    but before making actual change in prod i need to confirm few things thats why I started these questions

    about execution plans.

Viewing 5 posts - 1 through 4 (of 4 total)

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