October 15, 2019 at 2:06 pm
At new gig have a server that was physical and changed to vmware before I started. It is a RO node of an AG and seems to have some scary disk latency. Using SQL Sentry looking at over 2500MS for reads on some occasions. Below is one of the wonderful alert emails I get. Also ran diskspd and numbers look bad. Is there a setting on the vmware side anyone can think of which would affect disk subsystems like this. A physical box attached to this system does not have this issue
PHYSICAL READS on O: has 685ms average latency for mobiledoc_10 [ROWS]
PHYSICAL READS on J: has 418ms average latency for mobiledoc_7 [ROWS]
PHYSICAL READS on I: has 379ms average latency for mobiledoc_6 [ROWS]
PHYSICAL READS on E: has 241ms average latency for mobiledoc_2 [ROWS]
PHYSICAL WRITES on T: has 366ms average latency for temp8 [ROWS]
PHYSICAL WRITES on T: has 343ms average latency for temp5 [ROWS]
PHYSICAL WRITES on T: has 330ms average latency for temp3 [ROWS]
PHYSICAL WRITES on T: has 320ms average latency for tempdev [ROWS]
PHYSICAL WRITES on T: has 319ms average latency for temp7 [ROWS]
October 15, 2019 at 2:39 pm
A couple of years ago you needed to install VMWare guest tools to install better drivers for virtualization. Otherwise it would fall back to the much slower cpu-virtualization
Don't know if this is still the case.
October 15, 2019 at 6:36 pm
What is the SCSI controller type? Is this the default LSI Logic SAS? If so, you should change it to the VMWare Paravirtual.
You will not be able to simply change the SCSI type. You will need to add new SCSI controller, add new disks, stop SQL, move the data and log files, remove the old disks, and change the drive letters back to the old drive letters.
You can have 4 SCSI controllers. We use a dedicated paravirtual for the data, log and tempdb drives, and the LSI for the rest.
What is the power plan on the server in Windows control panel? is it set to balanced, or high performance?
Is the AG set to synchronous, or asynchronous? is there a significant volume of writes occurring to the secondary, and also reads?
Here is VMWare's recommendations:
https://kb.vmware.com/s/article/1002951
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 15, 2019 at 7:31 pm
so other dba looked at disk activity in sentry one and caught we have two controllers, one with 3 disks, the other with 9. This 9 includes drives with the largest files and tempdb. Going to spread it out but was wondering with RO Always on being so heavily dependent on tempdb if being on it's own controller would be good idea.
October 15, 2019 at 8:01 pm
so other dba looked at disk activity in sentry one and caught we have two controllers, one with 3 disks, the other with 9. This 9 includes drives with the largest files and tempdb. Going to spread it out but was wondering with RO Always on being so heavily dependent on tempdb if being on it's own controller would be good idea.
We typically put tempdb, data, and logs on their own dedicated paravirtual controllers.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 22, 2019 at 9:10 am
I've done this too. You can have up to 4 PVSCSI controllers per server so also might be a good idea to split your data over 2 controllers if possible.
If that's still not good enough, increasing the queue depth of the PV controller might be a way forward
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply