Overview
I have two servers with the same hardware specs and should be configured identically. They are running Windows Server 2012 R2 with 8 cores and 128 GB RAM each. On each server I am running a Hyper-V instance of SQL Server 2014. These servers are currently not doing anything else.
Problem
When I run a test on each server, for example:
BULK INSERT LOADTEST_01 FROM 'E:\loadtest\data.dat' WITH ( DATAFILETYPE='native', BATCHSIZE=100000 )
One server is always about twice as slow (3 minutes vs 6 minutes). The actual performance in terms of throughput is the same, but the "slow" SQL Server is generating about twice the I/O (reads and writes to and from the .mdf and .ldf) which is causing the test to take longer to complete.
The query used to pull out the metrics is:
SELECT
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO
And the metrics (edited in attempt to make easier to read):
Server DBName Logicalnam fileid Type physical nam size num_of_reads num_of_writes io_stall_read_ms io_stall_write_ms IO_Stall_Reads_PCT IO_Stall_Writes_PCt writes_and_reads mb_read mb_write reads_pct write_pct read_bytes_% write_bytes_%
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql-01 PPTTemp PPTTemp 1 ROWS C:\DATA\PPTTemp.mdf 5000 117 13099 319 220298 0.1 99.9 13216 6.89 3224.16 0.9 99.1 0.2 99.8
sql-01 PPTTemp PPTTemp_log 2 LOG C:\DATA\PPTTemp_log.ldf 30131.63 54 223225 240 193244 0.1 99.9 223279 1.14 12942.94 0 100 0 100
sql-02 PPTTemp PPTTemp 1 ROWS C:\DATA\PPTTemp.mdf 5000 108 7786 279 148597 0.2 99.8 7894 6.59 1920.49 1.4 98.6 0.3 99.7
sql-02 PPTTemp PPTTemp_log 2 LOG C:\DATA\PPTTemp_log.ldf 40105.25 57 174750 148 54018 0.3 99.7 174807 1.16 10194.92 0 100 0 100
I've tried copying the same SQL Server virtual machine to both physical host servers but the instance on the "slow" server continues to generate more IO and the "fast" server generates less IO. Same results if I eliminate any networking and domain membership.
Anyone have ideas?
I am about the re-completely blow away everything and re-install starting from the operating system. I don't understand how the Hyper-V host could make a SQL Server virtual machine generate more IO but I currently can't think of anything else.