October 25, 2005 at 10:38 am
We plan to replace our current Server which is extremely slow. We think it has something to do with the fact that we are using RAID5 with only 3 hard drives. The bad thing is SQL server, the data base files and the log files are all on one logical drive. My boss wants to stop using RAID 5 and put the OS on one drive, the SQL server and database files on another drive and the log files on the 3rd drive. I told him we should not get rid of RAID 5 due to fault tolerance. He just wants to increase the speed as quickly as possible. He wants to get rid of RAID 5 and doesn't care if we don't have the fault tolerance. As long as we have a backup from the previous night he is fine. 🙁 I thought RAID 5 increases speed. The CPU stays at 80% or higher. I have run profiler and the slowest query takes 4 seconds, which isn't that slow to us. I have rebuilt indexes and update statistics and it's still slow. Now he just wants to build a whole new server because we think it's the server and not the data.
I need specs for building a new server. I would like to use 2 hard drives to mirror the log files, have a RAID5 for the database files and use a 6th for hot swap in case one of the mirror or RAID 5 drives goes down. What size or kind of hardrives, what speed processor. What kind of controllers...everything. Anything to increase the speed. 🙁
There is only one database in use on the server and it is a little over 6G. We have people using it all over the world at any given time. At any given time there are about 80 connections. It is a web based application. People use it to input and retrieve data and for reports.
October 25, 2005 at 11:15 am
Somewhere I have seen that the best setup is:
System databases (except TEMPDB) on one drive
TempDB on it's own RAID 1 (two drives)
UserDBs on Raid5 with Log files on RAID1
Reasoning: TEMPDB and logs get heavy Writes. UserDbs get lots of Reads.
-SQLBill
October 25, 2005 at 11:21 am
What metrics are you basing this decision on? CPU utilization? Disk Queuing? Why are you targeting the disk sub-system as the bottleneck?
You mentioned 80% CPU utilization, was that SQL Server using all of it? If not, what else is running on the box? If so, did you notice I/O was the cause?
What is the specs on the host you are currently running?
October 25, 2005 at 11:55 am
When the CPU is at 80% it's SQL server using it. There is nothing else running on the box.
Disk quwuing goes up and down but never stays up for long. It ma jump up to 100, then 50, then 20.
Pages/sec stays at 0.
My boss is targeting the disk as the problem because the database files and log files are on the same logical file. But even is we seperate them logically it will not make a difference because it will still be using the same hard drive physically.
How can I tell if I/O is the cause?
It's windows 2003 with SQL server 2000 enterprise edition. HP+Compaq. Server type is DL380 G4. The data abse has grown from 4.3 to 6.2 GB in 4 months.
October 25, 2005 at 2:12 pm
Watch Avg. Disk Queue Length in perfmon! How many SCSI controllers did you purchase with it 1 or 2? Does it go over 1 or 2 and stay over 1 or 2 during low and/or peak usage (depends on how many controllers you have)? What is the avg value? Also did you go with 1 CPU or 2 when you bought the server? Did you enable hyper threading? If you have one CPU and you look at task manger and you see two cpu windows you have hyper threading enabled!
October 25, 2005 at 11:15 pm
I would recommend using the instructions at http://www.sql-server-performance.com/articles_audit.asp to get a good idea of what is really going on with your database server before changing anything or setting your course. Nothing like a hardware upgrade/reorg that doesn't accomplish anything to make your day. Spending the time to figure out where the performance issues are really coming from prior to an upgrade is always a good idea - if it turns out that the only thing you need to do to make the server scream is build a couple of indexes you're a Hero, opposite being that you go out and buy a bunch of new hardware, etc. and the performance still sucks because all of your queries are table scans.
Joe
P.S. A HP/Compaq DL380 is not a trivial server (nor is it a real beast, just a good workhorse), I've run much bigger databases than 6GB on similar machines - check and see (1) how many processors are installed, (2) amount of memory, (3) check and make sure that your machine is running a hardware RAID solution - you can get a DL380 configured with all of the internal drives running off of the standard SCSI channel on the MB then software RAID the hard drives which sucks.
October 26, 2005 at 11:28 am
I agree with Joe, "do your performance homework first !!!". We are an HP (Compaq) shop. I've got 25+ DL380's and DL580's. These machines are not 'wimpy'. Since it's a G4, you've probably got Xeon CPUs. 2 physical shows up in perfmon as 4 CPUs. Have you enabled hyper-threading in ther BIOS ? Also, how much RAM do you have ? Hogh CPU usage could be the system 'thashing' about ... I'd suggest 4 Gb of RAM and configure SQL Server min/max memory for 2048 Mb giving 2 Gb for the OS. But Joe's advice is best ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 27, 2005 at 2:13 pm
Hi guys,
well I did some research and this is what I have:
1 SCSI controller in use
2 CPUs (In task manager, 4 CPUs are shown for the history)
4 Gig of Memory
Hardware Raid.
Memory dynamically configured
Now I ran performon yesterday for 10 minutes during peak time and these is the figures I got.
avg pages/sec .101
avg Available bytes 1535537152
avg % Disk Time 24.864
avg disk queue .249
avg % processor time 87.997
avg Processor Queue Length 2
avg Buffer cache hit ratio 99.86
avg user connections 41
Today I ran it around the same time for 10 minutes and these are the results I get
avg pages/sec 77.764
avg Available bytes 1521085809
avg % Disk Time 56.810
avg disk queue .568
avg % processor time 96
avg Processor Queue Length 2
avg Buffer cache hit ratio 99.862
avg user connections 50
Memory usuage at this time was 1,732,936K
Physical memory 3369524K
Avg Memory 1483782K
System Cache 1593544K
Kernal Mem 70116
Paged 56548
Non paged 13568
Also found out that the server is restarted every Tuesday and Thursday. Indexes are rebuilt every other night and stats are updated every day.
CAn anyone please tell me why my CPU usuage is so high. Also one day the pages/sec is very low and then very high.
October 27, 2005 at 2:28 pm
Did you get a SQL Trace at the same time? CPU usage, etc. are directly related to queries, etc.
Joe
October 27, 2005 at 2:39 pm
You can use the sql profiler to track down cpu-intensive queries.
What is the number of stored procedure recompilations/sec?
October 27, 2005 at 2:50 pm
You can thow out the idea of a disk problem:
avg disk queue .568
October 27, 2005 at 3:28 pm
Just some additional information on what the threshhold of a disk throughput problem should be.
It's usually when the avg disk queue is greater than 2 * number of disks in your array (or arrays).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 27, 2005 at 3:32 pm
A little more information on the SQL Trace - run the trace for a while, then save the results to a table so that you can get a good look at what's going on. From the results table you should be able to sort the activity by time taken, disk I/O and CPU time taken, etc. - once you throw out logouts, etc. you should be able to see those queries/stored procedures, etc. that are taking the longest from which you can start working with the problem queries/procedure via query analyzer to determine why they're taking so long and if anything can be done about it.
Joe
October 27, 2005 at 3:50 pm
Does the application use stored procedures or in line SQL?
I would also incluse in your maintenance to run sp_recompile on all user tables, which will force all associated procedures and triggers to recompile they are ran next taking advantage of fresh statistics.
October 27, 2005 at 4:07 pm
Regarding your current server and performance statistics
Has anyone looked at the network ? Try capturing #packets in/out and packet errors.
To check if the application is chatty, capture SQL Server:SQL Statistics, batch requests/sec.
To check if the application is connecting/disconnecting for each batch, capture SQL Server: General Statistics, logins/sec.
Ideally, logins/second should be zero.
Regarding the disk drives, you can capture I/O by database file by running
SELECT sysdatabases.name
, virtualfilestats.*
FROM :: fn_virtualfilestats(default , default ) as virtualfilestats
join master.dbo.sysdatabases sysdatabases
on sysdatabases.dbid = virtualfilestats.dbid
order by numberwrites desc
Last thought:
Are the database data or log files growing during busy times ? Make that the database files have enough free space so that file growth does not occur during busy times.
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply