Index Statistics not there

Question

Index Statistics not there

Philip Kelley

SSCertifiable

Points: 6351
More actions
May 15, 2006 at 2:20 pm

#112233

What might cause statistics to not be present for an index? We've got a big table (4G) with a nonclustered primary key built on 13 columns [by someone NOT MYSELF who clearly did not understand data warehouses], and there are no statistics available for either the index, or the 13 auto-generated statistics. (NULL in sysIndexes.statblob). UPDATE STATISTICS returns "done" as soon as you hit go.
I've a handful of fixes to try, I'd just like to know why it's doing this in the first place.
Philip

Viewing 5 posts - 1 through 4 (of 4 total)

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

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 1

Addenda: I created some indexes on individual columns, and statistics were there. It did partial sampling, and didn't like the resulting selectivity on one column, so I ran sp_updatestats... and the statistcs got blown away. (We do this weekly, as part of the loading process.)

What the heck? Still researching, but I'd love a shorcut...

Philip

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 2

More clues: Restored a copy of the database from last week, and the statistics are there. Restore it from this week, and they're not.

Messing around with both restored databases, I can create indexes on both with statistics, but as soon as an "UPDATE STATISTICS" is issued against the "new" (problem) databases, stats are gone. No problems at all with the old (good) database.

No immediate hits on MS KB or Technet. Not sure what key words to use on Google. Running out of hair to pull.

Philip

David le Quesne SSCertifiable Points: 5229 More actions · Answer 3

Being none the wiser about this, but wanting to help, i had a quick hunt around on the internet this morning and found a couple of links which may or may not be of any use:

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/fc54fe88d2655bb1/a1c44d0c32d8dd4e%23a1c44d0c32d8dd4e

http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/c15386f7ac25479c/9a12eaee470f59d9%239a12eaee470f59d9

David

If it ain't broke, don't fix it...

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 4

David,

Thanks, that was useful. They imply that corruption may be the cause (I have run DBCC CHECKDB, it found no problems), and (re)creating a clustered index might fix it, as this essentially recreates the table (all data pages are moved). I'd thought of that, but was reluctant because (a) it's a large Production table, and (b) if it worked, we'd still be in the dark regarding the underlying problem.

They also recommend calling Microsoft, an idea I also like--particularly if they still refund your service fee if it is an actual and undocummented bug.

Philip