Hi All,

I'm wondering if anyone else has had a problem with installing the
cumulative hotfix for SQL2000 (Oct 3)

We've had huge perfomance issues with it, and have had to back it out.

As an example a SQL statement that took 27mins prior to the patch, increased
to 5 hrs 32 mins after the patch was installed.

Machine resources should not have been an issue, both tasks were run
overnight on a Quad Xeon 500 with 4GB RAM

This was not an isolated example.

TIA
Hayden Gill
The University of Queensland
Brisbane, Australia

 Hayden - did you take a copy of the showplan before and after the change?

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

No, I had a look but I didn't save it. (Actually, how do you do that - I've
never tried ?)

It was back to 27 mins again after I backed the patch out, and it was the
only change I made.

I would have thought that a security patch shouldn't be effecting the query
engines choice of query plan, but something major has changed that's for
sure.

Hayden

Hayden,

Display it as text in QA and save the results from there

 Security patches are generally applied to the current hotfixed code that MS
are working on turning into the next service pack.  Therefore when you pick up
the security fixes you pick up a whole heap of other stuff as well - a whole
heap of fixes and their side-effects.

 I rarely install hotfixes of any kind onto production servers unless I am
experiencing a known issue.  (Servicepacks are another matter).  Security
hotfixes are only really needed if you've exposed a SQL Server directly to the
internet - and I've never directlyly exposed any dbms to the internet....

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

Hayden,

Further to Neil's notes I would always reindex, update stats and compile
stored procs and views etc whenever the version of sqlservr.exe changes as
the optimizer could have changed.

Chris Wood
Alberta Department of Energy
CANADA

It's a datawarehouse extract job, and we build the tables & indexes from
scratch each time so that shouldn't be an issue.

Hayden

That's a bit of a worry... I hope SP3 won't have this issue!

I applied to get on the Beta for SP3 but I haven't heard anything yet.  I'm
more than happy to help MS resolve the issue if they're interested.

Thanks Neil, but a University campus isn't quite like that...  IT Students
like to hack !

Hayden Gill
The University of Queensland
Brisbane, Australia

We were surprised by some negative performance hits after some of our
customers upgraded to SQL2000 sp2.  We spent quite a bit of time tracking
them down, and eventually realized that certain queries withing stored
procedures (and only those within stored procedures) were using a different
plan after the upgrade.  For the most part things were fine, but a few of
the queries now had Index Scan as part of their plan.

SInce the tables were very large, the impact was huge.  A query that should
have taken milliseconds was taking 10-15 seconds, AND was acquring and
holding ocks and impacting the rest of the system.

A simple index hint restored the good plan and the millisecond performance,
but it took a while to get to that point.  We could not have done this work
without the Profiler, or without scritinizing the Profiler output looking
for trouble.  It also took us a while to choose the right Profiler optionsso
that query plans were included in the output.

In our case, we thing the latest SQL Server is probably more aggressive
(ironically) in optimizing queries within procedures, in particular when
variables are used as part of the WHERE clause.  The queries that had the
bad plans were very simple, and the index choice was seemingly very obvious,
and yet a clustered index scan was chosen as the best plan.

We verified that this behavior was introduced in the latest service pack,
and verified it on several machines doing very careful before/after the
update profiler traces.  I'm not sure if you are having the same or even a
similar issue, but the Profiler was a life saver.

James Hunter Ross

Thanks, we're already on SP2 but it does seem like something similar is
happening.

The Sql statement is simpy an Insert into [Table1] Select * from [View1]

View1 is a join of about 30 tables producing about 300,000 records.

All the tables used in the view are rebuilt (and re-indexed) from scratch
each time the job runs.

About the only thing that isn't rebuilt is the View, so maybe I should try
that on a test box

Thanks,
Hayden

 Worth a try, and also compare before/after showplans

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq

 Hayden,

 That's what beta's are for!  When SP3 beta is out make sure you do test it and
feedback.

 Ah yes, didn't see the signature.  In corporations if someone "hacks" they are
out the door no questions asked.  Might be a tad harsh in your environment I
guess.

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 Reply here - no email
 SQL FAQ (484 entries) see
 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq