In this post I’ll step through a few scenarios regarding row and page counts – and their associated influence on estimated CPU and I/O cost.  I’ll be using the Credit database to demonstrate different scenarios…

Starting State

I’ll start off by checking the total number of reported data pages and row counts for the member table:

 
SELECT [in_row_data_page_count]
FROM [sys].[dm_db_partition_stats]
WHERE [object_id] = OBJECT_ID('dbo.member') AND
[index_id] = 1;

SELECT COUNT(*) AS [row_count]
FROM [dbo].[member];

The member table has 142 data pages and 10,000 rows.

Next I’ll execute a simple query and check the estimated I/O and estimated CPU of the Clustered Index Scan operator:

 
SELECT [member].[member_no],
[member].[lastname]
FROM [dbo].[member]
OPTION (RECOMPILE);

The Clustered Index Scan operator shows the following estimated costs:

  • Estimated CPU Cost = 0.011157
  • Estimated I/O Cost = 0.107569

Inflating the page count while maintaining the row count

What happens if I inflate (artificially) the member table’s page count while maintaining the existing row count metadata?

 
-- Please don't use this in production - for demonstration purposes only!
UPDATE STATISTICS dbo.[member]
WITH ROWCOUNT = 10000, PAGECOUNT = 149000;

If you’re curious about the method I’m using with ROWCOUNT and PAGECOUNT, see the SQL Server Query Optimization Team’s discussion on the subject:

UPDATE STATISTICS undocumented options

Back to the scenario, querying sys.dm_db_partition_stats shows a 149,000 in row data page count (incorrect, of course).

Re-executing my previous SELECT statement – the Clustered Index Scan operator shows the following estimated costs:

  • Estimated CPU Cost = 0.011157 (same as previous measurement of 0.011157)
  • Estimated I/O Cost = 110.373 (increased from 0.107569)

Next I’ll reduce the (artificial) page count by half:

 
UPDATE STATISTICS dbo.[member]
WITH ROWCOUNT = 10000, PAGECOUNT = 74500;

Re-executing the SELECT statement – the Clustered Index Scan operator shows the following estimated costs:

  • Estimated CPU Cost = 0.011157 (same as previous measurement of 0.011157)
  • Estimated I/O Cost = 55.1987 (decreased from 110.373)

Inflating the row count while maintaining the page count

What happens if I inflate (unnaturally) the member table’s row count while maintaining the page count?

 
-- Please don't use this in production - for demonstration purposes only!
UPDATE STATISTICS dbo.[member]
WITH ROWCOUNT = 100000000, PAGECOUNT = 142;

Re-executing the SELECT statement – the Clustered Index Scan operator shows the following estimated costs:

  • Estimated CPU Cost = 110
  • Estimated I/O Cost = 0.107569 (no change from the original estimate with 142 data pages and 10,000 rows)

Reducing the row count by half, I see the following estimated costs:

  • Estimated CPU Cost = 55.0002
  • Estimated I/O Cost = 0.107569

Summary

So for this post I demonstrated two straight-forward scenarios:

  • I/O Cost of a Clustered Index Scan sensitivity to page counts, but not row counts.
  • CPU Cost of a Clustered Index Scan sensitivity to row counts, but not data page counts.

Why care?  Regarding page counts – think about the estimated I/O cost impact that high fragmentation may have (same number of rows across many partially filled data pages).  Regarding row counts – even for narrow rows – consider the  estimated CPU cost impact that high row counts may have.

 

 

Related Posts