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:

SELECT *
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'};

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;
UPDATE STATISTICS Awards.EarningPeriod WITH ROWCOUNT=120;

Because of the increase in volume, the plan changes:

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.

How to generate the T-SQL

Funny you should ask. I’ve created a script that does just that.

Resetting the statistics

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

DBCC UPDATEUSAGE
    (database_name, 'Awards.Transactions', index_name)
    WITH COUNT_ROWS;

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.