Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
 Posted - 2008-12-01 : 22:30:48
|
| We recently migrated from SQL 2000 to SQL 2000 but on a better server. The old 2000 had SP3 and the new one has SP4. A lot of queries have started to run super slow on the new server (which happens to be much more powerful than the new one)The only additonal thing on the new server is SQL 2005 on the same server, but there are not many DBs that are hosted under 2005 and they are not very busy either.The queries that would usually take about 3 hours to run on the old server now take over 24. These are mostly the ones which have cursors. Does anyone have any suggestions on what we could do to change or fix this? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-01 : 22:41:37
|
| Definitely, If you are having 2 instances in same server then they will consume memory and resources. You need to set min and max memory settings to allocate how much memory each instance needs.3 hours is really bad for queries. You need to tune it with proper indexes by analyzing execution plan. You need to change cursors to set-based solutions and rebuild all indexes if fragmented during migration. |
 |
|
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
Posted - 2008-12-01 : 23:18:01
|
| Thanks for the response. I agree with set based solutions and fixing those as I inherited them. In the meantime, I just have to live with this during the trasition. How do I set the memory allocation for each server? Also, is there an easy way to reset all indexes on a global level for a server? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-01 : 23:31:59
|
How do I set the memory allocation for each server instances?Depends on edition of SQL Server and Total Server Memory. Also, is there an easy way to reset all indexes on a global level for a server?You can rebuild all indexes . Search for it,there are so many posts regarding this. |
|
|
|
|
|
|
SQL 2000 Migration problem