mibuso.com

Hello,

we have migrated our SQL Installation to following configuration:

Server with 2 Xeon CPU with each 4 Kernels, running at 2.4 GHz, 76 GB Ram, Fijitsu Storage DX90, connected with Dual Controller and ISCSI, VMWare 5.0, 1GBit Ethernet

The Virtual server on this machine has 20GB Ram, all 8 Kernels from the CPUs, and 12 GB Ram for the SQL Server.

The behaviour:
Before migration.: The List Window of child records from the table "contacts" has had a normal performance. The are about 50000 records. The child-table itself has about 30 calculated field (all of type lookup into the table contact).

After migration: The list window of the child table performs very, very poor, after setting filter, it is a desaster.... one record is shown, the waiting 5 secondes, another record, and so on.

Any ideas?

Hopefully waiting for tips

Rgds
Peter

What version?
How large is the database?
How are disk configured?

Following Configuration

- Nav 2009 SP1
- SQL Server 2008, latest SP's
- Database Size 100GB
- The Storage is a Raid Drive and manage itself - dont know, where (parts of) database is physically stored

HTH
Peter

The server needs to warm up. Is it faster the 2nd try?

If yes, then there might be an issue with the read performance on the disk. Memory is a gazillion times faster than disks.

Try running profiler with the Tuning option to see if there are slow queries, then look at the reads. Are there many reads then you want to look at missing indexes. Are there few reads then you might want to talk at the HDD guys.

Check also if your physical server is not overcommited with VM's. In this case it is possible that the memory of your VM is paged out to disk. The VM thinks it is in memory but VMWare has paged it out to disk. You can see that with performance monitor. If you have VMWare tools running, some counters are added that give info about the VM itself (like paged memory, ballooned memory,...).
Also: don't give you VM more VCPU's than it really needs. It can slow down your VM. Try to give it 2 VCPU's and if you see that the VCPU's are used a lot, you can give more.
See here for some more info http://www.brentozar.com/archive/2011/11/virtualization-san-basics-for-dbas-video/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+BrentOzar-SqlServerDba+(Brent+Ozar+PLF)

Petr,

Please check with your VMware admin about the disk type of your storage, is it SATA (low speed) or SAN (high-speed)? During the initial implementation of our virtual environment I encountered a similar performance issue using SATA, just after upgraded to SAN, everything seems to be back to normal.

Hope this helps.

Cheers,

Jemmy