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!