Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tuj
Starting Member
1 Post |
 Posted - 2007-08-10 : 12:23:35
|
| ***cross-posted to MSDN SQL Server forum***Hi All. Hopefully I posted this to the right section.I would like help identifying if I have a disk performance issue or not. First the background: we have a j2ee application using the MS SQL 2005 JDBC driver and Hibernate on 4 application servers, and an active-passive SQL Server 2005 cluster. All of the servers reside in the same physical rack and switch.Our application is typically bounded by CPU on the app server, or throughput from the database. Several months ago we were using SQL 2000 and would often max out the CPU on the database server before anything else, but often the database could keep up and we would max out the app servers CPU. Now we have 2005 on a much more powerful machine and more app servers, but we seem to be running up against a problem with throughput from the database.The issue is not CPU. The total cpu average on the database server, as monitored in perfmon on 30 second intervals, stays consistently below 40%. The app servers stay well below 30%. But what concerns me is the Average Disk Queue Read Length on the database server, particularly for our E: drive. On this db server, the transaction log, the system and temp dbs, and our application's database are all on separate EMC SAN shares, connected via fibre chanel. The E drive houses the app data and is a 15-way meta device (fifteen 10GB logical devices striped at 960k for a 150GB device) in a RAID-S configuration, EMC Symmetrix array located in the same rack. The database is roughly 30GB.I have read various articles online describing how to interpret the Average Disk Queue Read Length performance counter with regards to SQL Server. Some have said this should not exceed the number of physical spindles * 2. We are seeing values of 32 consistently, averaging over 60 during peak processing hours, and spiking to well over 100 on a scale of 1.0. (3-second sample interval).So since our application servers seem to be waiting on their database calls (a lot of inserts with frequent, but small-resultset selects) and do not show I/O issues either with their local storage, memory, or network interface. The database server again has no CPU, network, or memory issues. I should add the the Average Disk Queue Write Length counter does not have any issues; its always below 1 (on a 1.0 scale). The EMC array has both read and write caching. The indexes of the application database are rebuilt weekly and defragmented every day, with stats rebuilt after the defrag.So how can I further determine where my performance problem lies? All thoughts appreciated! Thanks!-tuj |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-11 : 01:05:28
|
| It sounds high to me. How about disk channel throughput on the SAN? |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-08-11 : 01:27:37
|
| I am not sure I saw anything in your post to indicate what problem you have with database performance.What is the database issue you are seeing? Slow response to application requests? Slow inserts? Slow lookups?CODO ERGO SUM |
|
|
|
|
|
|
|
|
Do I have a disk performance problem?