SQL Server Row Count sp_spaceused rows discrepancy

Question

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!

Answer 1

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.