Post reply

SQL Profiler shows "NO STATS" TextData. WTF does that mean?

  • June 27, 2005 at 8:16 am

    #67204

    The Event Class is "Missing Column Statistics", and it seems that the very next Profiler row is related, because the NTUserName and SPID are invariably the same.

    Here's the TextData for an example:

    EventClass ~ TextData

    Missing Column Statistics ~ NO STATS[P].[InstTrancheID], [PU].[InstTrancheID], [PU].[BizStrategyID], [P].[BizStrategyID])

    SQL:BatchCompleted ~ select * from vwPositionByBizStrategy where InstRegionName in ('ASIA','EUR','LAT','NAMER','OTHER')

    this query does NOT always yield a "Missing Column Statistics" row in Profile, just here and there (20% of the time?)

    Why would SQL complain about missing column stats, and not create them itself?

  • June 27, 2005 at 9:47 am

    #568656

    2 Questions.

    1. Do you have the DB with auto create statistics on!

    2. Does the Base Table  of that view change very often ? (lots od DML on it)

     

     


    * Noel

  • June 27, 2005 at 9:51 am

    #568661

    1.) auto create statistics for the DB is ON

    2.) DDL is never run on the underlying tables, DML very regularly (dozens of ups, ins, and dels per minute)

  • June 27, 2005 at 9:57 am

    #568666

    Ok what is happening is that the auto create statistics process can't catch up with the amount of changes.

    You must schedule :

    UPDATE STATISTICS ( WITH FULLSCAN) at off hours to compensate for that!

    how often do you do it?

     


    * Noel

  • June 27, 2005 at 10:03 am

    #568667

    From Books Online.

    Rtfm (Read the Manual)

    "Column statistics for the query optimizer are not available."

    "As the data in a column changes, index and column statistics can become out-of-date and cause the query optimizer to make less-than-optimal decisions on how to process a query."

    "The frequency at which the statistical information is updated is determined by the volume of data in the column or index and the amount of changing data"

    Might I suggest some light reading.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_8e2b.asp

    -- Read this.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_72r9.asp

    Make sure AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS are on.

  • June 27, 2005 at 10:06 am

    #568669

    NOEL:

    whenever it occurs to me (once a month?), I run:

    sp_updatestats

    but it seems (from BOL) that this won't perform a full scan. I'll schedule the FULLSCAN to run every night and see if that helps with the NO STATS issue.

    btw, why don't Maintenance Plans have a tab for statistics maintenance?

    RAY:

    thanks for the links, both those db options are set to ON.

  • June 27, 2005 at 10:12 am

    #568673

    sp_updatestats is good when you need something for ALL tables,

    the UPDATE STATISTICS gives you finer control (Just the one you need )

    and BTW

    Maintenance Plans do have on Optimization tab in which you can specify UPDATE STATS with 100% sample (Fullscan ) but again is for ALL tables. I could gess is just a call to sp_updatestats 

     

     

     


    * Noel

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

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