index record count difference

Question

index record count difference

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

Wiesinger (11/16/2011)
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.

And checkDB?

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

Wiesinger Old Hand Points: 353 More actions · Answer 2

no problem on sunday,

trying it to start again today after our business hour (in one hour)

Jared SSC Guru Points: 61793 More actions · Answer 3

Wiesinger (11/16/2011)
no problem on sunday,
trying it to start again today after our business hour (in one hour)

Gail, is it possible for a filtered index to be the culprit? OP said these 6 records were from a previous year. Maybe it is filtered as such?

Thanks,

Jared

Jared
CE - Microsoft

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

SELECT COUNT(*) FROM SomeTable WITH (INDEX = idx_Filtered)

Msg 8622, Level 16, State 1, Line 1

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

A filtered index can only be used if the query has a predicate that is a match or a subset of the index's filter.

Plus it's a different set of rows missing now from last week, and rebuild fixes it.

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

Jared SSC Guru Points: 61793 More actions · Answer 5

GilaMonster (11/16/2011)
SELECT COUNT(*) FROM SomeTable WITH (INDEX = idx_Filtered)
Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
A filtered index can only be used if the query has a predicate that is a match or a subset of the index's filter.
Plus it's a different set of rows missing now from last week, and rebuild fixes it.

Got it. Thanks!

Jared

Jared
CE - Microsoft

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

... meaning Gail's still waiting on this =>

GilaMonster (11/16/2011)
Wiesinger (11/16/2011)
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.
And checkDB?

Wiesinger Old Hand Points: 353 More actions · Answer 7

sorry for the delay - but the checkdb lasts a while, there are almost 250.000 tables in the db.

checkdb and again checktable - both without errors

but no difference today!!! The same selects today without difference today.

There are no maintainance jobs running last night - only checkdb and checktable

All the indices show the same record count today

I have no idea why