MSSQL Spikes at a certain time of day.

[moifmurphy]

Hello,

We're running ESXi 3.5u4 on an HP Proliant 360GS with (1) Quad-Core Intel® Xeon® Processor E5405 (2.00 GHz, 1333MHz FSB, 80W) and 12GB RAM. The disks are RAID 0 with 3 x 146GB 10k SAS drives making up Datastore 1 and 2 x 300GB Dual Port 10k SAS drives making up Datastore 2. The VM I am having issues with resides on Datastore 1.

We've converted our MSSQL server and we are finding that at or around a particular time of the day our users in Finance are reporting that their Sage backend loses connectio. Upon investigation we are seeing huge spikes in Disk Read Rate. Everything else looks normal: network, system, memory etc.

I'm beginning to wonder if it's MSSQL itself rather than the VM setup or the actual server hardware. We have other VMs running but nothing on a scale that interferes with the SQL transactions nor the disk.

I've read the VMWare SQL permformance PDFs and the hardware best practices but havn't come across anything that could be related.

If anyone could offer any advice it would be greatly appreciated. I've included a couple of screenshots of the Disk activity.

Thank you.

[MHAV]

Oh well what you want solve the peaks of your VM then you should invest in some hardware. If you want to find out what causes the peaks you have to look at the VM and whats going on while the peaks are gonna happen to find out if that is a none typical problem of the SQL or the application that uses your SQL or not.

Regards

Michael Haverbeck

If you find this information useful, please award points for "correct" or "helpful".

[Scissor]

Do you have any scheduled tasks that run at the same time each day? Backup Jobs? AntiVirus definition updates or scans? SQL Maintenance Plans? Inventory scans (SMS, etc)?

If you find any, perhaps you can schedule them to run at a lower priority or in the evenings.

[moifmurphy]

It's definitely not the hardware. We're running 3 other VMs on that server without any issues whatsoever. That's not to say that I wouldn't mind some new kit but as it is I'm not in control of the budget

[moifmurphy]

Not that I'm aware of. I'll double check but we do all scans and tasks overnight.

I'll get back to you on that one.