We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
COURSE of the MONTH
8 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Moved to new server, Running SQL 2000, Database is running SLOW! Please HELP!
Posted on 2008-06-13
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
3- +1
13 Comments
Active today
1) Update all statisticson all tables. This should always be done after a move / upgrade.
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).
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).
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
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
Active today
1)
EXEC sp_MSforeachtable '
UPDATE STATISTICS ?
'
2) You would need a Windows person to do this.
4)
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'max degree of parallelism', 1
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
EXEC sp_MSforeachtable '
UPDATE STATISTICS ?
'
2) You would need a Windows person to do this.
4)
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'max degree of parallelism', 1
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
Active 1 day ago
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...
Active 1 day ago
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) ?
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!
Active 1 day ago
OK, would move Virtual Memory to the system disk and leave it as "let windows manage" - not much point allocating 100gb, windows will probably only ever go to double at most. Need to get that off the same disks as the Database...
Raid 5 is not the best for SQL server. Has good read response but shot write response. Raid 1 or Raid10 is much better. But should be able to live with Raid5 - maybe reconsider as a future reference, or if there is any thoughts on disks again...
Have you given the database sufficient space to grow ? You have a physical allocation on disk and a used space within that, when the used space approaches the physical space, it will autogrow, and want to try to minimise that autogrow as much as possible.
How did you copy the old database ? Was it a SQL backup and then a restore ? Or, did you try to attach the database ?
Do you have maintenance plans in place ? Are you managing the transaction log properly if running in FULL recovery mode ?
Have you run recent backups with any optimize options set (as part of Maintenance wizard) ? If not, do it now.
Is there anything new or different within the database itself ?
We could go on asking twenty questions each time, or, suggest that you have a quick read of the following:
for some quick reference points, and a light read goto : http://www.sqlteam.com/article/sql-server-2000-performance-tuning-tools
then grab a cup of coffee, and have a read of : http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx -- some things in there are kind of academic rather than real life, so need to consider what you have learnt in the first reading...
and if you can there is an excellant book : http://www.microsoft.com/mspress/books/4939.aspx
Raid 5 is not the best for SQL server. Has good read response but shot write response. Raid 1 or Raid10 is much better. But should be able to live with Raid5 - maybe reconsider as a future reference, or if there is any thoughts on disks again...
Have you given the database sufficient space to grow ? You have a physical allocation on disk and a used space within that, when the used space approaches the physical space, it will autogrow, and want to try to minimise that autogrow as much as possible.
How did you copy the old database ? Was it a SQL backup and then a restore ? Or, did you try to attach the database ?
Do you have maintenance plans in place ? Are you managing the transaction log properly if running in FULL recovery mode ?
Have you run recent backups with any optimize options set (as part of Maintenance wizard) ? If not, do it now.
Is there anything new or different within the database itself ?
We could go on asking twenty questions each time, or, suggest that you have a quick read of the following:
for some quick reference points, and a light read goto : http://www.sqlteam.com/article/sql-server-2000-performance-tuning-tools
then grab a cup of coffee, and have a read of : http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx -- some things in there are kind of academic rather than real life, so need to consider what you have learnt in the first reading...
and if you can there is an excellant book : http://www.microsoft.com/mspress/books/4939.aspx
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 :)
Featured Post
Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, we’ll look at how to deploy ProxySQL.
- Databases
- Network Analysis
- Network Operations
- Network Security
- MySQL Server
- Network Architecture, Percona, *proxy servers, *Data Migration
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month8 days, 2 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.