SQL query faster on slower machine

Question

We have two MS SQL servers running MS SQL 2012. One has 8GB RAM, 2 processors and enough storage. The other has 8 processors, 64GB RAM, multiple attached drives with data, programs, logs and tempdb separated.

Usually, the big server is faster on queries. However, on a simple query, the small machine takes 10 seconds and the other machine it takes 5 minutes. As far as we can determine, the SQL Server settings are the same, the data is the same, the query is the same on both machines, the estimated and the actually execution plans are the same on both machines, but the big machine is slower. Tempdb is one file on the same disk as SQL.exe on the small machine, and there are 8 files in tempdb on an SSD drive for the big machine.

What steps can we follow to determine why a very few queries are slower on the big machine than the smaller machine?

The query is:

SELECT field1, field2 
FROM lookuptable1
WHERE field2 <> '' 
AND field1 not in (SELECT field1 
    FROM lookuptable2
)

We have 16 threads for parallel tasks on the big machine and 4 on the small. The query returns no values (expected), but much more slowly with the bigger machine. Stats have been rebuilt and the query plans flushed.

Answer 1

Test to see if exists (vs IN) might make a difference.

SELECT field1, field2 
FROM lookuptable1 outerTableAlias
WHERE field2 <> '' 
AND not exists (SELECT * 
    FROM lookuptable2 innerTable where innerTable.field1 = outerTableAlias.field1 )

)

Try the query without the "field2 <> ''". This would be a highly suspect place for a table_scan.