Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696

    Unanswered: Index/Column Statistics - SQL 2005

    I've been banging my head over a week with this problem, it seems stats get out of date rather quickly on SQL 2005, resulting in procedures taking much longer to run. When I run an sp_updatestats, problem is resolved. I don't understand the purpose of having auto_stats turned on if it is not doing it job of keeping index/col stats up to date. DB is in compatibility mode 80, can this cause an issue ? I did not have these issues on SQL 2000.

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It doesn't kick in until 20%+ of stats are changed from the last update. Obviously this is substantially more than can start interfering with query plans.

    As such, many people update stats as part of regular maintenance and use auto update for "emergencies". A bit like proactively growing database files but keeping auto_grow on.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Do you use "truncate table" at all? Using truncate sometimes did not update the table as having changed, then when you re-fill the table with data, the statistics make it look like there is twice as much data in the table. At least, it used to. Next time this happens, you might want to just run sp_compile on a bunch of the table, to see if you just have a bad query plan getting created. I think the sp_updatestats would invalidate much of what is in the procedure cache.

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by MCrowley
    Do you use "truncate table" at all? Using truncate sometimes did not update the table as having changed, then when you re-fill the table with data, the statistics make it look like there is twice as much data in the table. At least, it used to. Next time this happens, you might want to just run sp_compile on a bunch of the table, to see if you just have a bad query plan getting created. I think the sp_updatestats would invalidate much of what is in the procedure cache.
    No, nothing of the sort, inserts one day's data (20-25K rows), table has 10MM rows. I run the process that populates this, and then run sp_updatestats, and all indexes on the offending table "have been updated", does this necessarily mean they are stale ?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It should not mean the statistics are stale. You are only changing the table by about 0.2% per day. I am still leaning toward the bad query plan idea. Like I say, next time this happens, get profiler to see which query/stored proc is running extra long, then see if recompiling it improves performance.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by MCrowley
    It should not mean the statistics are stale. You are only changing the table by about 0.2% per day. I am still leaning toward the bad query plan idea. Like I say, next time this happens, get profiler to see which query/stored proc is running extra long, then see if recompiling it improves performance.
    If thats' the case then "with recompile" should address this issue, correct ?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If much of the querying is on this 0.2% of the table then the statistics may be out of date with respect to the target data, especially if a component of the index includes temporal data. Is the target of the proc predominantly this recently added data?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by pootle flump
    If much of the querying is on this 0.2% of the table then the statistics may be out of date with respect to the target data, especially if a component of the index includes temporal data. Is the target of the proc predominantly this recently added data?

    Yes, most of the day will be spent on this new 0.2% data that represents positions / risk analysis for the respective day.

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Parameter sniffing seemed to be the root cause of our performance problems, not as prevalent in 2000 as in 2005.

    Fixed simply by defining a variable and assigning it the value of the input variable.

Posting Permissions

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