Home, Parent: SQL Server Cost Based Optimizer
AdventureWorks, Index Seek, Key Lookups, Table Scan, Joins, Insert, Update, Delete,
Parallelism I, Parallelism II

Examples with AdventureWorks

Since Adventure Works database is populated from the same CSV files, the row count should be the same from instance to instance. However, it is possible that there may be minor variations in size. Also, there is no assurance that the statistics generated to represent the data distribution is exactly the same. The execution plan can only use the data distribution statistics for row estimates, so it is always necessary to check the data distribution statistics to generate essentially the same results as described here.

For the Adventure Works database used in examples here, the Sales SalesOrderHeader table has 31,465 rows, and 5600KB data or 700 pages of in-row-data, as shown below.

exec sp_spaceused 'Sales.SalesOrderHeader'

sp_spaceused

SELECT in_row_data_page_count, in_row_used_page_count, in_row_reserved_page_count
FROM sys.dm_db_partition_stats p
WHERE p .object_id = OBJECT_ID ('Sales.SalesOrderHeader' ) AND index_id < 2

db partition stats

Below is the data distribution statistics. (See Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 for details on data distribution statistics.) The default sample for low row counts should be a full sample. Not every statistics sampling will be represented by exactly the same sample set, particularly in the Range High Key values, for which the number of Equal Rows is an integer. The key values in between the Range High Key values (the Range Rows) are characterized with an Average Range Row distribution.

DBCC SHOW_STATISTICS( 'Sales.SalesOrderHeader' , IX_SalesOrderHeader_CustomerID )

DBCC SHOW STATS

Finally check the state of parameterization to ensure that the execution plan estimate rows represent the exact query, instead of using parameterized values.

SELECT is_parameterization_forced FROM sys.databases WHERE name = 'AdventureWorks'

 

I have made extended versions of sp_spaceused in sp_spaceused2 and sp_helpindex in sp_helpindex3.

Other SQL procedures here SQL Scripts.