Using UPDATE STATISTICS to fake row counts

Here’s a quick tip: When you’re evaluating query strategies, you may want to consider how your query will scale when the volume in your database goes up. This does not neccessarily mean that you have to start filling your tables with gigabytes on gigabytes of data.

Here’s a very simple test query that I built on a “fact” table with 100000 rows, joined to a lookup table with 10 rows:

FROM Awards.Transactions AS t
INNER JOIN Awards.EarningPeriod AS p ON t.EarningPeriod=p.StartDate
WHERE p.ExpiryDate>={d '2015-01-01'} AND p.ExpiryDate<{d '2016-01-01'};

Statistics on 100000 rows

Never mind the warnings on the Index Scan operators. Now, let’s use UPDATE STATISTICS to change the row counts on these tables:

UPDATE STATISTICS Awards.Transactions WITH ROWCOUNT=10000000, PAGECOUNT=24000;

Because of the increase in volume, the plan changes:

Statistics on 10000000 rows

In this case, the only change was that the query went parallel, which introduces a few more operators. However, a lot can change in a query when you scale the volume, and quite often, the entire layout of the plan can change dramatically.

Note that the query optimizer not only considers the rowcount, but often also the page count (which translates to how many megabytes of data need to be moved), so you may do well to include WITH PAGECOUNT as well.

Resetting the statistics

To reset the index statistics to the actual rowcount of the table, you can use a DBCC command:

    (database_name, 'Awards.Transactions', index_name)

Hat-tip to Paul White.

Word of warning: Don’t do this in production. The statistics on an index are global, which means that any changes to the table statistics will affect any query that uses the index. Use this only for demo or test purposes.

One thought on “Using UPDATE STATISTICS to fake row counts

  1. Pingback: Specifying Statistic Row Counts – Curated SQL

Let me hear your thoughts!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s