By:   |   Comments (6)   |   Related: > Maintenance


Ever wondered what the SQL Server explain plan for you query would look like if there were millions of records in the tables of your SQL Server database. This is a question that SQL Server database administrators (DBAs) as well as developers often ask and in most cases what they do is load a bunch of dummy data into the tables and check the explain plan that is generated. If there is a lot of referential integrity defined on the SQL Server database this can become a very time consuming task. In other cases you may not have the necessary disk space in your development environment to load massive amounts of data into the tables. This tip will look at an alternative to this method.


Available since SQL Server 2005 have been the undocumented options of the UPDATE STATISTICS command, WITH ROWCOUNT and WITH PAGECOUNT. Using these options allows you to trick the optimizer into thinking the tables have many more records and pages than they actually do. Let's use a simple example to illustrate how these options work.

Sample table setup

-- Table creation logic
CREATE TABLE testtable ([col1] [int] NOT NULL primary key,
                        [col2] [int] NULL,
                        [col3] [int] NULL,
                        [col4] [varchar](50) NULL);
CREATE NONCLUSTERED INDEX [IX_testtable] ON testtable ([col2] ASC);
-- Populate table
SELECT @val=1
WHILE @val <= 2000
   INSERT INTO testtable (col1, col2, col3, col4) 
       VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR))
   SELECT @val=@val+1

Now that we have a small table with some data loaded into it, let's take a look at what the explain plan looks like for the following simple query.

SELECT * FROM testtable WHERE col2 = 6;

Original Explain Plan with a clustered index scan

We can see from the explain plan that this query is going to do an index scan. Now let's update the statistics on both the table and index using the new options. Here is the syntax:

UPDATE STATISTICS testtable IX_testtable WITH ROWCOUNT = 50000000, PAGECOUNT = 500000

Before we take a look at the new explain plan let's take a look at the sys.partitions and sys.dm_db_partition_stats DMVs and see what effect the statistics update had on these objects. Note: The output below shows the DMVs before and after the UPDATE STATISTICS command was run. Only the relevant columns are displayed.

sys.partitions and sys.dm_db_partition_stats DMVs before and after UPDATE STATISTICS

We can see from the output above that the row_count and in_row_data_page_count have been updated based on what we supplied to the UPDATE STATISTICS command. Now let's take a look and see if this has had any effect on the explain plan.

Explain Plan After Statistics Update where the optimizer is using parallelism

This explain plan shows that the optimizer has now chosen a parallel execution plan. Using these undocumented options we can get an idea of how the explain plan for a query may change due to larger data sets without having to load large amounts of data into our tables.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, January 31, 2013 - 12:03:18 PM - Ben Snaidero Back To Top (21847)

@Sasi, @Shasthri

There was actually a typo in the script above that has now been corrected.  It should have read WHILE @val <= 2000
Thanks for reading

Thursday, January 31, 2013 - 11:28:29 AM - eric81 Back To Top (21845)

Actually I did both and neither worked.  sys.partitions shows the same row count 50000000 after I executed both commands.

Thursday, January 31, 2013 - 9:22:50 AM - Justin Back To Top (21837)


You should just be able to run UPDATE STATISTICS <table or index name> to have SQL Server recalc the statistics for you.  Or I would imagine if auto update stats is on that this would revert it back.

If you did it on the entire DB for some reason you could also alternatively run sp_updatestats to update all statistics in the DB.

Thursday, January 31, 2013 - 12:40:35 AM - Shasthri Back To Top (21818)

@Sasi : In the While Statement change the condition to WHILE @val != 2000  and check.I think then you can see the difference


Wednesday, January 30, 2013 - 7:04:43 PM - eric81 Back To Top (21807)

I totally forgot about this option.   Now how do I revert my stats back to the original row counts?

Wednesday, January 30, 2013 - 9:52:11 AM - Sasi Back To Top (21792)


Thats some good testing. But I dont see a difference in the execution plan even after doing the update stats  with pagecount and rowcount options.

get free sql tips
agree to terms