Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679 
Author Message
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679

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



Mon, 28 Mar 2005 04:13:44 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679

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

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

 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


Mon, 28 Mar 2005 08:26:39 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679
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


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

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

>  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



Mon, 28 Mar 2005 11:10:07 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679
Hayden,

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

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

Quote:
> 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.

 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



Mon, 28 Mar 2005 19:36:03 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679
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


Quote:
> 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 - did you take a copy of the showplan before and after the
change?

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

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



Mon, 28 Mar 2005 20:49:06 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679
It's a datawarehouse extract job, and we build the tables & indexes from
scratch each time so that shouldn't be an issue.

Hayden


Quote:
> 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



> > 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 - did you take a copy of the showplan before and after the
> change?

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

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



Mon, 28 Mar 2005 21:42:43 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679

Quote:
>  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.

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.

Quote:
>  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....

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

Hayden Gill
The University of Queensland
Brisbane, Australia



Tue, 29 Mar 2005 00:18:54 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679
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



Tue, 29 Mar 2005 01:12:26 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679
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



Tue, 29 Mar 2005 06:19:41 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679
 Worth a try, and also compare before/after showplans

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

 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


Tue, 29 Mar 2005 09:16:52 GMT
 Huge Performance Issues - Microsoft SQL Server 2000 Security Hotfix Update 8.00.0679
 Hayden,

Quote:
> 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.

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

Quote:
> >  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....

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

 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



Tue, 29 Mar 2005 09:16:45 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. Installation order for Security Hotfix Update 8.00.0679 Clustered server

2. Security Update Build (8.00.0679)

3. Security Update 8.00.0679

4. Microsoft SQl Server7 Security Hotfix Update 7.00.1078

5. SQL Server 2000 Security Hotfix 8.00.0667

6. Security Hotfix Update 7.00.1078 on SQL Server 7

7. SQL Server won't start after Security Hotfix 8.00.0686

8. Memory leaks in Job after installing Security Hotfix Update 8.00.0686

9. HUGE performance issues with SQL Server 2000 on NT 4 versus Windows 2000

10. SQL 7 Security Hotfix 7.00.1076

11. ODBC Driver 4.00.3513.00 Do Not Install -- Confirmed Issue with Microsoft

12. security hotfix 8.00.0686


 
Powered by phpBB® Forum Software