Solved

Moved to new server, Running SQL 2000, Database is running SLOW! Please HELP!

Posted on 2008-06-13
13
220 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:chrisbrns
[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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21780120
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).
0
 
LVL 1

Author Comment

by:chrisbrns
ID: 21780271
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
0
 
LVL 1

Author Comment

by:chrisbrns
ID: 21780428
Would the /3gb be adding a /3 to the end of the OS line?

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Standard" /fastdetect /NoExecute=OptOut /3
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 21780444
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
0
 
LVL 1

Author Comment

by:chrisbrns
ID: 21780570
I will work on the commands tonight and follow up tomorrow.
Thank you

Chris
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21781253
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...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21781292
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) ?
0
 
LVL 1

Author Comment

by:chrisbrns
ID: 21786149
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!
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 21786256
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





0
 
LVL 10

Expert Comment

by:TAB8
ID: 21790607
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  :)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 22439637
I believe I should get some points for this q, say 100.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question