Slow query

[bobbo]

I have a fairly simple query that joins a table (ScreenContents) to a view (vw_ISN) like so:

FROM ScreenContents WITH (NOLOCK)
INNER JOIN vw_ISN ar WITH (NOLOCK) ON ScreenContents.LabelCounter = ar.SystemItemCounter
AND (ar.SystemItemTypeCounter = 12)
AND (ar.JurisdictionCounter = 93)
AND (ar.LanguageCounter = 1002)

Now if I run this query I get 30000 results back in 1 second. If however I change the last line to
AND (ar.LanguageCounter = 1003)
it takes 12 minutes for approximately the same number of rows. There are around 15000 rows in the table referenced by vw_ISN (ISN) for both languagecounters.

The table has a primary key and one other index. This other index is composed of
SystemItemTypeCounter
SystemItemCounter
LanguageCounter
JurisdictionCounter

I rebuilt the primary key but this made no difference. I checked the statistics and they appeared to be up to date with good selectivity.
I then ran the following statement
UPDATE ISN set languagecounter=languagecounter where languagecounter=1003
This solved the problem and the query runs in 1 second for languagecounter = 1003. I rechecked the statistics and this update did not affect them. I then restored a backup and ran a similar update query against a column that is not included in the index and another that is included in the index. Neither made any difference.

How did updating the languagecounter field solve the problem?

[bobbo]

Update to this:

Adding a new language (1004) which would add another 15000 records to the ISN table had the same effect (i.e. the query is fast for 1003).

[rmiao]

Did you compare execution plans?

[bobbo]

I did and one way (the fast way) was using a clustered index scan and the other (slow) one was using an index seek and a row lookup. The question is why (when the data was more or less uniform) did it change when I ran that update when the statistics were unchanged.

[rmiao]

Since data distribution changed after you updating the table, sql will generate new plan accordingly.

[bobbo]

In the normal course of events, I would agree but there was no change of data. The query I ran :

UPDATE ISN set languagecounter=languagecounter where languagecounter=1003

did not actually change anything.

[rmiao]

What's sp level on that server?

[bobbo]

SQL 2005 SP3
9.0.4035
Developer Ed

[rmiao]

Looks like should report it to Microsoft connect: https://connect.microsoft.com/dashboard/?wa=wsignin1.0