November 16, 2011 at 1:59 am
Hello,
I have a table with approximately 1.300.000 records (Dynamics Navision 5.1), on the table are 21 indices. The problem is that a select count(*) without using an index delivers all records, the same statement with using an index delivers a count to 12 records less.
After an index rebuild the result was o.k.
Now some days later I have the same problem with 6 records less.
Any idea what causes the problem?
The server is a SQL 2008 R2 128GB Memory
November 16, 2011 at 2:06 am
Can you show us the exact queries you're running to get the counts, please?
Thanks
John
November 16, 2011 at 2:13 am
select COUNT(*) from [dbo].[AT101 Swietelsky Bau GmbH$Sachposten]with(index(0))
where
[Sachkontonr_]= '250020' and
Buchungsdatum <= '2011-09-30'
select COUNT(*) from [dbo].[AT101 Swietelsky Bau GmbH$Sachposten]with(index(7))
where
[Sachkontonr_]= '250020' and
Buchungsdatum <= '2011-09-30'
First selcect delivers: 1325748
second: 1325742
November 16, 2011 at 2:34 am
Does the column(s) indexed in index ID 7 contain any NULLs? This would explain the difference, although it doesn't explain why it disappears after you rebuild the index.
John
November 16, 2011 at 2:58 am
Sounds like you had some minor database corruption. Might be an idea to increase the frequency of checkDB for a while.
Won't be nulls, because count(*) means count all rows regardless, not count non-null values.
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
November 16, 2011 at 3:00 am
I checked it - there are no Nulls
the problem is, that the application (Navision) comes to different results, depending on what report was started. (with the profiler I saw the selects in the reports uses different indexes).
the checktable (including indexes) reports no error.
Werner
November 16, 2011 at 3:07 am
Wiesinger (11/16/2011)
the checktable (including indexes) reports no error.
No, it won't now, because the problem went away when you rebuilt the indexes.
Indexes with a different number of rows to the table is a form of database corruption. It's a form that rebuilding the indexes does fix.
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
November 16, 2011 at 6:38 am
I executed the checktable befor rebuilding the index - there was no error log;
the question is, what can cause this difference of rows in the index
November 16, 2011 at 6:46 am
One more time with feeling =>
GilaMonster (11/16/2011)
Wiesinger (11/16/2011)
the checktable (including indexes) reports no error.No, it won't now, because the problem went away when you rebuilt the indexes.
Indexes with a different number of rows to the table is a form of database corruption. It's a form that rebuilding the indexes does fix.
When's the last time you ran a full checkdb?
November 16, 2011 at 7:03 am
every week on sunday
November 16, 2011 at 7:06 am
Do you have any errors in the logs about possible corruption?
November 16, 2011 at 7:08 am
no - there is absolute no indication for an error
November 16, 2011 at 7:11 am
Checking assumptions (since nothing else is making sense atm) =>
What isolation level did you run this under?
Was it under repeatable read and in the same batch?
I have a few of those monster tables, and 6 rows inserted is always only 1 instant away.
November 16, 2011 at 7:27 am
the 6 records are old records (last year), we mentioned the error, because there where reports with different results.
to find the problem I created a simple select with and without the use of an index. Last week we had the same problem with 12 records, so I rebuild all the indices of the table, and it worked. Today I have the same problem again with 6 other records.
the error is repeateable with the simple select
also the sys.dm_db_index_physical_stats reports different record counts
November 16, 2011 at 7:46 am
Right now the only thing that makes any sort of sense in my mind is readuncommited isolation level to explain this. Since it seems it's not corruption.
Hopefully Gail has seen this before and knows about other options.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply