I have a SQL statement and I run SQL Optimizer on it. Sometimes it shows my original is fastest, sometimes it shows other alternatives as the fastest. Why is that?
If SQL Optimizer finds queries that have very close actual run times, you may find that sometimes it may show a different one being the fastest. Take the following example.
Your original SQL runs at 5 seconds. You run it again, you might catch the server a good time when it's free and it may show up as 4 seconds. If you run it again it may show up as 6 seconds, but it usually stays in the range of 4 to 6 seconds. Say you're comparing it to one of your alternatives. The alternative runs in the range of 4 to 6 seconds.
So it is possible for a case where your original query ran at 6 seconds and the alternative ran at 3, showing that you're alternative is best. Another time you might run them and you'll see the original ran at 4 and the alternative ran at 5, thus the original is better.
But on average, in this scenario, the alternative should be best choice. And usually this only happens when the run times are close together, in which case you can go with either SQL or choose on that has a better cost or less cpu usage or whatever you need.
