October 2, 2009 at 8:30 am
We’ve been experiencing issues on one of our shared SQL boxes for the last two weeks. I’ve been tracking down individual problems, but am thinking there is a simple, underlying issue. I’m posting this in hopes of getting ideas from others.
It is a SQL Server 2000, SP4 w/MS09-004 patch (8.00.2055) Enterprise on a Windows 2003 SP2 cluster. 4GB RAM, 4 Processors. There are 40 user databases, most of them are small and have little activity. Only 5 are larger than 5GB and the largest is only 8GB.
The disks are RAID5 on a MSA1000 shared array. There are three data drives. All but two of the databases are using the D: drive for the data files and the E: drive for the log files. Two databases are using the F: drive for the data and log files.
For the last two weeks I have seen the number of deadlocks and disk queue length increase drastically on all disks, even though some of the databases are on different disks. There has been a slight increase in compiles per second. Lock waits have increased.
Processor time, memory usage, cache hit ratio, network utilization, connections, transactions per second have all remained the same.
There have been NO configuration changes, software installed, changes to the network, or new databases added. The performance issues are affecting all databases regardless of which disk set they are on.
When troubleshooting some of the deadlocks, I found waits on tables that only have 1,000 records! It made no sense.
Altering the applications or queries is not an option since most of the databases use third-party apps. Besides, they haven’t changed.
I am not thrilled about the idea of failing the cluster over to the other node since this will just mask the issue. I’m more interesting in know what is causing the issue.
My working theory is a memory issue that is causing more disk usage. My Page Life Expectancy is averaging at 22 – 25, but drops to zero frequently (about twice a minute). However, there is no indication of problems or changes within the memory statistics.
OK – anyone every experience this or have any ideas?
October 2, 2009 at 8:42 am
I have seen this very problem on an MSA1000... absolutely appaling I/O performance (huge queues, 1500ms per I/O) and a machine that just responded like something out of the stone age.
I even saw deadlocks that patently weren't deadlocks when you looked at the actual locks.
The solution... eventually, one of the MSA1000 controllers failed. HP came in and swapped it out. The difference in performance after that was like driving a Ferrari after pedalling uphill on a push bike.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply