Sp performence.

  • Hi,

    I am facing one issue in SP at prod. which is running at QA and staging within 1-2 mins. But at prod some time it is running in 1-2 min and some time it is not running even in 45 mins.

    What can be the reason for that.Even all indexs are fine and no any Backup/maintainance activity running at prod.

    Thanks

    Nitin Varshney

  • If stats are accurate, then most probably you encounter problem with http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]

  • Not enough information.

    Could be locks or waits for other resources

    Could be bad exec plans

    Could be several other things.

    When it runs slow, what's the wait type?

    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
  • is the sp execution plan will be changed for the grouping of small and large data set in the sp ?

  • @Gila: How can we know the wait type and locking objects ? there is no any blockage in sp_who2 active command.

  • nitin.varshney (12/9/2012)


    is the sp execution plan will be changed for the grouping of small and large data set in the sp ?

    Not unless something invalidates or removes the one plan and regenerates. A stored procedure has a single execution plan cached for it.

    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
  • nitin.varshney (12/9/2012)


    @Gila: How can we know the wait type and locking objects ? there is no any blockage in sp_who2 active command.

    Query sys.dm_exec_requests. There are several columns relating to the waits.

    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
  • e4d4 (12/9/2012)


    If stats are accurate, then most probably you encounter problem with http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]

    +1. Excellent link.

    Nitin, I've faced such situations in real time scenarios. I tried a lot, moved from here to there, tested every possibility. Finally found this parameter sniffing stuff. For me, changing the procedure with recompile option worked well.

Viewing 8 posts - 1 through 7 (of 7 total)

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