Post reply

Statistics Missing For This Table

  • March 21, 2008 at 1:04 pm

    #128383

    In Display Execution Plan for a query I get the message "Warning - Statistics Missing for this table. Choose Create Statistics from the right click menu."

    So I open another QA window and run "update statistics mytablename with fullscan".

    I open my query again, select Display Execution Plan and still get the same messge. Why? Also, I run update stats weekly on this DB, why are stats missing.

    Thanks,

    Randy

  • March 24, 2008 at 6:07 am

    #792968

    If the statistics did not exist, they can't be updated.

    To create the statistics, use the CREATE STATISTICS command. Or you could have SQL Server create it automatically by running a simple SELECT using the column(s) as search arguments.

    You might first want to check if the database itself creates/updates statistics automatically e.g.

    SELECT DATABASEPROPERTY(' database name', 'IsAutoCreateStatistics'), DATABASEPROPERTY('database name ', 'IsAutoUpdateStatistics')

    and set the options accordingly

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • March 24, 2008 at 2:04 pm

    #793210

    Is it a permanent table you're getting that message for, or a temp table?

    If permanent, check that auto_create_statistics is on for that database

    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

  • March 25, 2008 at 7:02 am

    #793452

    I guess I don't understand statistics completely. This is a vendor app and they want auto-update stats off and to run a weekly update statistics job. I guess I turned off auto-create stats when I turned off auto-update. I thought routinely running update stats on every user table was covering everything.

    So if auto-create is turned off and a new query is put in play that uses columns not used too often, or not used previously, statistics will never get created unless done manually, or turn auto-create back on. Is that correct? Why would one ever turn auto-create off?

    I turned auto-create back on and I'll run the update stats job again tonight.

    Thanks for the help.

  • March 25, 2008 at 7:16 am

    #793464

    Randy Doub (3/25/2008)

    So if auto-create is turned off and a new query is put in play that uses columns not used too often, or not used previously, statistics will never get created unless done manually, or turn auto-create back on. Is that correct?

    Yup.

    The recommendation is to leave both auto_create and auto_update on, unless the auto updaet is casing problems (sometimes on very heavily loaded systems), and then to manually update any stats that the auto update doesn't update frequently enough

    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 5 posts - 1 through 4 (of 4 total)

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