Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Yes, this can affect plan choice. Does running 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. –  Aaron Bertrand Nov 27 '12 at 17:40
    
The following code corrected my issue: DBCC UPDATEUSAGE (_database_) WITH COUNT_ROWS Queries are running at least 50% faster. Regards! –  RollTide Nov 27 '12 at 19:59
    
@AaronBertrand: better make it as an answer. DBCC UPDATEUSAGE is the best solution for wrong counts.. –  Marian Nov 29 '12 at 13:29
add comment

migrated from stackoverflow.com Nov 29 '12 at 7:12

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.