SQL Server Row Count – sp_spaceused Rows Discrepancy

performancesql serversql-server-2005

I am researching a performance problem with an application running on SQL Server 2005. I've conducted performance tuning, and I realize that a key table is poorly structured(poorly chosen clustered index). Unfortunatly, I've been asked to hold off on structural changes until some scheduled down time so I must improve performanc without fixing the root structural problem.

Thus, my question is related to the rowcount returned in sp_spaceused. I understand that this value may not be current, but it should be close. The discrepancy is 6 rows returned vs. an actual of over 18k rows. I suspect that this discrepancy is causing some interesting execution plans (index scans instead of index seeks). All other indexes on the table appear to have a correct row count.

Can anyone help explain this? Is this taken into consideration when selecting an execution plan? If so, please provide some direction to updating this value? I've tried rebuilding indexes and updating statistics.

EXEC sp_spaceused @objname=N'dbo.tblEMPLOYEES_MASTER';

Returns rows: 6

SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary, 
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber;

All other indexes(non-clustered) on the table return a 18,163 row count, however the clustered index (IndexID of 1) has a row count of 6.

Thanks everyone for your feedback!

Best Answer

Yes, this can potentially affect plan choice. Does running DBCC UPDATEUSAGE make everything right again? e.g.

DBCC UPDATEUSAGE('yourdb', 'dbo.tblEMPLOYEES_MASTER'); 

Make everything right again? That doc says:

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.