| Author |
Topic |
|
nicolo
Starting Member
20 Posts |
 Posted - 2005-03-01 : 14:48:03
|
| I am currently running MS SQL 2000 on an IBM x205 xSeries Eserver model 848033X. There's only a single Intel Pentium 4 2.40GHz processor with 2GB (just added today) and 533Mhz FSB. There are 3 x 74GB SCSI disks. The first has the OS on a 12GB partition and the other two disks are in RAID-1 config.Many of the datafiles that go into this MSSQL2000 database are nothing more than scanned images - small TIFs...but they are many....thousands upon thousands. When the RAID-1 set ran out of room, I started putting these files onto the remaining portion of the first disk which is the OS disk.Naturally the files grew to up to 18GBs now on the OS disk. Upgrading up to 2GB RAM seemed to help a bit, however, as other people try to access the PC, the CPU would often spike up to almost 100% then down. Is there anyway to alleviate this problem? Is it simply because I have too many of these database files sitting on the first OS disk instead of the RAID-1 array? What can I do to tweak the system with what I've got? Is there anything I can do to optimize the database's performance like defragmenting it or indexing, etc? Thanks for any help |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-01 : 15:11:22
|
| There are a lot of things that can be done to improve the database's performance.DBCC DBREINDEXDBCC INDEXDEFRAGDatabase redesignFix the queryAdd indexes to support the queryUPDATE STATISTICSUpgrade the hardwareIt's hard for us to tell you which one is going to fix your problem though as you haven't posted the query or let us know what is causing the problem. To find out what is causing the problem, you'd have to run Performance Monitor and SQL Profiler to narrow it down.Tara |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-01 : 15:22:59
|
| You probably need to get a large RAID 5 array to put your data on, as well as more RAM. It sounds like your database is around 80-90GB right? You can't get very much of your 80GB database into 2GB's of RAM. Also, you need to monitor Average Disk Queue length in perfmon. I bet that it shows that you need more disks. If that number is above 2.0 for any period of time, you need more disks.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
nicolo
Starting Member
20 Posts |
Posted - 2005-03-01 : 16:27:22
|
| Yes the RAID 1 set contains about 56GB of data files and the new partition with the other newer data files is at 18GB right now. I ran the Performance Monitor and saw the Av Queue Length go up to .454 as max sometimes. This was while I was doing a simple search operation. Don't know if that is above 2 like you said. The scale was from 1-100. I currently have a new RAID 5 server on hand which I can move over those data files sitting on the OS disk. Maybe that will help a little?Thanks Tara can you tell me precisely how or what to look for in the SQL Profiler as I've never used that before. I also saw some things under the DB Maintenance plan regarding reindexing, updating stats, etc and was wondering if this might help? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-01 : 18:03:04
|
| When you say move data files, are you talking moving some of the SQL data files to that machine or some of your TIFF's. I thought that your TIFF's were inside of your SQL Database..454 isn't bad at all. The question is does it stay around there (like a flatline) or is it up and down a lot (like a heartbeat).Flatlining at .5 isnt too bad, but it might indicate bottlenecks elsewhere. The heartbeat style graph is what you want to see, small very short spikes with flat lines in between.Are you using hardware or software RAID? It sounds like something is spiking the CPU, thus limiting the disk queuing you are seeing.If you had a faster CPU, then you'd probably start to see that the disks are you bottleneck.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
nicolo
Starting Member
20 Posts |
Posted - 2005-03-02 : 08:59:07
|
| yes I have a new Dell server with a RAID 5 array and plenty of space. I was planning on moving the data files, the TIFs, from where they are sitting now to that server and have the SQL db link to the new server for those files. There was no flatlining, but just a standard heartbeat graph. What I did see that was almost flatlining was page faults. I checked the Task Manager as well and saw that the most page faults came from the vendor application running on the machine itself, along with ntvdm and sql server. I am using software RAID at this point on that particular box. The new Dell storage server has dedicated hardware RAID. Do you think moving those TIFs from the OS disk to the new Dell storage server would make a difference or should I continue looking into optimizing the db? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-02 : 12:14:28
|
| Software RAID!!!!BAD BAD BAD!That's exactly why your CPU is soo high and your disk queue isn't very high. I'd move everything you can to the Dell box with the hardware RAID. Put SQL Server on there and put everything on the RAID 5 array. If you have a separate RAID 1 array that doesn't contain the OS, you could put the transaction logs there.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
nicolo
Starting Member
20 Posts |
Posted - 2005-03-02 : 12:36:41
|
| gripes...yes the RAID-1 set are Windows 2000 dynamic disks. But I've recently started to notice the slowness. Migrating the entire thing over to the Dell would take an insane amount of time. There are two large data volumes right now that are accessed by the SQL DB. The first is on the RAID -1 set and that is locked for read only access right now. The new volume sits on the rest of the OS disk. I was planning on just moving the new volume files over the Dell server and accessing them through the network. Would that be enough or do I still have to migrate the ENTIRE thing including DB over to the RAID 5 DEll server? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-02 : 14:38:38
|
| If it were me, I'd make it a weekend project and migrate everything.Software RAID is a really really bad idea IMHO. You are just asking for performance problems (like you are seeing) and maybe even data corruption issues.You may be able to get away with moving either the TIFF's or the SQL database to the new machine. A few questions:How big is your SQL database in GB's?How big are the TIFF's in GB? Are the Tiff's stored on the file system or inside of SQL server? I'm not 100% sure from the posts above.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-02 : 15:19:42
|
| "insane amount of time"Why? Take Full Backup of the DB, restore to new box, once that is done (which WILL take quite an while!) mark the original box ReadOnly / SingleUser / etc. and take a Differential backups and restore that on the New Box. Then go live with the next box ... the differentail BACKUP/RESTORE should be quite modestKristen |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-02 : 15:30:52
|
| There's liek 80GB's of data to move from one server to the other. It's going to take quite awhile to do it, but the longer you wait the more data you'll have to move.That why I said, find a weekend to have a scheduled outtage and move the data. It's going to take many hours to do it methinks because you take a write hit when writing to a RAID 5 set. In the long run, you'll be MUUUUCH better off and have much better performance.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
nicolo
Starting Member
20 Posts |
Posted - 2005-03-02 : 17:38:16
|
| The database size? Well I don't know much about how to tell but the backup file is approx 2.5GB right now.Individual TIFs are usually in the 10K - 25K range. Data volume 1 sits on the Windows 2000 RAID 1 set and is 55GB while data volume 2 sits on a leftover partition from the OS disk and that's 18GB so far. The vendor application using the SQL backend, but the actual TIF files sit inside a hierarchy of files in these two volumes. The volumes can be built on any disk. I am really considering moving all the TIFs over to the new DELL server, then backing up and restoring the SQL DB onto the DELL. Course the other way is to stop using data volume 2 on the OS disk and start using it on the DELL server. |
|
|
|
|
CPU usage spikes