March 21, 2008 at 12:47 am
As per the last post of mine for slow database perfoamance, we hae HP SAN (EVA 4000). Our main database servr is in blade server with mirrored i,e c drive. and d drive is made from EVA 4000 enclosure of 146gb *11 hdd which is in SAN.We have observed in profiler on the per second basis that cpu utilization is always 50-60 % and RAM is always available at 2gb out of 4 gb. We have observed that Read activity is always about 2 crore and write activity is about 15 to 20000 per querry. In profiler, disk time always being 300 - 3000 % and avg disk q lenth is 50- 60 per second in the continueue course of observation .But still the querry gets timedout. So how we can manage the db and improve the performance. We have 3 databases in SAN including the first one sharing the same 146gb * 11 hdd.
Also the database is stored in horzontal format in blocks in vraid because vraid horizontally stores the data on all hard disk & we can make raid 0 , raid 5 & raid 10.
So how we can go ahead with physical raid or Vraid. which one is best to use.
vrijesh
March 21, 2008 at 10:38 am
You need to be sure to have a good realationship with your sys admins. With virtual stuff, at the database level, you do not necessarily see all of the things that might be impacting your performance. For example, we had a SAN where we were sharing the underlying disk with another appliction, it would kick up a huge hit to the disk and we would have tremendous disk queues form on the sql server side. We saw nothing on sql server that should cause the disk queues that we were seeing. We went back to our sys admins and they were able to move the other application so that we were not negatively impacted. As we move towards virtual servers etc, we need to understand that the same things can happen at cpu and memory as well.
Mark
March 21, 2008 at 11:22 am
An average disk queue length of 50-60 tells me that you have an I/O bottleneck. The danger of using a SAN for DB storage is that the storage admins usually don't understand DB server's needs. You'll need to work with your storage admins to get your database files on their own physical disks/spindles.
There may be more for you to look at, but with a queue length that high, there's a good place to start. Your CPU usage is also a bit high. CPU spikes that high (or higher) are quite normal, but consistent usage of 50-60% is high. How busy would you consider your server?
March 22, 2008 at 9:00 pm
I tend to agree with John that you want to figure out what the CPU load is. If I had consistently 50-60%, I have a busy server and I'm keeping an eye on it, perhaps considering hardware upgrades. When you mention the server, is this SQL Standard or Enterprise and do you only have 2GB allocated to SQL? I'd be looking to at least add the /3GB switch and move to 2.5-2.8GB of RAM.
You don't want R5 on these servers and I'd be wary even with virtual raid. Get your own spindles for the database.
March 24, 2008 at 10:00 am
Hire an SAN-experienced sql server expert to give your systems a review. Way too many things can be mis/poorly configured in this setup!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 25, 2008 at 11:51 am
I had run into a similar experience about two years ago using the HP EVA SAN. it turned out that the SAN itself was the culprit, and that this was an early indicator of complete failure of the SAN. Within a month of seeing this kind of bottleneck we started getting errors where drives were disappearing spontaneously, requiring a reboot of the SAN (and subsequent reboot of 22 SQL Servers).
Our solution was to ditch the HP SAN and go with an EMC Fibre Channel SAN. Once this was in place, all problems disappeared, and it has been working flawlessly ever since. Granted, this is an extreme solution, but after many sleepless nights recovering from the HP's disasters it was well worth the investment IMHO.
Cogiko ergo sum (I geek, therefore I am).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply