473,692 Members | 2,216 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
Jul 20 '05 #1
3 3045
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
Jul 20 '05 #2
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
Jul 20 '05 #3
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
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2010
by: Stephan Eberle | last post by:
Hi @ll... I wanted to code a small proxy-like program for testing purposes. Another application binds to a local port 12203 and listens there for incoming datagrams and sends datagrams. My proxy schould then take the datagrams sent by the other application and forward them. In return, it has to receive the incoming datagrams from the "server" (remote machine) and forward the data to the local port. I did it before with vb6 and it worked...
1
4167
by: Ben M. | last post by:
Greetings all, This should be an easy task, and Im sure it is, but as many times as I have tried, I cant seem to get this to work properly. We changed ISPs recently from a shared host to a co-located server, and our former host was nice enough to send us a backup of our old SQL2000 database (about 5MB). I went into Enterprise Manager, created an empty database with the same name
2
1521
by: Jens | last post by:
Microsoft Security Paradigmes are Irritating. I sure they're fine once you know what they are, but for the uninitiated it's quite counterintuitive to work with. I moving an old SQL Server-backend-IIS5/ASP-frontend application to servers with windows 2003 standard edition. One server will run the database the other will run IIS 6.0. Note that i haven't set-up a domain, which i think requires one machine to be domain controller which...
3
2156
by: Otmar Ganahl | last post by:
Hi! In my application I transform a docbook file to a html view using the webcontrol "Xml" (with a xsl).It works fine, except the using of the tag "fileref" in the docbook-file will cause an exception: _ctl1_DesktopThreePanes1_ThreePanes__ctl3_xml1: Failed! Object reference not set to an instance of an object. (The IE6.0 xml-parser transform the docbook file
9
2264
by: Christian Wetzig | last post by:
hi, i have a table with test_field of type "TEXT". when i do: select test_field from test where id=1; the connection (with psql) hangs and no output is received, so i have to kill psql. this occurs, at first sight, if length(test_field)>32748.
1
1445
by: Terrell Miller | last post by:
I have an Access front end with ODBC drivers to a SQL2k box. All our security is Wiondows-authentication on the back end, we don't use Access security workgroups. THerefore when I try to get the userID for the CurrentProject object (if memory serves), it returns "Admin" for every user. Is there a way to run a query (pass-through if necessary) that will go grab the SQL2k User and return it to Access? TIA,
1
5030
by: ferg | last post by:
Hey folks, I'm only beginning to understand the ins and outs of programming, let alone C# and thing like ADO/CDO etc etc, but I'm on my way to trying to develop a User Help Desk solution on Exchange 2000/2003 using C#. I understand that E2K doesn't support ADO.Net (despite being a ".Net Enterprise Server" or whatever), so would someone be able to point me in the right direction for an easy way to work with Exchange messages, contacts...
2
1422
by: Carlo Marchesoni | last post by:
I have a Crystal Report that accesses one table in a very simple Access Database (no user no password). When Exporting the report to PDF format, the browser gives the following error: Logon failed. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
17
2053
by: 57R4N63R | last post by:
I'm currently building a website for one of the client. There has been few errors here and there, but just recently the problem is getting worse. Basically the symptoms is that when the user try to access the page, it takes really long time to load. However, after up to 1 hour, the website will run fine again as normal. This issue has been there with the site. I usually just ask the system admin to restart the IIS Service. However, the...
5
5390
by: Michael Rudolph | last post by:
Hi newsgroup, I have an issue with the configuration of a DB2 federated database (WebSphere Information Integrator) in conjunction with the relational wrapper for Oracle on AIX. DB2 seems to not use the existing tnsnames.ora. The Oracle client is installed and the connection to the Oracle database using tnsping and sqlplus is possible (for the db2instance user). For that I changed the environment for the db2instanceuser and added the...
0
8604
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9083
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8800
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7627
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5819
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4324
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4557
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2974
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1957
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.