1

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.

llanato
  • 2,508
  • 6
  • 37
  • 59
RCBeck
  • 11
  • 2
  • If you really are sure the data is the same, next step should be to ensure that structure is also the same (constraints, triggers, indexes...) – DrCopyPaste Jan 21 '15 at 13:50
  • hm ok, you say actual execution plans are the same? Double check, data is the same; run the queries manually (did you also check them using the Profiler?). It could also be that simply your "big server" is more under fire than the small one, meaning the execution plan may be optimal, but the server is in such heavy use that you have to wait a bit... – DrCopyPaste Jan 21 '15 at 13:54
  • Have you compared the result of running on both machines with `set statistics io on`? using `set showplan_xml on` then viewing in a diff tool can be insightful. – Alex K. Jan 21 '15 at 14:01
  • http://sqlperformance.com/2014/12/sql-plan/different-plans-identical-servers / http://sqlperformance.com/2014/11/t-sql-queries/multiple-plans-identical-query – Aaron Bertrand Jan 21 '15 at 14:17
  • My guess is there will be a difference in the *actual* plans based on something in the two articles I linked in my previous comment (and don't ever bother looking at *estimated* plans). For the *actual* plans did you validate that all of the estimated/actual row counts were the same? The diagram isn't the only thing to check. You can also use extended events to track the waits generated by the session executing the query, so you can compare what the query is doing on the more powerful machine that isn't happening on the small one. You should find evidence in the plans, the waits, or both. – Aaron Bertrand Jan 21 '15 at 14:21

1 Answers1

-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.

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • My experience with IN vs EXISTS queries is that it needs to be tested and tried. But thanks for the downvote............. – granadaCoder Jan 21 '15 at 14:27
  • It wasn't my downvote but you might want to read this article before making such broad claims about using IN. http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/ I tend to use the EXISTS like your example here. Alleviating the <> '' is simple. Just change it to > '' – Sean Lange Jan 21 '15 at 14:31
  • Thanks for the article reference. I altered my post to say "try out" vs a dogmatic statement. Just personally, I've seen about 3 queries in my life where exists made a big difference. I also had a developer who loved nested IN clauses. And 3 months later this person couldn't tell you why (this same person) wrote it that way. So I'm jaded. – granadaCoder Jan 21 '15 at 14:42