Strange count results with uncommitted isolation level

Question

Strange count results with uncommitted isolation level

kladibeeto

SSCommitted

Points: 1582
More actions
August 19, 2010 at 5:09 am

#222757

Hi all,
I am loading fact table (app. 65 M of rows).
In another window I was execute this query
set transaction isolation level read uncommitted
select COUNT (*)
from table -- this is fact table
The results which I got confused me:
Results:
--17 778251 1.
--16 6888802.
--57 0764683.
--15 6619244.
--57 0764685.
--14 869420...
--57 076468...
--14 302536...
--57 076468...
--94 14409 ...
--77 65001
First I thought that process is in rollback but that is not the case.
When I look execution plan of the query above, I see that optimizer choose to scan the nonclustered index (probbably the smallest one )
Does anybody have idea why I got this non-incremental results (statistics,isolation level)?
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

This happens with read_uncommitted sometimes, due to how data is moved in and out of indexes as it is written and updated. You could try to force it to use the clustered index when you select the count, which in theory would be more accurate and what you expect.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If accurate results are important, don't use readuncommitted

See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kladibeeto SSCommitted Points: 1582 More actions · Answer 3

Thanks for the answer.

The result is not really improtant because I was just checking in where is my load.

I got strange results becasue index which optimizer decide to use index which was modified by insert process in the moment when I run the query for counting.

Later I saw that process is returning rowcnt collumn from sysindexes to zero for all indexes on Fact table.

It seems that process was in rollback but sysprocesses return that cmd for this process is INSERT.

I will test it again!

Maybe there is some problem with cmd collumn in SQL Server R2 but I am not sure in this yet.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

kladibeeto (8/20/2010)
Later I saw that process is returning rowcnt collumn from sysindexes to zero for all indexes on Fact table.

sysindexes is deprecated and should no longer be used. It will be removed in a future version. Besides, the row count in sysindexes shouldn't be considered accurate.

Try sys.partitions, but understand that the row count in there is not certain to be accurate for an in-progress transaction.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass