Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2002
    Posts
    8

    Angry Unanswered: Store Procedure Mistery

    Hi!

    I'm having a strange problem with SQL Server 2000 using Store Procedures.

    If I run the store procedure code alone (without exec) everything goes fine and it gives me some results. Taking around 1m.
    Now the strange stuff... the same code in a Store procedure (with exec)... it should take around 1m too... nice joke... it takes around 11m. More 10m!
    How about this???

    Any clues? Ideas?

    Thanks,
    Fernando Silva
    Last edited by Fernando Silva; 04-10-02 at 16:45.

  2. #2
    Join Date
    Apr 2002
    Posts
    16
    Does the name of your stored procedure starts with sp_ ?

  3. #3
    Join Date
    Feb 2002
    Posts
    8

    Post

    No!

    Another thing... it doesn't matter the mumber of times I run the SP it always take around 11m.

  4. #4
    Join Date
    Oct 2001
    Posts
    4
    Try this:

    EXECUTE storedProcedure WITH RECOMPILE

    That will force the procedure's query plan to be recompiled, and should improve performance.

    If that does not work, try dropping the procedure and then re-create it. Sometimes SQL Server does not fully release a procedure's original query plan, even after you recompile it (it's rare, but it can happen)

    Also check that your index statistics are up-to-date (run UPDATE STATISTICS <indexname> on a regular basis)

  5. #5
    Join Date
    Feb 2002
    Posts
    8

    Smile

    Thanks!

    After updating the index statistics and recompiled the SP it return to the normal behavior.

    But shouldn't the SQL Server recompute the indexes statistics by it self? I didn't deactivate that option...

    Another question. Do I need to recompile the SP after updating the index statistics of the tables?

    Thanks again,
    Fernando Silva

  6. #6
    Join Date
    Apr 2002
    Posts
    3
    Hi,

    There is no need to recompile the stored procedure after the statistics is updated.

    Also even if you trun Auto statistics on,you need to updat the statistics manually because sql server uses algorithm to upate the statistics.

    use dbcc showcontig(tablename,index_name) with fast It gives the scan denstiy count.Enjoy

    VenkatChebrolu

Posting Permissions

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