SSMS is reporting wrong rowcount? What?

Posted: September 13, 2011 in SSMS Tips/Tricks/General Info

Hey All,

I was recipient of a very interesting email today. An end user was using SSMS and found that when examining porperties/storage of a table the row count did not match when executing count(*) against the table. The parties involved were sure that something was wrong with SSMS or the INDENTITY column on the table.

What??????

Here is what I shared with my end user and shall share with you.

My first job as a DBA is to never trust my developer, correct ? So I would never second guess the information they are providing me. Just to be safe I opened up SSMS to validate what was being reported.

My 1st step was to examine the reported row count using SSMS properties/storage.

Next step I took was to examine the count of rows on the table itself. A count(*) was run against the table in question.

The following query returned a rowcount of 616597:

/*————————
SELECT COUNT(*) AS count FROM dbo.DLVAPPT
————————*/
count
———–
616597
(1 row(s) affected)

Sure enough when the row count within SSMS is compared to a count(*) for the table there are differnces.

A couple of clues led me to the folowing conclusion. This database had been recovered from a higher lifecycle down to the Development environment. Reindexing did not run against the DB in the lower life cycle. The following query returned different rowcounts for the Cluster and Non-Clustered indexes on the table.

/*————————
SELECT id,indid,rowcnt
FROM sysindexes
–WHERE indid IN (1,0)
where OBJECT_NAME(id)=’DLVAPPT’
————————*/
id                 indid     rowcnt
———–          ——   ——————–
805577908    1          616596
805577908    2          614184
805577908    3          614184

And the final clue was the comparison of  SSMS rowcounts and select count(*) in production matched.

To fix this issue I ran a dbcc reindex of the table.

DBCC DBREINDEX(DLVAPPT)

All the early on speculation of a bug in SSMS, indentity column issues or dbcc corruption could be put to sleep. The next step was to put the end user at ease and prove that the issue has been resolved.

Final step in my process was to pull the information exactly like our end user did? Opening properties/storage on the table showed that select count(*) matched what SSMS was reporting.

SELECT id,indid,rowcnt
FROM sysindexes
–WHERE indid IN (1,0)
where OBJECT_NAME(id)=’DLVAPPT’

id                 indid              rowcnt
805577908    1                 616597
805577908    2                 616597
805577908    3                 616597

SSMS can be used as a powerful tool. It does and can have it issues, but more than not I find it to be an effective tool in adminstering SQL Server. Can you imagine if SSMS queried every table for a rowcount? For our larger databases it would take forever to pull up not to mention the overhead it would put on the database server. Something else I hope you take away from this post is know your systems the best that you can it will help you with Problem Determination is the long run.

Thank you for reading this your friendly

DBABullDog

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s