Post reply

Update Statistics : _WA_Sys_0000000 ... Type

  • January 17, 2018 at 7:08 am

    #409965

    Dear all,

    I was looking at the statistics of OLA jobs related with Update Statistics command.

    BElow you can find the result of them for table ETL.LoadLog:

    ETL.LoadLog - _WA_Sys_00000003_4AD81681
    ETL.LoadLog - _WA_Sys_00000004_4AD81681
    ETL.LoadLog - _WA_Sys_00000005_4AD81681
    ETL.LoadLog - _WA_Sys_00000006_4AD81681
    ETL.LoadLog - _WA_Sys_00000007_4AD81681
    ETL.LoadLog - _WA_Sys_00000008_4AD81681
    ETL.LoadLog - IDX_FailedLoads
    ETL.LoadLog - IDX_SuccessfulLoads
    ETL.LoadLog - PK_ETL_LoadLog

    The last 3 ones I understand. They are index related. But what about the first 6 ? They are related with what?

    Thank you

  • January 17, 2018 at 7:16 am

    #1976118

    They're the ones auto-generated when you use a column that doesn't already have statistics on it in a query.

    John

  • January 17, 2018 at 7:43 am

    #1976123

    The _00000002_ etc is the column number in the table to which those statistics belong; the last eight characters are the object_id of the table (in hex).

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • January 17, 2018 at 7:49 am

    #1976126

    Ignore these stats. If they are useful and always created, you could drop them and recreate them with better names, but it's really not worth the effort.

  • January 17, 2018 at 9:54 am

    #1976150

    Steve Jones - SSC Editor - Wednesday, January 17, 2018 7:49 AM

    Ignore these stats. If they are useful and always created, you could drop them and recreate them with better names, but it's really not worth the effort.

    I STRONGLY DISAGREE!!!!  These are "column stats" that SQL Server made because the column had no index associate with it.  It could and will absolutely kill performance, especially on large tables, while SQL Server dutifully rebuilds the stats because a given query needs the stats.  We have such stats on a fair number of large tables and dropping such a stat would (and has) taken up to 15 minutes to rebuild.

    --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)

  • January 17, 2018 at 10:28 am

    #1976160

    Did you manually recreate them?

  • January 17, 2018 at 11:44 am

    #1976170

    Steve Jones - SSC Editor - Wednesday, January 17, 2018 10:28 AM

    Did you manually recreate them?

    Nope.  The system did when someone ran a query that needed them.  Usually (at least for our systems), they exist because a new production stored procedure came into being and there were no indexes to support the new proc.

    --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)

  • January 17, 2018 at 12:40 pm

    #1976187

    Jeff Moden - Wednesday, January 17, 2018 11:44 AM

    Steve Jones - SSC Editor - Wednesday, January 17, 2018 10:28 AM

    Did you manually recreate them?

    Nope.  The system did when someone ran a query that needed them.  Usually (at least for our systems), they exist because a new production stored procedure came into being and there were no indexes to support the new proc.

    Thank you very much. This means that update statistics should only ( in normal situation run against indexes) and so, if we have update statistics others then again indexes, it means that we are missing index? If so, how can we discover what were the sp that generate this out of the box statistics?

  • January 17, 2018 at 2:42 pm

    #1976224

    No, it does not mean any of those.
    Update statistics absolutely must run against column statistics. More so than against the stats associated with indexes, since they also get updated with index rebuilds.

    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

  • January 18, 2018 at 5:58 am

    #1976306

    river1 - Wednesday, January 17, 2018 12:40 PM

    Jeff Moden - Wednesday, January 17, 2018 11:44 AM

    Steve Jones - SSC Editor - Wednesday, January 17, 2018 10:28 AM

    Did you manually recreate them?

    Nope.  The system did when someone ran a query that needed them.  Usually (at least for our systems), they exist because a new production stored procedure came into being and there were no indexes to support the new proc.

    Thank you very much. This means that update statistics should only ( in normal situation run against indexes) and so, if we have update statistics others then again indexes, it means that we are missing index? If so, how can we discover what were the sp that generate this out of the box statistics?

    Just to echo what Gail has already stated, the system generated statistics are just as important as the index generated statistics and must also be maintained.  It's not possible to determine which query generated the system generated statistics but it IS possible to determine which system generated statistics are being used over time by using TF 866 and some long running code to examine queries available in cache.  See the following link for a bit more on that.

    https://www.sqlservercentral.com/Forums/1878360/Need-some-help-with-some-XML-Hell-please

    --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)

Viewing 10 posts - 1 through 9 (of 9 total)

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