Queries run slower in 2000 than 7

I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of r
am. I moved the database from a SQL Server 7 SP2 box with only 2 processors
and 1 gig of RAM. I inherited a monster report that runs in 30 seconds or
less on the sql 7 box, but
takes a minute and a half on the new box. This report uses a tremendous amo
unt of temp tables and dynamic sql (as I said I inherited it). By using sp_
executesql instead of EXEC for the dynamic sql, i was able to get the report
to run in 55 seconds. How
ever, I am confused as to why it would run so much slower on a much larder b
ox with 2000, especially considering i was the only one on the 2000 box, and
the SQL 7 box has 400 users on it.
Any ideas?Did you remember to update statistics on all tables, preferably with the
FULLSCAN option?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:6AA5B19E-7050-4FF7-914D-B0772F6E6ADC@.microsoft.com...
I created a new SQL Server 2000 SP2 on a 4 processor machine wiht 2 gig of
ram. I moved the database from a SQL Server 7 SP2 box with only 2
processors and 1 gig of RAM. I inherited a monster report that runs in 30
seconds or less on the sql 7 box, but takes a minute and a half on the new
box. This report uses a tremendous amount of temp tables and dynamic sql
(as I said I inherited it). By using sp_executesql instead of EXEC for the
dynamic sql, i was able to get the report to run in 55 seconds. However, I
am confused as to why it would run so much slower on a much larder box with
2000, especially considering i was the only one on the 2000 box, and the SQL
7 box has 400 users on it.
Any ideas?|||I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took a
lmost 12 minutes to run and actually rhe query came back at 57 seconds inste
ad of 55. It is the same exact database file as sql 7. i am so confused.
i am supposed to be releasi
ng this server to productiona t the end of the week, but my queries are runn
ing slower. the whole justification for this purchase was to make things fa
ster and now i have no explanation why things are slower. any toehr ideas a
re greatly appreciated.|||Could you please list your hardware as well as where you placed your data
files on each server?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:04C40E3B-AFD1-4862-8957-CA29F358B3FE@.microsoft.com...
I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took
almost 12 minutes to run and actually rhe query came back at 57 seconds
instead of 55. It is the same exact database file as sql 7. i am so
confused. i am supposed to be releasing this server to productiona t the
end of the week, but my queries are running slower. the whole justification
for this purchase was to make things faster and now i have no explanation
why things are slower. any toehr ideas are greatly appreciated.|||Can you list your hardware and DB configuration? Especially the disk config.
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:

> I just ran sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'. it took almost
12 minutes to run and actually rhe query came back at 57 seconds instead of 55. It
is the same exact database file as sql 7. i am so confused. i am supposed to be re
lea
sing this server to productiona t the end of the week, but my queries are running slower. t
he whole justification for this purchase was to make things faster and now i have no explana
tion why things are slower. any toehr ideas are greatly appreciated.
>|||Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||Outside of using RAID0+1, instead of RAID 5, I'd expect this to be OK. What
did you have for hardware for your SQL 7 box? Also, for your particular
query, have you tried running off parallelism:
SELECT
*
FROM
MyTable
OPTION (MAXDOP 1)
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:BA36F91D-17C8-47A3-9E7F-C8DCBCC5F620@.microsoft.com...
Here’s the specifications for this server.
Compaq DL580 G2
4x 2800 MHz processors
2GB Ram – SQL is configured to dynamically use all of this except the last
128MB which is saved for the OS.
4GB Page file on C: drive
C: - 34 GB Local Mirror (OS and SQL binn files only)
E: - 34 GB Local Mirror (SQL Log Files Only)
F: - 200 GB SAN RAID 5 (SQL Data Files Only)
I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||What is your SQL 7 box config.?
Eric Li
SQL DBA
MCDBA
Tammy Moisan wrote:
> Here’s the specifications for this server.
> Compaq DL580 G2
> 4x 2800 MHz processors
> 2GB Ram – SQL is configured to dynamically use all of this except the la
st 128MB which is saved for the OS.
> 4GB Page file on C: drive
> C: - 34 GB Local Mirror (OS and SQL binn files only)
> E: - 34 GB Local Mirror (SQL Log Files Only)
> F: - 200 GB SAN RAID 5 (SQL Data Files Only)
> I: - 200 GB SAN RAID 5 (SQL Data Files or Application data staging area)
> J: - 100 GB SAN RAID 5 (SQL Data Files or Application data staging area)|||I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box|||I just wanted to confirm that fact. Have you tried turning off parallelism
on the disaffected query?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tammy Moisan" <anonymous@.discussions.microsoft.com> wrote in message
news:A4598E2A-D769-41FB-8FDC-E3FDD1459B2C@.microsoft.com...
I do nto have so much info about this box, except that it is much smaller
2 1.2 ghz Processors
1 GIG Ram
Data files on d:\ with 500MB free
Log FIles on e:swap file on f:\ 250MB
I do not see why this matters, as it is a smaller box