SQL2K: Performance Problem with a query after Security Patch Ms03-031

[Brian Oster]

After applying security patch MS03-031 (Sql server ver 8.00.818) a
query that used to execute in under 2 seconds, now takes over 8
Minutes to complete. Any ideas on what the heck might be going on?

I have tested this extensively and can say for certain that installing
this hot fix is what has caused the performance problem. I just don't
know why or how to fix it.
Brian Oster

[Erland Sommarskog]

Brian Oster (bgoster@swbell **nosmap**.com) writes:

After applying security patch MS03-031 (Sql server ver 8.00.818) a
query that used to execute in under 2 seconds, now takes over 8
Minutes to complete. Any ideas on what the heck might be going on?

I have tested this extensively and can say for certain that installing
this hot fix is what has caused the performance problem. I just don't
know why or how to fix it.

And it is even more difficult to say from a distance. It is not likely
that the security fix as such is the culprit, but there might be other
fixes that were bad for you.

To actually "prove" that the hotfix is the cause, you would have to
build two different environments, and then run the query in both.
Even if you are not looking for proof, but only a solution, this
might still be worth the effort, so that you can compare query plans.

If you can come that far that you can actually produce a complete
repro that creates the table(s), populates it/them, and the runs
the query, I will glad forward that script to Microsoft for con-
sideration. However, since this would take quite some of your time,
I fully understand that you don't want to do this.

A milder alternative is just to post the tables, the indexes and the
query. Maybe there is some simple improvement that can be done to
the query.

You can also opt to open a case with Microsoft. If they accept the
change as a bug, you should be refunded for expenses you were
charged for the case. However, performances problems are always a
bit iffy. If they judge that your query was poorly written, you may
not get a refund. (I should add that at this point I am completely
speculating. I have very little personal experience of MS Support.)
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Brian Oster]

On Mon, 15 Sep 2003 21:31:35 +0000 (UTC), Erland Sommarskog
<so****@algonet .se> wrote:

Brian Oster (bgoster@swbell **nosmap**.com) writes:

After applying security patch MS03-031 (Sql server ver 8.00.818) a
query that used to execute in under 2 seconds, now takes over 8
Minutes to complete. Any ideas on what the heck might be going on?

I have tested this extensively and can say for certain that installing
this hot fix is what has caused the performance problem. I just don't
know why or how to fix it.
And it is even more difficult to say from a distance. It is not likely
that the security fix as such is the culprit, but there might be other
fixes that were bad for you.

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. 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..."


To actually "prove" that the hotfix is the cause, you would have to
build two different environments, and then run the query in both.
Even if you are not looking for proof, but only a solution, this
might still be worth the effort, so that you can compare query plans.
This is exactly what I have done. Actully used 3 different servers to
test it. As soon as I apply the hotfix... query dies. Same tables,
same indexes, same sql, etc.


If you can come that far that you can actually produce a complete
repro that creates the table(s), populates it/them, and the runs
the query, I will glad forward that script to Microsoft for con-
sideration. However, since this would take quite some of your time,
I fully understand that you don't want to do this.

A milder alternative is just to post the tables, the indexes and the
query. Maybe there is some simple improvement that can be done to
the query.
Well since I needed an immediate solution, I was able to "code" around
the problem in my app. What concerns me is that something else might
"break" and it would be nice to know the nature of this problem which
might aid in solving the next one that might pop up.


You can also opt to open a case with Microsoft. If they accept the
change as a bug, you should be refunded for expenses you were
charged for the case. However, performances problems are always a
bit iffy. If they judge that your query was poorly written, you may
not get a refund. (I should add that at this point I am completely
speculating. I have very little personal experience of MS Support.)

I have no doubt that they would judge my query to be "poorly
written"... hell, I'd probably agree. :) But the fact of the matter
is that for about 5 months the query returned a result set in under 2
seconds until the hot fix was applied, at which point the query took
over 8 minutes to return a value. Too me that is a performance hit
that is simply too large to be discounted as JUST a performance issue
and, IMO, is some sort of a bug.

Appreciate the response. I will let the higher up powers decide if
they want to pursue it further w/ MS. I just have concerns that there
is an issue with our install of SQL server and/or the hot fix and
instead of resolving the issue we created a work around w/o really
knowing the root cause of the problem.

Brian Oster

[Erland Sommarskog]

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