Poor Performance after upgrading from 2000 to 2005

I have a problem that I hope someone will be able to provide some insite
into, or some tips to help me.
We recently upgraded databases on an SQL server 2000 to an SQL 2005 server.
The hardware on both machines is exactly the same.
The SQL 2000 machine, although it has 64 bit hardware, the 32bit editions of
windows and SQL2000 were installed on it. The server has 4 gigs of ram.
For the upgrade, we installed Windows 2003 server x64, and SQL Server 2005
x64 on the 2nd server. To move the databases from the 2000, to the 2005
server, the data and log files were copied over to the 2005 server,
preserving the same directory structure as the original server. The the
databases were then attached to the sql2005 server.
The first problem arose approx 2 hours after the upgrade. The computer ran
its self out of memory. I assumed this was due to the 64 bit being able to
address more memory than the 32 bit edition. I then set the "maximum server
memory" setting to 2944. After restarting the server, the memory use in the
machine now peaks at around 3.9 gigs.
However, the problem now is that after a few hours of use, users are
complaning that transactions that used to execute in a matter of seconds in
the old system are in some cases now taking upwards of a minute.
Does anyone have any ideas of what can be done to help bring the performance
back to its original levels? Remember that other than using Windows
32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
exactly the same. I have ordered an additional 4 gig RAM (to bring total to
8) to see if that helps, but in all honesty I am completly lost.
Thanks for any insite,
RyanTry updating statistics on all tables WITH FULLSCAN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:OrMi8gmGHHA.3616@.TK2MSFTNGP06.phx.gbl...
I have a problem that I hope someone will be able to provide some insite
into, or some tips to help me.
We recently upgraded databases on an SQL server 2000 to an SQL 2005 server.
The hardware on both machines is exactly the same.
The SQL 2000 machine, although it has 64 bit hardware, the 32bit editions of
windows and SQL2000 were installed on it. The server has 4 gigs of ram.
For the upgrade, we installed Windows 2003 server x64, and SQL Server 2005
x64 on the 2nd server. To move the databases from the 2000, to the 2005
server, the data and log files were copied over to the 2005 server,
preserving the same directory structure as the original server. The the
databases were then attached to the sql2005 server.
The first problem arose approx 2 hours after the upgrade. The computer ran
its self out of memory. I assumed this was due to the 64 bit being able to
address more memory than the 32 bit edition. I then set the "maximum server
memory" setting to 2944. After restarting the server, the memory use in the
machine now peaks at around 3.9 gigs.
However, the problem now is that after a few hours of use, users are
complaning that transactions that used to execute in a matter of seconds in
the old system are in some cases now taking upwards of a minute.
Does anyone have any ideas of what can be done to help bring the performance
back to its original levels? Remember that other than using Windows
32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
exactly the same. I have ordered an additional 4 gig RAM (to bring total to
8) to see if that helps, but in all honesty I am completly lost.
Thanks for any insite,
Ryan|||Hello Ryan,
There could be many reasons for this but the most common is that the
statistics often need to be recomputed when a database is upgraded. Try
rebuilding all the statistics and see if that makes a difference.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||I will do that, because it certainly can't hurt. However, that senario does
not explain why when the service is first started, everything runs normally,
but the slowdown occurs slowly over the following couple of hours, (or could
it)? It seems I have to re-start the server service every couple of hours in
order to make the system usable.
-Ryan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:iV7IXEpGHHA.2488@.TK2MSFTNGHUB02.phx.gbl...
> Hello Ryan,
> There could be many reasons for this but the most common is that the
> statistics often need to be recomputed when a database is upgraded. Try
> rebuilding all the statistics and see if that makes a difference.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||On 08.12.2006 08:34, Ryan wrote:
> I will do that, because it certainly can't hurt. However, that senario doe
s
> not explain why when the service is first started, everything runs normall
y,
> but the slowdown occurs slowly over the following couple of hours, (or cou
ld
> it)? It seems I have to re-start the server service every couple of hours
in
> order to make the system usable.
Could be paging. Make sure your SQL Server memory settings do not grant
more mem than physically available minus some space for operating system
and other programs.
robert|||On Thu, 7 Dec 2006 19:28:57 -0600, "Ryan" <iQDevelopers@.nospam.nospam>
wrote:
>Does anyone have any ideas of what can be done to help bring the performanc
e
>back to its original levels? Remember that other than using Windows
>32bit/SQL 2000 32bit to Windows 64Bit/Sql2005 64bit everthing else is
>exactly the same. I have ordered an additional 4 gig RAM (to bring total to
>8) to see if that helps, but in all honesty I am completly lost.
>Thanks for any insite,
Sounds like the working set is expanding, can you verify that with
task manager or perfmon?
Frankly, sounds like a major bug, SQLServer or maybe your app (?) on
the same box has a memory leak?
J.|||Indeed updat statistics, rebuild youre indexes.
After upgarde , SQL2005 may need to recompile some storedprocedures(new
queryplan).
This might cause a delays.
Just my 2 cents.|||Last night I ran the sp_updatestats stored procedure on some of the busier
databases on that machine. (I plan do do the rest of them tonite). I also
moved approx half the databases to another server (same configuration). As
of now, things seem to be working ok (so far. Its been running under load
for about 6 hours now). I am not sure if it is due to the updated
statistics, or the fact that each machine has only half the load as the
original problem machine.
Once I get the additional ram into the original server, and increase the
setting in SQL for max memory use, I'll start miagrating them back to see
if the problem re-occurs. Thanks for all the responses. If anyone has any
other ideas of things I can look into as well, please let me know.
Thanks,
-Ryan|||In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats didn't
really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that helped
a lot.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Ryan" <iQDevelopers@.nospam.nospam> wrote in message
news:O%23Im9CxGHHA.2456@.TK2MSFTNGP06.phx.gbl...
Last night I ran the sp_updatestats stored procedure on some of the busier
databases on that machine. (I plan do do the rest of them tonite). I also
moved approx half the databases to another server (same configuration). As
of now, things seem to be working ok (so far. Its been running under load
for about 6 hours now). I am not sure if it is due to the updated
statistics, or the fact that each machine has only half the load as the
original problem machine.
Once I get the additional ram into the original server, and increase the
setting in SQL for max memory use, I'll start miagrating them back to see
if the problem re-occurs. Thanks for all the responses. If anyone has any
other ideas of things I can look into as well, please let me know.
Thanks,
-Ryan|||Ok. I'll do that. Thanks for the tip.
Do you know if there is any way to do it on all tables at once? I ended up
writing a script that uses a cursor to loop all the tables. Is that the best
way to do it?
Thanks,
-Ryan
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:Ow3ADB0GHHA.3468@.TK2MSFTNGP04.phx.gbl...
> In my migration from SQL 7.0 to SQL 2000,I found that sp_updatestats
> didn't
> really do the job. When I ran UPDATE STATISTICS WITH FULLSCAN, that
> helped
> a lot.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada