Poor query performance

  • I have a procedure making a call to a table that normally takes 5-10 seconds to return results. However, lately this has been taking 1-2 min. I have found a fix which is to rebuild an index (0% fragmentation) in the main table that the query uses, then it runs fine.

    Reorganizing and/or updating statistics on that index does not help. By comparing the before and after exec plans, I’ve noticed that the Actual/estimated number of rows has changed and a key lookup & index seek changes from parallelism = False, to Parallelism = True - after the index rebuild. Other than that nothing major stands out at me when comparing the plans.

    Also noticed that after restarting sql the query goes back to the old non-parallelized plan which is slower. This is occuring on both of my SQL 2005 test and production servers. Any ideas what could be causing this?

  • Are you sure that updating stats with fullscan doesn't help? Changes in estimated rows before/after is usually indicative of statistics-related problem (or bad parameter sniffing).

    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
  • I updated stats with full scan on the entire table. Should I try just updating them on that particular index?

  • No. Updating stats on the table updates all the stats on that table, index and column.

    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
  • Any other ideas? We basically have a table for each month with the same structure and this is even happening with the historical tables that have no inserts and very little read activity. There is no fragmentation.

  • Stale stats or bad plan in cache to be honest.

    The optimiser has no knowledge of fragmentation, so that won't change plans, usually when rebuild fixes query problems it's actually the stats update that fixed things

    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
  • I use Ola Hallengren's maintenance solution, so stats are being updated nightly based on threshold.

    The query uses a better plan after rebuilding the index, but could it possibly be bad stats on another table that the query uses, that's not related to the index?

  • Or stale stats on the same table, on a different column. Or on another table. Or a bad plan (from bad parameter sniffing) getting into cache maybe.

    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
  • I have a feeling that it's bad parameter sniffing - chasing that down now. If nothing comes up there, then I will update stats on all tables EXCEPT the one that I've already updated with no success.

  • Next time the query misbehaves, can you capture the actual execution plan (not estimated please) before and after the index rebuild? Without that, just guessing.

    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
  • I have both if you would like to take a look?

  • Post them. Won't have a chance tonight. Maybe tomorrow, maybe saturday.

    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

Viewing 12 posts - 1 through 11 (of 11 total)

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