Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Using WITH ROWCOUNT and PAGECOUNT option of SQL Server UPDATE STATISTICS

MSSQLTips author Ben Snaidero By:   |   Read Comments (6)   |   Related Tips: More > Query Plans
Problem

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.

Solution

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
DECLARE @val INT
SELECT @val=1
WHILE @val <= 2000
BEGIN  
   INSERT INTO testtable (col1, col2, col3, col4) 
       VALUES (@val,@val % 10,@val,'TEST' + CAST(@val AS VARCHAR))
   SELECT @val=@val+1
END

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 WITH ROWCOUNT = 50000000, PAGECOUNT = 500000
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


Last Update: 1/30/2013


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.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Wednesday, January 30, 2013 - 9:52:11 AM - Sasi Read The Tip

 

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.


Wednesday, January 30, 2013 - 7:04:43 PM - eric81 Read The Tip

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


Thursday, January 31, 2013 - 12:40:35 AM - Shasthri Read The Tip

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

 


Thursday, January 31, 2013 - 9:22:50 AM - Justin Read The Tip

@Shasthri

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 - 11:28:29 AM - eric81 Read The Tip

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


Thursday, January 31, 2013 - 12:03:18 PM - Ben Snaidero Read The Tip

@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
Ben


Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.