Had a db that worked fine on a dual PII-400 server with 2 gb RAM running NT
4 Server SP6 with SQL Server 7.0.
Bought a dual xeon 2.4 Ghz, with 2 gb RAM and installed Windows 2000 Server
SP4. Then installed SQL Server 7.0 and then applied SP4.
There's plenty of disk space, ie, over 40 Gb free and the database's mdf
file is 2 gb and its ldf file 271 Mb.
I moved the db using detach / attach and ran sp_updatestats to the db
afterwards.
Performance is generally OK (though the db is hardly being pushed) except
for when I try to execute a complicated query. On the old box, this query
would take up 85 - 95% of cpu for around 30 seconds. On the new box, the
same query takes up 100% cpu and goes on for minutes. I haven't actually
timed it, but it takes an unacceptable time.
I could rewrite the query - split it into two or something - but surely this
should be unnecessary on a more powerful box.
I'm fairly newby to sql server admin so may have missed something obvious.
The query is usually executed via odbc from an Access 2000 application, but
I've run it locally on the server to remove Access from the equation.
I've run the Profiler but to be honest it tells me little. Just shows the
sql transact statement starting but not finishing (I end up cancelling the
query before it completes because the system is live).This is a multi-part message in MIME format.
--=_NextPart_000_0D08_01C393E8.DA49A0E0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Using sp_updatestats isn't enough. You want to run a FULLSCAN on each =table:
sp_MSforeachtable 'update statistics ? with fullscan'
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:#WTPEhAlDHA.644@.TK2MSFTNGP11.phx.gbl...
This one is really bugging me and I'd really appreciate some pointers.
Had a db that worked fine on a dual PII-400 server with 2 gb RAM running =NT
4 Server SP6 with SQL Server 7.0.
Bought a dual xeon 2.4 Ghz, with 2 gb RAM and installed Windows 2000 =Server
SP4. Then installed SQL Server 7.0 and then applied SP4.
There's plenty of disk space, ie, over 40 Gb free and the database's mdf
file is 2 gb and its ldf file 271 Mb.
I moved the db using detach / attach and ran sp_updatestats to the db
afterwards.
Performance is generally OK (though the db is hardly being pushed) =except
for when I try to execute a complicated query. On the old box, this =query
would take up 85 - 95% of cpu for around 30 seconds. On the new box, =the
same query takes up 100% cpu and goes on for minutes. I haven't =actually
timed it, but it takes an unacceptable time.
I could rewrite the query - split it into two or something - but surely =this
should be unnecessary on a more powerful box.
I'm fairly newby to sql server admin so may have missed something =obvious.
The query is usually executed via odbc from an Access 2000 application, =but
I've run it locally on the server to remove Access from the equation.
I've run the Profiler but to be honest it tells me little. Just shows =the
sql transact statement starting but not finishing (I end up cancelling =the
query before it completes because the system is live).
--=_NextPart_000_0D08_01C393E8.DA49A0E0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Using sp_updatestats isn't =enough. You want to run a FULLSCAN on each table:
sp_MSforeachtable 'update =statistics ? with fullscan'
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Paul Welsh"
--=_NextPart_000_0D08_01C393E8.DA49A0E0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0035_01C39495.993D8690
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks for that, Tom. Just tried it. No joy.
I ran the offending query overnight and it took 50 minutes. Previously =it took 30 seconds.
Clearly, there's something fundamental that I've not done.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elXzkpAlDHA.1764@.tk2msftngp13.phx.gbl...
Using sp_updatestats isn't enough. You want to run a FULLSCAN on each =table:
sp_MSforeachtable 'update statistics ? with fullscan'
--=_NextPart_000_0035_01C39495.993D8690
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Thanks for that, Tom. Just tried =it. No joy.
I ran the offending query overnight and =it took 50 minutes. Previously it took 30 seconds.
Clearly, there's something fundamental =that I've not done.
"Tom Moreau"
Using sp_updatestats isn't =enough. You want to run a FULLSCAN on each table:
sp_MSforeachtable 'update =statistics ? with fullscan'
--=_NextPart_000_0035_01C39495.993D8690--|||This is a multi-part message in MIME format.
--=_NextPart_000_00C0_01C3948D.9B62D130
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
OK, re-reading it, I see that the OS and hardware have changed but not =SQL Server. I'd be interested in the query plan it generates, as well =as the statistics IO. Also, there may be a disk issue here. Have you =looked at avg disk queue length? What is your disk configuration?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Paul Welsh" <reply.to.group.please@.microsoft.com> wrote in message =news:Owo##xIlDHA.392@.TK2MSFTNGP11.phx.gbl...
Thanks for that, Tom. Just tried it. No joy.
I ran the offending query overnight and it took 50 minutes. Previously =it took 30 seconds.
Clearly, there's something fundamental that I've not done.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:elXzkpAlDHA.1764@.tk2msftngp13.phx.gbl...
Using sp_updatestats isn't enough. You want to run a FULLSCAN on each =table:
sp_MSforeachtable 'update statistics ? with fullscan'
--=_NextPart_000_00C0_01C3948D.9B62D130
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
OK, re-reading it, I see that the OS =and hardware have changed but not SQL Server. I'd be interested in the query =plan it generates, as well as the statistics IO. Also, there may be a disk =issue here. Have you looked at avg disk queue length? What is your =disk configuration?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Paul Welsh"
Thanks for that, Tom. Just tried =it. No joy.
I ran the offending query overnight and =it took 50 minutes. Previously it took 30 seconds.
Clearly, there's something fundamental =that I've not done.
"Tom Moreau"
Using sp_updatestats isn't =enough. You want to run a FULLSCAN on each table:
sp_MSforeachtable 'update =statistics ? with fullscan'
--=_NextPart_000_00C0_01C3948D.9B62D130--
No comments:
Post a Comment