Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    25

    Unanswered: Stored Proc Optimizing

    Suddenly one of the stored proc becomes very slow. When checked the query plan came to know that 2 tables are not using indexes. Used sp_compiled and also dropped and recreated the proc. But still the optimizer is not using the desired indexes. Does not want to force the index. Wanted to know the root cause of optimizer not using the index. please help.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Have you updated statistics for the 2 tables?

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    pdreyer has an excellent point about the statistics. You should probably also check to see if there are border cases in your data. Are there any combination of parameters the stored procedure accepts that cause a table scan to be preferable? Typically, RDBMS systems only use indexes at 4 - 7 % selectivity.

  4. #4
    Join Date
    Jan 2010
    Posts
    25
    update statistics are actually run every weekend using a batch. But still there are no sign of any change with the query plan.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Can you add timing statements to find where in the proc it is suddenly taking the time and then give us the SQL at this point. Perhaps another batch is now locking a table when it wasn't before - have any batches been added/changed recently? Have you tried just running the proc and seeing if it runs slow at all times? Do the tables concerned always have a moderate amount of data in them or could one particular table be empty when the statistics are built and so affect stored proc plans?

    Mike

Tags for this Thread

Posting Permissions

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