We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of the
databases runs at 100% CPU when ever a query it made. Nothing else has
changed! Before SP4 the queries are quite small and took only 1-2 seconds to
run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
If I copy the database to another server with SP3 on it, it works fine. If I
then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds.
The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN with
RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
temp, msdb, etc And monitoring them there seems to be no disk problems,
queues are short, etc
Can anyone help? either hot fix, uninstall (don't have SP3 backup of master,
etc), sp changes.
Cheers
nigelDid you run update_statistics , reindex database.
Maybe recompile stored procedures!
Greetz
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Nigel" wrote:
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel|||Thanks.
I've run these manually and as part of the daily maintenance plan. The
database also has Auto update and create statistics.
At present the part where it all goes mad does not use any SP, but is called
via a JDBC Java client directly.
Task Manager view can be seen here.
http://www.callacomp.co.uk/sql/orion%20cpu.jpg
Performance monitor SP3 (live)
http://www.callacomp.co.uk/sql/sp3mon.jpg
Performance monitor SP4(test machine)
http://www.callacomp.co.uk/sql/sp4mon.jpg
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Hate_orphaned_users" wrote:
> Did you run update_statistics , reindex database.
> Maybe recompile stored procedures!
> Greetz|||Nigel wrote:
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel
Have you reviewed the execution plan for one of the problem queries?
Did you update statistics (manually, not automatically) when you moved
the database?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It sounds like the query plan has changed so you may need to tune or change
that query slightly so that it gets a better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:85602962-D3D7-4951-8E08-4C70D67C4855@.microsoft.com...
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of
> the
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
> to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
> I
> then install SP4 on this machine it now runs at 100% CPU for 30-50
> seconds.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN
> with
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of
> master,
> etc), sp changes.
> Cheers
> nigel|||I've updated statistics -sp_updatestats
I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
The process uses no SP all queries are suppled from the client using JDBC,
doesn't this mean that the query plan is updated as and when. Certainly afte
r
a reboot? Or am I wrong?
I've run PSSDiag but I'm no expert at looking at the traces files. Either a
little help or lots of reading to do?
Regatds
NIgel|||It may very well generate a new query plan each and every time you execute
it. But that does not mean it will generate a good one or even a different
one. If it generated a bad plan the last time you executed it and the
conditions or parameters have not changed chances are it will generate a bad
one again and again and again. Some times the conditions are such or the
data is such that the optimizer thinks it has the right plan but ends up
with a bad one for your conditions. By changing the query slightly ( an IN
to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
and in this case hopefully better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
> I've updated statistics -sp_updatestats
> I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
> The process uses no SP all queries are suppled from the client using JDBC,
> doesn't this mean that the query plan is updated as and when. Certainly
> after
> a reboot? Or am I wrong?
> I've run PSSDiag but I'm no expert at looking at the traces files. Either
> a
> little help or lots of reading to do?
> Regatds
> NIgel|||OK Cheers, I start looking into this. So does this mean that SP4 has changed
how query plans are calculated?
--
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Andrew J. Kelly" wrote:
> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a b
ad
> one again and again and again. Some times the conditions are such or the
> data is such that the optimizer thinks it has the right plan but ends up
> with a bad one for your conditions. By changing the query slightly ( an IN
> to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
> and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
>|||Every service pack and or edition has the potential to change query plans to
some extent and Sp4 is no exception. Most of the time it is due to fixing
bugs and occasionally a query plan may be affected by it. This does not mean
all of them will change.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:0DD973D6-5D6C-4633-B533-350115E0477C@.microsoft.com...[vbcol=seagreen]
> OK Cheers, I start looking into this. So does this mean that SP4 has
> changed
> how query plans are calculated?
> --
> Nigel
> Underpaid and working for the NHS.
> So help me and save lives!
>
> "Andrew J. Kelly" wrote:
>|||Hi, you need at least apply postSp hotfix (2187) and check again.
Regards,
Oleg.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOSI3EpPHHA.1252@.TK2MSFTNGP02.phx.gbl...
> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a
> bad one again and again and again. Some times the conditions are such or
> the data is such that the optimizer thinks it has the right plan but ends
> up with a bad one for your conditions. By changing the query slightly ( an
> IN to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a
> different and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
> "Nigel" <Nigel@.discussions.microsoft.com> wrote in message
> news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
>
