THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SELECT Hints, Tips, Tricks FROM Hugo Kornelis WHERE RDBMS = 'SQL Server'

Fake statistics, and how to get rid of them

This blog has moved! You can find this content at the following new location:

https://SQLServerFast.com/blog/hugo/2014/03/fake-statistics-and-how-to-get-rid-of-them/

Published Sunday, March 2, 2014 12:35 PM by Hugo Kornelis

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ben Nevarez said:

Hi Hugo,

If you want to restore the real values for rows and pages you can use the DBCC UPDATEUSAGE statement. Actually, DBCC UPDATEUSAGE can be used to correct pages and row count inaccuracies in the catalog views. For example you can try

DBCC UPDATEUSAGE(AdventureWorks2012, 'dbo.Address') WITH COUNT_ROWS

Regards,

Ben

March 2, 2014 6:42 AM
 

Ben Nevarez said:

Hugo,

Actually the ROWCOUNT and PAGECOUNT options of the UPDATE STATISTICS statement do not change any statistics object but the number of rows and pages of a table. You can see this information with the sys.dm_db_partition_stats DMV.

Regards,

Ben

March 2, 2014 6:56 AM
 

Hugo Kornelis said:

Thank you for posting that correction, and for providing the better way to reset the counts, Ben!

March 2, 2014 7:12 AM
 

jchang said:

Thomas Kesjer and I discussed statistics workarounds a few years back. I recall that he noted that when the true statistics (in stats_stream, formerly statblob) were sampled at 100%, the ROWCOUNT and PAGEGOUNT fake values were ignored, but not if sampling were less than 100%. TK might comment on this, or you could look into it as my memory is not as good as it used to be. I mentioned to Adam Mechanic a while back that I could decode the stats_stream/statblob binary, and in fact, write my artificial statistics histogram. I did this in SQL Server version 2000 and posted some details on SQL-Server-Performance. Of Microsoft then added a checksum value to the binary in version 2005.

See http://sqlblog.com/blogs/joe_chang/archive/2012/05/05/decoding-stats-stream.aspx

I have asked various Microsoft people several times if they could provide the specific checksum function so I could continue my research and would strongly advice against apply artificial statistics to production systems. This was politely (or not) declined.

Adam took an interest in this, but decided it was easier to make an actual table(s) with similar structure and desired distributions, generate statistics on this, then apply another table.

March 2, 2014 12:19 PM

Leave a Comment

(required) 
(required) 
Submit

About Hugo Kornelis

Hugo is co-founder and R&D lead of perFact BV, a Dutch company that strives to improve analysis methods and to develop computer-aided tools that will generate completely functional applications from the analysis deliverable. The chosen platform for this development is SQL Server. In his spare time, Hugo likes to visit the SQL Server newsgroups, in order to share and enhance his knowledge of SQL Server.
Privacy Statement