Bhuvnesh (1/16/2013)
sqlnes (1/16/2013)
It's weird, when I tried it today, it has back to the "correct" plan without set FORCEPLAN. it has been running fine now. I just wondering now would it take some time after re-analyzing table/indexes then optimizer can do accurate calculation?1) Have you/any job updated the statistics ?
2) have you recomplied the SP/script ?
3) did both execution have same parameter's values ?
Yes, I did "update statistics" yesterday. but it did not help. Actually, I just retried and I was wrong, the plan is still go the "incorrect" one.
And I just found that in my query,
select T1.* from
(select T0.a, T0.b, ROW_NUMBER() OVER (ORDER BY T0.A) as RN
FROM (
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
UNION
SELECT a, b FROM KEY_TABLE kt JOIN......
) T0
) T1
WHERE T1.RN BETWEEN 1 to 50
If I commented out the last line (WHERE...), it went normal. And if I added the where clause back, it went bad again. strange...