Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2008-06-13
Medium Priority
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

Question by:chrisbrns
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
LVL 70

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).

Author Comment

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


Author Comment

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

[boot loader]
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Standard" /fastdetect /NoExecute=OptOut /3
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 70

Accepted Solution

Scott Pletcher earned 1000 total points
ID: 21780444
EXEC sp_MSforeachtable '

2) You would need a Windows person to do this.

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'max degree of parallelism', 1
EXEC sp_configure 'show advanced options', 0

Author Comment

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

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...
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) ?

Author Comment

ID: 21786149

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!
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 1000 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

LVL 10

Expert Comment

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  :)
LVL 70

Expert Comment

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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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.

636 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