0

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.

alnedey
  • 11
  • 1

2 Answers2

0

I bet your recovery model and/or initial size of the database values are not identical between the source and target.

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
0

It turns out that the servers had different "System Profiles". For some reason the servers came from Dell differently configured. It was only revealed after installing the Dell OpenManage Server Administrator software. The slower server was set to a more energy-saving system profile.

alnedey
  • 11
  • 1