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
October 28, 2005 at 7:21 am
Okay, I'll take a look at these suggestions and get back to you.
Also, I ran the trace while while running perfom monitor and came up with 3 queries that took about 2 to 3 minutes to run. But there where about 10 of each query running at the same time. I ran the queries again this morning at around 7am when our CPU is only at about 15-20%. At this time each query took a short time to run. The longest one may have run for about 19secs. So I'm taking the more users....the longer it takes for this huge queries to run.
During my research I read that Pages/sex should be between 0 and 20. Why is it that one day it was at o and the next day it was at avg 86.
I did notice that the SQL server logs are enormous. I have to cycle them every day. Maybe twice a day. They're so huge that they just hang when I try to open them.
The other thing is the network admin and I view this server thru a VPN. Unfortunately he knows less than I do. 🙁
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply