chrisbrns
asked on
Moved to new server, Running SQL 2000, Database is running SLOW! Please HELP!
Hey experts,
SQL 2000 moved from a older server to a new server, both running Windows 2003 Standard. SQL 2000 and OS have been patched and updated. I copied all settings accross and moved our database over to new server. It is working, but very very slow. I have 75-80 users a day working in it and having very bad performance issues. New server is substantially faster and using more ram. What am I missing?
The database is EMR software, the EMR company is little help. They don't have a good answer and I am completely setup to thier standards and likings. I thought maybe hardware, but everything really looks good. Everything on the server is blazing fast.
From what I have gotten from the docs, the slowness occurs when moving from different fields in the software and modules. IE, going from Neurology to Ortho.
Old server was running Raid 5
New server the same
Old srv 4 p4 2.8ghz
New srv 2 quad core 3.0ghz xeons
Old srv 2gb ram
New srv 4gb ram
I dont get it, please help!!!!
Thank you
Chris
SQL 2000 moved from a older server to a new server, both running Windows 2003 Standard. SQL 2000 and OS have been patched and updated. I copied all settings accross and moved our database over to new server. It is working, but very very slow. I have 75-80 users a day working in it and having very bad performance issues. New server is substantially faster and using more ram. What am I missing?
The database is EMR software, the EMR company is little help. They don't have a good answer and I am completely setup to thier standards and likings. I thought maybe hardware, but everything really looks good. Everything on the server is blazing fast.
From what I have gotten from the docs, the slowness occurs when moving from different fields in the software and modules. IE, going from Neurology to Ortho.
Old server was running Raid 5
New server the same
Old srv 4 p4 2.8ghz
New srv 2 quad core 3.0ghz xeons
Old srv 2gb ram
New srv 4gb ram
I dont get it, please help!!!!
Thank you
Chris
ASKER
Thank you Scott,
I am not the strongest person at SQL. I am not sure how to update statistics on all tables. Is this something that needs to be done through command?
/3gb Not sure how to do this
I wanted raid 10, but we were not as fortunate due to budget
Max degree of parallelism - You mean using all CPUs? I do not see any configuration to this.
Can you please help with these? thank you
Chris
I am not the strongest person at SQL. I am not sure how to update statistics on all tables. Is this something that needs to be done through command?
/3gb Not sure how to do this
I wanted raid 10, but we were not as fortunate due to budget
Max degree of parallelism - You mean using all CPUs? I do not see any configuration to this.
Can you please help with these? thank you
Chris
ASKER
Would the /3gb be adding a /3 to the end of the OS line?
[boot loader]
timeout=30
default=multi(0)disk(0)rdi
[operating systems]
multi(0)disk(0)rdisk(0)par
[boot loader]
timeout=30
default=multi(0)disk(0)rdi
[operating systems]
multi(0)disk(0)rdisk(0)par
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will work on the commands tonight and follow up tomorrow.
Thank you
Chris
Thank you
Chris
hey Chris,
Depending on which versions of SQL 2000 and Server, the 3gb switch might not help - unless you are using enterprise, it is not going to use more than 2 gig anyway...
Can you describe your disk structure a little more ? Is it all just raid 5 - or do you have system disks and a seperate array for data etc... If you simply run up task manager - is memory kind of busy, or CPU, or does the machine look to be very under-utilised ? Have you played with Performance monitor before ? Is the server just for SQL, or does it have to share with other applications ?
When install SQL / setting up SQL, did you simply use "defaults", or did you change some of the configurations ? When loading the database, did you specifiy where to put it - or just used standard settings ? Did you specifiy sizes for your database - or is it just "autogrow" ? What recovery model do you use for your database ? Have you a maintenance plan to do the neceessary backups and re-index/ optimisation ?
Have you set up Virtual Memory so that Windows takes care of it, or have you configured your own settings ?
In short, there is a lot of things that can affect performance, and need to start looking at the different combinations to isolate where to start tweaking - Generally speaking, disk IO is the slowest part of a machine, and the way Virtual Memory, and physical file locations/size all pointing to the same location (ie the system disk) is the more common reason behind poor performance on what looks to be a reasonable machine...
Depending on which versions of SQL 2000 and Server, the 3gb switch might not help - unless you are using enterprise, it is not going to use more than 2 gig anyway...
Can you describe your disk structure a little more ? Is it all just raid 5 - or do you have system disks and a seperate array for data etc... If you simply run up task manager - is memory kind of busy, or CPU, or does the machine look to be very under-utilised ? Have you played with Performance monitor before ? Is the server just for SQL, or does it have to share with other applications ?
When install SQL / setting up SQL, did you simply use "defaults", or did you change some of the configurations ? When loading the database, did you specifiy where to put it - or just used standard settings ? Did you specifiy sizes for your database - or is it just "autogrow" ? What recovery model do you use for your database ? Have you a maintenance plan to do the neceessary backups and re-index/ optimisation ?
Have you set up Virtual Memory so that Windows takes care of it, or have you configured your own settings ?
In short, there is a lot of things that can affect performance, and need to start looking at the different combinations to isolate where to start tweaking - Generally speaking, disk IO is the slowest part of a machine, and the way Virtual Memory, and physical file locations/size all pointing to the same location (ie the system disk) is the more common reason behind poor performance on what looks to be a reasonable machine...
Just another thought, is the speed issue just manifesting itself in EMR ? Is it a client server architecture - does it use a rich client, or browser ? Is the performance related to networked traffic and the machine itselft works very quickly ? Is there possibly network contention with cards / switches / routers (however it is set up) ?
ASKER
Mark,
Great questions, all things I wasn't quite sure to explain.
You are right about the /3gb switch, we are only using SQL 2000 standard. Which if I remember only uses 2gb. Task manager shows SQL process taking 1.7gb of VM most of the time. Even on a busy day when all users are logged in, it will top out about 1.6-1.7gb. The software by design is non browser on client, uses full sql connections via software. Rather large install on all workstations. AKA you pull the network cable and the program crashes. I have many other EMR softwares I have played with where browser based and doesn't need a 100% connection at all times. Keep in mind nothing has changed but the server. Networking issues seem to not really be the issue...All GB network, doctors tablets have same slowness on wireless as running the software on the server.
I have (by the company's standards) setup a 100gb virtual memory swap parition that is from the raid 5.
As far as I can see it only uses 5-6gb of it.
I setup or new system with 2 drives on raid 1 for OS and a seperate raid 5 for sql data. I think thats what MS says is best practices.
Most of SQL settings are set to default, but thats what our EMR company has told me is best... but we all know that sometimes that isn't the best for every environment. Unfortunatly I don't have a ton of experience with SQL performance tuning. Not really sure where to look to see if some of those settings are set.
I was reading about database fragmentation... I talked with our SQL programer and he said he would try running some commands this weekend, but he said he didn't think that was the problem. I don't know though.
Thank you for the support!
Working on site as I write.
THank you!
Great questions, all things I wasn't quite sure to explain.
You are right about the /3gb switch, we are only using SQL 2000 standard. Which if I remember only uses 2gb. Task manager shows SQL process taking 1.7gb of VM most of the time. Even on a busy day when all users are logged in, it will top out about 1.6-1.7gb. The software by design is non browser on client, uses full sql connections via software. Rather large install on all workstations. AKA you pull the network cable and the program crashes. I have many other EMR softwares I have played with where browser based and doesn't need a 100% connection at all times. Keep in mind nothing has changed but the server. Networking issues seem to not really be the issue...All GB network, doctors tablets have same slowness on wireless as running the software on the server.
I have (by the company's standards) setup a 100gb virtual memory swap parition that is from the raid 5.
As far as I can see it only uses 5-6gb of it.
I setup or new system with 2 drives on raid 1 for OS and a seperate raid 5 for sql data. I think thats what MS says is best practices.
Most of SQL settings are set to default, but thats what our EMR company has told me is best... but we all know that sometimes that isn't the best for every environment. Unfortunatly I don't have a ton of experience with SQL performance tuning. Not really sure where to look to see if some of those settings are set.
I was reading about database fragmentation... I talked with our SQL programer and he said he would try running some commands this weekend, but he said he didn't think that was the problem. I don't know though.
Thank you for the support!
Working on site as I write.
THank you!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just as an aside ... you have upgraded the database compat level to 90 ??
You should do this BEFORE you update all the indexes and stats :)
You should do this BEFORE you update all the indexes and stats :)
I believe I should get some points for this q, say 100.
2) Add the /3GB to the boot-up file to give SQL more RAM (and Windows less).
3) RAID-5 is not ideal for performance, but it's a given here.
4) Make sure 'max degree of parellelism' is set to 1 (i.e., turned off).