I have a table,
dbo.ClaimBilling, with 130,000 rows. In this table, the column
OperatorID is a
varchar(max) and is heavily skewed. 125,000 rows are 'user1', and the remaining 5000 rows are split between 6 other values, with 'user2' having a total of 3 records.
There is a non-clustered index on
OperatorID, and the clustered index is the primary key,
I have the following query currently:
SELECT DISTINCT IDClaimBilling FROM dbo.ClaimBilling cb INNER JOIN dbo.BillingItem bi ON cb.IDClaimBilling = bi.ClaimID WHERE OperatorID = @operator
No matter what value
@operator is, the estimate of rows from
ClaimBilling is ~4000, which is not close to what any value would return, and it's always a clustered index scan, it doesn't use the
operatorID index. If I remove the join and do
SELECT DISTINCT IDClaimBilling FROM dbo.ClaimBilling WHERE OperatorID = @operator
then it does use the
OperatorID index, but again the estimates are wrong regardless of the value of
@operator, this time always estimating ~18,000 or so.
I did an
UPDATE STATISTICS dbo.ClaimBilling WITH FULLSCAN before running the queries.
Why are these estimates so wrong even if the statistics know exactly how many rows there are per value?
I'm declaring and assigning
@operator a value in testing. It was originally part of a procedure and I assumed that was the problem, but it behaves the same when used in an ad hoc statement as well.
The query only runs when the user first logs in, so only maybe a couple times a day per user.