Microsoft Says there is nothing wrong with SQL Server 2008 R2

Over 3 weeks ago we solved the issue we were having with one  particular cluster node running SQL Server 2008 R2. We have been going back and forth with Microsoft to obtain an explanation on why the erratic behavior of the RDBMS engine.

The test was very simple:

1
Select TOP 10000000 * INTO #test from table1;  -- Select top 10 million rows and dump it into a temp table.

Test 1. Hyper-Threading Turned ON, after a fresh RDMBS engine restart:

Table ‘Table1’. Scan count 1, logical reads 375393, physical reads 180, read-ahead reads 381363, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10000000 row(s) affected)

SQL Server Execution Times:

CPU time = 46208 ms, elapsed time = 195976 ms.

Test 2. Hyper-Threading Turned ON, 10 minutes after a fresh RDMBS engine restart:

Table ‘Table1’. Scan count 1, logical reads 375393, physical reads 186, read-ahead reads 381433, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10000000 row(s) affected)

SQL Server Execution Times:

CPU time = 42885 ms, elapsed time = 540990 ms.

Test 3. Hyper-Threading Turned OFF, 1 hour after a fresh RDMBS engine restart:

Table ‘Table1’. Scan count 1, logical reads 375393, physical reads 195, read-ahead reads 381433, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 43118 ms, elapsed time = 49097 ms.

There were no parallel executions and the instance had no locks. This was the only query running on it.

After Microsoft support went through all the pssdiag logs, they were not able to find out what was the database waiting for. When looking at the performance counters  I/O numbers were dismal; 2 MB/s when writing, 10 MB/s when reading when HT was ON.

Changing the CPU and I/O affinity made no changes to the performance.

Once HT was turned OFF, we had peaks of 240 MB/s reading and writing.

Microsoft is stating that we might have a BIOS or Hardware problem.