sqlskills-logo-2015-white.png

Row and Page Count Influence on Estimated CPU and I/O Cost

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.

 

 

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.