Solved

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

Posted on 2008-06-13
13
214 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
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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
 
LVL 69

Accepted Solution

by:
ScottPletcher 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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:ScottPletcher
ID: 22439637
I believe I should get some points for this q, say 100.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now