Perfmon points to disk I/O

[mebenz]

We were having performance issues in all our applications and in order to try and determine if it was an application, the server, the cluster setup, sql setup, etc, we purchased a new, high powered server to test the application that was showing the slowest performance.

The new server has 2x Quad Core Intel Xeon Processors, 4Gb RAM (3 allocated to SQL), Dual NC373i Multifunction Gigabit, Eight 72GB 15K hard drives (2 RAID 1 for OS, 2 RAID 1 for sql logs and 3 RAID 5 for sql data) on Windows 2003 Enterprise. We are running SQL 2000 Enterprise with SP#4.

We ran a test for an hour with all users actively using the application. I ran CPU Object: % Total Processor Time, Memory Object: Pages/Sec counter, PhysicalDisk Object: Avg. Disk Queue Length, PhysicalDisk Object: Avg. Disk Sec/Transfer and Buffer Cache Hit Ratio.

The memory (avg 0.729, max 66.137 only 3 spikes), processor (avg 3.19, max 13) and bugger cache (avg 99.848, max 99.884) are all performing well.

However, the Avg disk queue for the data drive was averaging 5.838, with max of 166.110 (over 20 spikes hitting over 100 on the graph) and the avg disk sec/transfer averaging 0.103, max of 0.757 and graphs are identical to disk queue.

From what I've read, there is something wrong. Even though the application was performing well during this time, the disks are either not performing well, or the application is somehow not performing well?

We are going to be moving our other applications to this server, so if we already have red flags on the disks, it's some concern to me.

Since I am not a hardware gal, nor did I develop the application that is causing these spikes, is there anything anyone can suggest?

 

[hmckillop]

A great tool for assisting you is SQLIO, if you run this on each of your mounted volumes or drives it will give you a picture of how well the discs are performing.

I have had extensive experience in SQL performance, from SAN configuration, lightpulse HBA cards, OS configuration to SQL configuration.

The best thing in terms of your application is to run a full profiler trace, a great tool for pointing out issues in your application is a tool like SQL Co-efficient, not only does it give you a great output, but looks well too.

In addition to this I recommend a performance audit of all tasks, have a look at this link
[URL unfurl="true"]http://www.sql-server-performance.com/articles/per/performance_audit_p1.aspx
[/url]

In my case I knew it was likely to be the SAN disc subsystem as SQLIO was showing a throughput of 5MB/sec but the real key was all requests had a latency of more than 100ms which highlighted the issue. We have since had a new disc subsystem and disc configuration giving us a higher throughput and lower latency, but this is due to change again.

Hope this helps.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[mrdenny]

I posted a responce on the other thread.

What disk is showing the high queuing, and what's on that disk?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[mebenz]

The queuing is on our sql data disk, which only holds the mdfs for master and our application's mdf. NOthing else.

 

[mrdenny]

Is your database mostly read or mostly write?

How large is the database, and how large do you expect it to grow?

By my count you have 7 disks in use. Are you using the 8th for a hot spare?

Setup SQL Profiler and make sure that you aren't doing any table scans or index scans that you shouldn't be. These can put a great deal of stress on the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog