CODEWITHSUNDEEP
sql-server
user1279887
user1279887

Reputation: 518

SQL Server database statistics - row count in index smaller than row count of table

When I run the following query:

SELECT 
    object_name(ss.object_id) object_name, 
    ss.name, last_updated, rows, rows_sampled,
    round (100 * (CAST(rows_sampled as float) / CAST(rows as float)), 2) sample_percent,
    steps, unfiltered_rows, ss.filter_definition, modification_counter, is_disabled
FROM 
    sys.stats AS ss
JOIN 
    sysobjects so on so.id = ss.object_id
JOIN 
    sys.indexes si on si.name = ss.name
CROSS APPLY 
    sys.dm_db_stats_properties(ss.object_id, ss.stats_id) AS sp
WHERE 
    so.xtype != 'S'
    AND sp.rows <> sp.rows_sampled
    AND ss.name not like '_WA%'
ORDER BY 
    object_name(ss.object_id), ss.name;

I see indexes, from the same table, whose rows values are different.

Shouldn't an index have the exact same number of rows in them as their underlying table?

Upvotes: 0

Views: 747

Answers (1)

Luis Cazares
Luis Cazares

Reputation: 3595

You're not comparing table rows against index rows. You're comparing total rows against the sample rows used to create the statistics on the index.

From Microsoft Docs

  • rows Total number of rows in the table or indexed view when statistics were last updated. If the statistics are filtered or correspond to a filtered index, the number of rows might be less than the number of rows in the table.

  • rows_sampled Total number of rows sampled for statistics calculations.

Upvotes: 3

Related Questions