Post reply

Statistics on every column?

  • April 25, 2016 at 8:38 am

    #310187

    I've just discovered that one of my machines (previously hidden from me) has some very odd bi-weekly statistics rebuild code on it. As it's going through the tables, it builds statistics on all new columns a table may have and has previously built column statistics on all columns of all tables of all user databases.

    Of course, the code also does statistics rebuilds by date rather than by rows inserted/updated so it basically rebuilds all statistics on all tables and indexes every week. I can certainly fix that. Heh... have to... the databases combine for a total of only 1.2 TB but the statistics rebuilds take almost 18 hours. :sick:

    IMHO, having stats on every column of every table is counter productive at best but, before I take on the task of identifying and dropping stats from columns that aren't used for criteria in queries, I thought I'd ask "Is there any merit to having stats on every column of every table of every database"?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 25, 2016 at 8:48 am

    #1874259

    No, but other than the time that the manual stats updates take, not too much downside either. They're not large, and those that aren't useful won't get autoupdates triggered.

    You can get away with dropping all (on a test server) then running a workload against it and seeing which ones the QO recreates. Probably faster than looking at every single query for where, join and group by columns.

    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

  • April 25, 2016 at 8:49 am

    #1874261

    If the column is never in a join or a filter then it is purely a cost on the system.

    Stats only autoupdate if there is a query on it so even if stats are years out of date, they will never be triggered for auto stats update unless there is a filter on a query on that column.

    It may be a cool idea to enable asynchronous statistics update, unless there are large inserts onto the tables which can heavily impact a query, which tends to be mostly on a DW size and style DB, where a first execution with outdated stats can be severely punishing.

    But creating stats on all columns, OK I can get behind that, but to force a rebuild on stats which havent been used by queries in years just as part of a job?! Can't get behind that.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • April 25, 2016 at 9:12 am

    #1874269

    I had a similar situation a few years back. I did exactly what Gail suggested, dropped everything.

    I then took the top 100 queries/procs, generated scripts to run them, and let the stats get re-created.

    The fortunate (unfortunate?) thing was that performance was so bad that once I did this in the prod environment, nobody noticed. After things got squared away, I looked pretty good.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • April 25, 2016 at 12:39 pm

    #1874341

    Thanks for the answers, folks. Everything is pretty much as I thought but had to make sure. Lot's o' experienced folks out there and I've not run into such a problem before. Thanks for your help and quick responses.

    Even though they created the stats using _WA_ (to make it look like they were auto-magically built), I've found a pattern in the names created by the rebuild job that looks reliable. Combined with the two "creation type" bit columns in sys.stats, I believe I can ID all the column stats created by the job.

    I'm might be missing it but I think it's a real shame that MS doesn't have something like a "last_used" column for column statistics anywhere. I can derive that info from dm_db_index_usage_stats for somethings, though.

    Again, thanks for the help. I really appreciate it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 25, 2016 at 2:53 pm

    #1874377

    Jeff Moden (4/25/2016)

    Thanks for the answers, folks. Everything is pretty much as I thought but had to make sure. Lot's o' experienced folks out there and I've not run into such a problem before. Thanks for your help and quick responses.

    Even though they created the stats using _WA_ (to make it look like they were auto-magically built), I've found a pattern in the names created by the rebuild job that looks reliable. Combined with the two "creation type" bit columns in sys.stats, I believe I can ID all the column stats created by the job.

    I'm might be missing it but I think it's a real shame that MS doesn't have something like a "last_used" column for column statistics anywhere. I can derive that info from dm_db_index_usage_stats for somethings, though.

    Again, thanks for the help. I really appreciate it.

    Well, you can probably get rowcount changed and calculate the threshold.

    IF change in rowcount >autoupdate stats threshold THEN the stats should have autoupdated but was not, implying it was unused since last stats update.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • April 25, 2016 at 3:09 pm

    #1874382

    MadAdmin (4/25/2016)

    Jeff Moden (4/25/2016)

    Thanks for the answers, folks. Everything is pretty much as I thought but had to make sure. Lot's o' experienced folks out there and I've not run into such a problem before. Thanks for your help and quick responses.

    Even though they created the stats using _WA_ (to make it look like they were auto-magically built), I've found a pattern in the names created by the rebuild job that looks reliable. Combined with the two "creation type" bit columns in sys.stats, I believe I can ID all the column stats created by the job.

    I'm might be missing it but I think it's a real shame that MS doesn't have something like a "last_used" column for column statistics anywhere. I can derive that info from dm_db_index_usage_stats for somethings, though.

    Again, thanks for the help. I really appreciate it.

    Well, you can probably get rowcount changed and calculate the threshold.

    IF change in rowcount >autoupdate stats threshold THEN the stats should have autoupdated but was not, implying it was unused since last stats update.

    Thanks but, provided that I'm not totally off my rocker, what I'm trying to determine is whether or not a given stat is actually being used or not. The rowcount doesn't indicate whether or not the stats are being used when execution plans are being created and autoupdate doesn't happen often enough on large tables to make a difference.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 25, 2016 at 3:17 pm

    #1874385

    Jeff Moden (4/25/2016)

    MadAdmin (4/25/2016)

    Jeff Moden (4/25/2016)

    Thanks for the answers, folks. Everything is pretty much as I thought but had to make sure. Lot's o' experienced folks out there and I've not run into such a problem before. Thanks for your help and quick responses.

    Even though they created the stats using _WA_ (to make it look like they were auto-magically built), I've found a pattern in the names created by the rebuild job that looks reliable. Combined with the two "creation type" bit columns in sys.stats, I believe I can ID all the column stats created by the job.

    I'm might be missing it but I think it's a real shame that MS doesn't have something like a "last_used" column for column statistics anywhere. I can derive that info from dm_db_index_usage_stats for somethings, though.

    Again, thanks for the help. I really appreciate it.

    Well, you can probably get rowcount changed and calculate the threshold.

    IF change in rowcount >autoupdate stats threshold THEN the stats should have autoupdated but was not, implying it was unused since last stats update.

    Thanks but, provided that I'm not totally off my rocker, what I'm trying to determine is whether or not a given stat is actually being used or not. The rowcount doesn't indicate whether or not the stats are being used when execution plans are being created and autoupdate doesn't happen often enough on large tables to make a difference.

    yeah, it is a column called modification+counter in [sys].[dm_db_stats_properties] and no you are not off your rocker yet, I typed rowcount in haste.

    Also an explicit stats update will make it so that the stats probably never autoupdate.

    so that throws my initial logic out the window.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

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