Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Location
    Treviso (Italy)
    Posts
    17

    Unanswered: Procedure lose performance during usage

    Hi,

    i've a store procedure has a strange behavior, As soon as created has a good performance , but after some times (indeterminated) it takes more time to be execute.... (up to 70s!!!)

    The thing that i've not understood was if i take the query inside to the store i execute it separtely I get result immediately...

    Dropping and re-creating procedure,it become newly fast... I've just scheduled a maintenance plan with index optimization and integrity check, but this seems doesn't work ...

    Any idea?

    Thanks .

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Is it being re-compiled? Does it make use of updated statistics?

  3. #3
    Join Date
    Jun 2003
    Posts
    269
    I think execution plan is changing based on parameter passed by sp.
    Post ur sp and related table and index.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    Jun 2004
    Location
    Treviso (Italy)
    Posts
    17
    It not depends on parameters, when System reaches the "degrade point" the store appears slow for different params ...

    It simply use a bad execution plan, avoid using a index created for this type of query ...

    The only two things unlock the situation are :
    - Rebuilding Store Procedure
    - Update Statistic of table in which store works

    Are there situations where SQL Server doesn't update statistics regolary???
    The is a some relation between the load of the systems (when application was under load by several users) and the reaching of the "degrade point".

    System in this state seems ignore the index (specific for this query) presence .... and so use a different execution plan.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Recreate the procedure with "WITH RECOMPILE", and see how it goes for a week. You will incur a small performance penalty on each run, as the query compiles, but that may be more acceptable than waiting 70 seconds at random.

  6. #6
    Join Date
    Jun 2004
    Location
    Treviso (Italy)
    Posts
    17
    Could be a solution ... but anyone have an idea why this behavior occurs?

    However I try with this option and I'll tell you the result for one week.

  7. #7
    Join Date
    Jun 2003
    Posts
    269
    u can force to use specfic index.
    Code:
    select * from urtable with(index(index_name))
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

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