Brian Oster (bgoster@swbell**nosmap**.com) writes:
I can say for certain that the installation of the hot fix is what
breaks the query. Now, could it be that something else is really the
culprit and the hotfix simply "exposed it".... could be.
Well, what I tried to say is this: the security fix is for named pipes.
It seems unlikely to me that this could affect the query plan. However,
it is not unlikely that there are other post-SP3 fixes in that build,
and it is one of those fixes affects your code.
I guess I was kinda hoping I'd get about 20 responses of people saying
something to the effect of "...you moron, this has been posted 200 times
in the last week... read the FAQ for the solution..."
Unfortunately, it is not that simple.
Let me stress that I have absolutely no knowledge of what fix may have
caused your problem. But I can provide a generic discussion.
Recall that SQL Server uses a cost-based optimizer. This means that
SQL Server determines the query plan for a query by looking at histograms
of the distribution of the values in the columns involved in the
query, and combined with the available indexes, the SQL Server chooses
what it *thinks* is the best plan. I believe that for complex queries
SQL Server may not evaluate all possible plans, but may go for a plan
that looks "good enough".
Most of the time SQL Server is successful. But sometimes it goes wrong.
There may be a correlation between the data in two columns that SQL Server
is unaware, which causes it to decline a good plan. And of course,
statistics may be incorrect or out of date. But with auto-statistics
this is not that likely.
Nevertheless, bad plans appear. I recall that I had a complex 11-table
query, which first had to fix when we moved from SQL 6.5 to SQL 2000,
as it was intolerably slow at our bigger sites. A little later I had to
look at it again, because now the new version was slow at a site
which had a smaller amount of data! (But small enough for that plan
that SQL Server opted for.)
Every once in a while, Microsoft makes changes to the optimizer. I
understand that you think it is a bug when it causes your query to
crawl like a snail. But what if the change that caused this, makes
ten other queries go faster?
Note, I am not saying that are such changes. The change might well
be a pure bug, where SQL Server simply makes another decision than
the developers had intended.
I fully understand if you don't find what I say here fully satisfying.
But this is the same with any enterprise DBMS. You have a cost-based
optimizer, and that optimizer is just a very complex guessing game.
I think you also understand now, that the fact you are alone to see
a problem with this fix, is not so strange as it may seem.
Finally, one thing you could try, is to run UPDATE STATISTICS WITH
FULLSCAN on all tables involved in the query. Not that I think that
the odds are that great, but in case you have some time to spare,
you could try it and see what happens.
--
Erland Sommarskog, SQL Server MVP,
so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp