Performance decrease on new server

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).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" wrote in message news:#WTPEhAlDHA.644@.T=K2MSFTNGP11.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 =NT4 Server SP6 with SQL Server 7.0.Bought a dual xeon 2.4 Ghz, with =2 gb RAM and installed Windows 2000 ServerSP4. 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 mdffile is 2 gb and its ldf file 271 Mb.I =moved the db using detach / attach and ran sp_updatestats to the dbafterwards.Performance is generally OK (though the db is =hardly being pushed) exceptfor when I try to execute a complicated =query. On the old box, this querywould take up 85 - 95% of cpu for around 30 seconds. On the new box, thesame query takes up 100% cpu and =goes on for minutes. I haven't actuallytimed it, but it takes an =unacceptable time.I could rewrite the query - split it into two or something =- but surely thisshould 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, =butI'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 =thesql transact statement starting but not finishing (I end up cancelling =thequery before it completes because the system is =live).

---

--=_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" = 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--|||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" wrote in message news:Owo##xIlDHA.392@.T=K2MSFTNGP11.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" = 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--

Performance decrease after OS upgrade

We were running a 4 processor server with 8 GB RAM and 800GB hard disk
space on Windows 2000 server and SQL Server 2000.
Recently we have upgraded the server to 8 processor and added another
800GB. The OS was upgradded to Windows 2003 enterprise edition.
All the processor are 2.5GHz Xeon preocessors.

After upgradation the performance of the server has gone down from what
it was giving before the upgrade.

It seems that multiprocessing is not ocurring.

The model of the server is HP DL740

The OS is installed in a built in array(5i controller) of the server
and the SQL server is installed in a external array(6400 controller).

I will really aprecite if anyone can give any clue to improve the
performance.

Thanks in advance.

Taw.Really need more details... One thought, when you added the 800 GB of
disk space -- I assumed you added a new array? How was it configured?
i.e. Was the existing array named drive E and then the new array named
drive F? Or did you span it into one giant 1.6T array?|||Before the upgrade the OS(Windows200) and the SQLServer was both in the
external array. OS was in one logical drive (C:) and Sqlserver in
another logical drive (D:). The built in array was not used.

For upgrading the system we have added 2X36.4 GB harddisk in the built
in array and clean installed the OS(Windows 2003). Now there is one
1.6TB (14X146 GB) external array,RAID 5. The external array is the
logical drive D: and the OS(RAID1) is the logical drive C:|||Taw,

From the limited information provided, your biggest performance hit here is
the use of RAID 5. RAID1 is much faster, especially for the transaction
log. If we assume that the configuration is generally the same just more
processors and more spindles, I would look into pulling back your max degree
of parallelism.

Also is SQL completely installed on the external array? The default install
is to the C: drive and this may be where tempdb is located.

<tawfiq.choudhury@.grameenphone.com> wrote in message
news:1108442363.222496.139900@.g14g2000cwa.googlegr oups.com...
> Before the upgrade the OS(Windows200) and the SQLServer was both in the
> external array. OS was in one logical drive (C:) and Sqlserver in
> another logical drive (D:). The built in array was not used.
> For upgrading the system we have added 2X36.4 GB harddisk in the built
> in array and clean installed the OS(Windows 2003). Now there is one
> 1.6TB (14X146 GB) external array,RAID 5. The external array is the
> logical drive D: and the OS(RAID1) is the logical drive C:|||My only guess is that you have a "HP Modular Smart Array 30". I
remember buried somewhere in it's documentation, the optimal number of
disks per logical drive is 8. But the array is top-of-the-line and
having 14 disks in one logical drive shouldn't impact it that much.
You can try playing with the Parallellism Query Plan Threshold setting.
Otherwise, sorry, I have no other ideas.|||Yes SQL is installed completely in the external array.