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

Question

Post reply

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

Greg Johnson-186398

SSCommitted

Points: 1703
More actions
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?

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

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

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 2

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)

noeld SSC Guru Points: 96590 More actions · Answer 3

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

Ray M SSC-Insane Points: 21093 More actions · Answer 4

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.

Greg Johnson-186398 SSCommitted Points: 1703 More actions · Answer 5

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.

noeld SSC Guru Points: 96590 More actions · Answer 6

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