Statistics - Never calculated on index

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Statistics - Never calculated on index

Statistics - Never calculated on index

Rate Topic

Display Mode

Topic Options

Author

Message

Davis H

Posted Wednesday, February 01, 2012 12:27 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:03 PM
Points: 23, Visits: 254

In taking a deeper look at the statistics on our 2008 instance, I have found several index and column statistics which have never been calculated. I looked in the sys.stats table using the STATS_DATE function. For a gross majority, there is a date there. I went under the object explorer to the Statistics branch under the object tree, went to the properties of the statistic in question and sure enough, at the bottom where it has: Statistics for these columns were last updated: it says (never). So, I click on the box to Update statistics for these columns and nope. Doesn't do it. I have run UPDATE STATISTICS with fullscan, sample, all ... nothing. The datatype for this one in particular is a UNIQUEIDENTIFIER (I know ... not my design), but dozens of other indexes are made of the same and they do have a date populated for them.

Not much out there in the GoogleVerse on this, so I would appreciate any and all thoughts.

Thanks

Post #1245271

GilaMonster

Posted Wednesday, February 01, 2012 12:33 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

Is the table in question empty?
Is the index_id possibly 0 (a heap)?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1245277

Davis H

Posted Wednesday, February 01, 2012 12:40 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:03 PM
Points: 23, Visits: 254

Oy!!! I'm sooo embarassed. Blush

Yes, the tables are empty.

Sometimes it is the most obvious things that get us.

Thanks

(Where's the delete thread button?)

Post #1245284

GilaMonster

Posted Wednesday, February 01, 2012 12:49 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

If it makes you feel better, that exact scenario stumped me for more than a day a while back.

No delete thread button, was removed after too many people deleted threads with huge amounts of info in after deciding they either had their answer or weren't going to get exactly the answer they wanted. Lots of people browse these boards without ever posting and learn from the existing threads.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1245293

Davis H

Posted Wednesday, February 01, 2012 1:23 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:03 PM
Points: 23, Visits: 254

My deletion request was said tongue-in-cheek. Whistling

Like you, I'm sure we won't be the last person to overlook this answer and it is good to have it out in the GoogleVerse.

Thanks, Gail!

Post #1245313

Dmitry Otblesk

Posted Thursday, October 11, 2012 10:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 9:43 AM
Points: 4, Visits: 18

Well in my case the situation is different. The table is not empty. It is not a heap either. However clicking on "Update statistics for these columns" checkbox in SQL Management Studio has no effect whatsoever. Statistics are not updated after that even if I run the query, which needs them.

Statistics still could be updated programatically without any problem. But I am simply curious why this checkbox in SSMS does not work? Any suggestions?...

Post #1371649

Davis H

Posted Thursday, October 11, 2012 11:14 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:03 PM
Points: 23, Visits: 254

How many rows are in the table?

Can you run a trace and see what command is run after selecting the checkbox?

Post #1371690

Dmitry Otblesk

Posted Friday, October 12, 2012 9:46 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 06, 2013 9:43 AM
Points: 4, Visits: 18

Davis H (10/11/2012)

How many rows are in the table?

Can you run a trace and see what command is run after selecting the checkbox?

There are several thousands rows in the table.
The SQL trace shows that no command at all is sent to the server after selecting the checkbox (which was expected knowing the outcome).

I checked Microsoft forums and found that many people faced the same problem. And this is possibly a bug in SSMS.

Post #1372394

« Prev Topic | Next Topic »

Permissions