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.Make everything right again? That doc says: