SQL Server Performance (slowdown)
From: Paker (Paker_at_discussions.microsoft.com)
Date: 02/16/05
- Next message: Kalen Delaney: "Re: IX lock on a table"
- Previous message: S: "sql7 with win 2003 dc ASAP"
- Next in thread: David Gugick: "Re: SQL Server Performance (slowdown)"
- Reply: David Gugick: "Re: SQL Server Performance (slowdown)"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 16 Feb 2005 14:07:03 -0800
Hi,
Please help. I have a strange performace problem.
Hardware: HP Server 4 CPU's 8 GB memory .
Sql Server installed on disk C: (RAID 5-5disks)
OLTP database on disk D: (RAID 10- 10disks)
Archive database on disk E : (RAID 5-5 disks)
Operating System Windows 2000 Advanced server SP2+ /PAE enable,
SQL server 2000 Enterprise Sp3
Allocated 7GB memory with AWE support
Software installed HP OpenView and CA BrightStore High availability
The average value of user connections is 300~350
Actually OLTP database collects some sort of information for about 50
users simultaneously for 1-5 days by inserting 1 row per minute approx for
every user.
I have one havy load table -average count 100,000-3,000,000 rows per user,
the other tables much smaller.
Every day runs the job that moves the information about deleted users to the
archive database.The slowness began a month ago when amount of active users
has grown up. During last month the OLTP database has grown from 7 GB (we
have a lot of sites with the same size and they do not have any problem) to
30GB
The havy load table has 5 indexes including clustered ,based on userid
(indexid=1). Every index has a high level of defragmentation approximate 12%.
On the OLTP database the optimal index size is 2.5GB vs. 15.6GB actual size,
on the archive database 4.6GB vs. 33GB. (I can not rebuld indexes now, next
down time only in april)
The problem:
Deleting the inactive user information from the havy load table causes to
slowdown when inserting the information for a new users. Average duration for
every insert from 0.5ms to 20 sec
I tried diffrent kinds of deletes (set rowcount 500-100 -> delete ... where
userid=.. waitfor delay '...'; delete with cursor). So delete job does not
run, the OLTP database grows up every day.
I gathered a lot of information from the diffrent counters:
CPU works fine; Memory - Buffer cache hit ratio 99.9; page life expectancy
about 8000;
Average disk queue length 1.2-1.5. Sometimes disk time % returns more than
100% but it's not really usefull for RAID's. Amount of Reads/sec~ average
200-500 sometimes a short peaks till 1000, Writes/sec ~ 300
A used the formula for I/o calculations 500+2*300/10 disks=110 (on the peak
average values). The normal value up to 120 I/O per second
The percent of full table scans is low.
Amount of transactions/sec is from 500 to 900 (peak hours)
Sometimes perfmon captures lock waits (ms) more than 200
Thanks in advance
- Next message: Kalen Delaney: "Re: IX lock on a table"
- Previous message: S: "sql7 with win 2003 dc ASAP"
- Next in thread: David Gugick: "Re: SQL Server Performance (slowdown)"
- Reply: David Gugick: "Re: SQL Server Performance (slowdown)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
