Make SQL Server Optimizer to think that record count is huge

Question

We are having few huge tables (with many millions of rows) in Production, holding invoice related data. We are facing performance issues in production due to these tables.

In Pre-production environments, as the data count is lesser, we are not able to mimic the performance testing. say, dbo.InvoiceHeader, dbo.InvoiceDetails

I have seen some where, where we can artificially update the statistics of the table, to make the optimizer believe that there are huge number of rows are present in the table. So that, we can tune the query in the pre-production and test it. I searched online. I could not find the same.

Can you please guide in, how to temporarily manipulate the statistics of a table to huge number of rows. We will do performance tuning and revert it back to original setting in the pre-production environment.

Thanks for your inputs.

Answer 1

I believe this was what you are looking for

https://sqlsunday.com/2016/02/27/update-statistics-with-rowcount/

https://blogs.msdn.microsoft.com/queryoptteam/2006/07/21/update-statistics-undocumented-options/

The following content is a combination of the links above in the event of the links not working in the future

UPDATE STATISTICS table | view
[
    {
        { index | statistics_name }
      | ( { index |statistics_name } [ ,...n ] )
            }
]
[    WITH
    [
        [ FULLSCAN ]
        | SAMPLE number { PERCENT | ROWS } ]
        | RESAMPLE
        | <update_stats_stream_option> [ ,...n ]
    ]
    [ [ , ] [ ALL | COLUMNS | INDEX ]
    [ [ , ] NORECOMPUTE ]
] ;

<update_stats_stream_option> ::=
[ STATS_STREAM = stats_stream ]
[ ROWCOUNT = numeric_constant ]
[ PAGECOUNT = numeric contant ]

<update_stats_stream_option>


This syntax is for internal use only and is not supported. Microsoft reserves the right to change this syntax at any time.

You can use the following to change the statistics

UPDATE STATISTICS <Table Name> WITH ROWCOUNT=10000000, PAGECOUNT=24000;
UPDATE STATISTICS <Table Name> WITH ROWCOUNT=120;

You can reset the statistics using DBCC UPDATEUSAGE (database_name, <table name>, index_name) WITH COUNT_ROWS;

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.