Row Count and Number of Records differ in Query Analyzer

Question

Row Count and Number of Records differ in Query Analyzer

skrilla99

SSCertifiable

Points: 5795
More actions
February 10, 2005 at 10:55 am

#68049

I have a single table set up as follows:
colA bigint Null,
colB bigint Null,
colC int Null,
colD varchar(100) Null,
colE varchar(100) Null
When running the following plain old query in Query Analyzer -
Select * from myTable
I get a rowcount of roughly 3.4 million rows (the same number given by table properties in EM), but the row counter label given with the result set indicates that the last row is roughly 2.6 million.
Further, in the Messages tab, the number of rows is listed as ~3.4 million, but as soon as I switch back to the results tab the rowcount indicates ~2.6...
What could be causing this difference?
Sincerely,
Dan B

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

olavho SSChasing Mays Points: 646 More actions · Answer 1

Interesting situation...

I think I remember having experienced that EM table properties shows the wrong rowcount. The information shown there is based on the statistics available, and not on the actual data. So if the statistics are not entirely updated, there might be a slightly wrong row count. (Not that 2.6 mill compared to 3.4 can be considered 'slightly wrong'...).

However, I would believe that the messages tab in query analyser produced used an actual rowcount, and not any statistics-based value. So, I'm a bit puzzled. Have you checked what the truth is - i.e. "select count(*) from myTable"?

Sincerely,

Olav

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

Check your client system's memory usage when running this qury - the grid display in QA may be consuming a large amount of memory for such a large resultset, and may not be able to handle any more rows.

You have 1 rowcount, reported from the server, which is accurate, and you have another rowcount in the QA status bar, which represents how many records QA was able to stuff into its grid, which may be inaccurate if it ran into client memory limitations.

skrilla99 SSCertifiable Points: 5795 More actions · Answer 3

Thanks for the replies. I think that lack of memory is likely the culprit. I re-ran the query with results as text and it dies at about 2.8 million rows returned. I am surprised that it would not indicate that as a problem when in grid mode.

I guess it is time to add ram!

Sincerely,

Dan B

noeld SSC Guru Points: 96590 More actions · Answer 4

>>I guess it is time to add ram! <<

I guess is time for a where clause

* Noel

skrilla99 SSCertifiable Points: 5795 More actions · Answer 5

Never heard of such a thing. I think you are making that word up.

Amit Lohia SSCertifiable Points: 6104 More actions · Answer 6

Try this

SELECT rowcnt FROM Sysindex

WHERE Indid in (0,1) AND Id=Object_Id('<name of your table>')

Kindest Regards,

Amit Lohia

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 7

is autostats enabled ?

run sp_updatestats / dbcc updateusage once in a while (even with autostats on)

Check again.

Still differences ?

Is your sql2k on sp3a and on a multi-proc box ? if yes did you apply the hotfix Q814509 ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

skrilla99 SSCertifiable Points: 5795 More actions · Answer 8

I am prepared to declare memory / virtual memory as the cause.

I increased the memory on the box but still had the same problem A quick system check (which I should have done first) revealed that the system/pagefile drive was completely full. At the same time, the pagefile was only slightly higher than the configured starting size and much less than the configured maximum.

My hunch is that in some way the OS was trying to allocate more space in VM, which it couldn't. The OS then returned an error that QA couldn't handle. Unsure of what to do, QA just puked out the records it had already loaded and indicated successful execution.

It is curious to note that I did get a memory error when running the results in text mode, just not in grid mode.

I have moved the pagefile to a seperate volume and this problem has vanished. With luck I will be able to replicate the problem in test, but it is pretty low priority. I am interested to hear of any other weird problems people have run across when out of virtual memory.

Thanks for all the responses!

Sincerely,

Dan B