Wrong index being hit??
-
I ran into the observation on a replicated database where select count(id) from tableA was returning different results between my two servers but the servers have the identical amount of records. On both servers I did the following 2 queries:
select datepart(year,indate),count(id)
from tableA
group by datepart(year,indate)
compute sum(count(id))
..then.. select count(id) from tableA.
The datepart query on both servers returns a matching number of records, and the execution plan shows it hitting the 'indate' index. The select count query returns varying hits, however. The backup server matches the first query, and the index hits the index called 'id' as expected. On the primary I get more records than actually exist, and the index it's hitting is not id, but one of my other indexes. I can replicate this on newly built databases in my test environment, and see it hitting varying indexes, and not the id index. Has anyone seen that happen? The type of indexes are identical between functioning and non-functioning databases. My SQL version is the same as well.
-
A little more info on this. I've confirmed it's hitting the last-created index on the table. Eg. id's indid in sysindexes =2. If I create an additional index on id, say idA, it gets indid = 42 in sysindexes, and then my select-count query hits the idA index. If I then go and create another index on anything else, eg. NewIndexC that gets indid = 43 in sysindexes, and the select-count query now hits that index.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply