Performance issue on SQL Server 2008 R2 hosted on Windows Server 2008 R2 Our client is using SQL Server 2008 R2 SP3 on Windows Server 2008 R2 Enterprise SP1. I download their MDF.

Their server is:


Intel XEON E5-4620 2.20Ghz 128 GB RAM (SQL Server using 64GB, 20% free).
64 cores on 4 processors.
RAID SSD I/O system (but doesn't matter because physical reads is 0).


My computer is:


Intel i7 5700HQ 2.7GHz 16 GB RAM (SQL Server using 8GB).
4 cores on one processor.


I query simple Select * from StokKart. Table has 68k rows and 20 columns.

Client server results

(68648 row(s) affected)
Table 'StokKart'. Scan count 1, logical reads 3854, physical reads 0, read-ahead reads 0, 
 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:    CPU time = 640 ms,  elapsed time = 4013 ms.


My laptop results

(68600 row(s) affected)
Table 'StokKart'. Scan count 1, logical reads 3847, physical reads 0, read-ahead reads 0, 
 lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:    CPU time = 187 ms,  elapsed time = 1742 ms.


I installed a fresh SQL Server on my machine using the client's backup and Windows 7. Client's Server always 4-4.5 seconds. Windows 7 machine under 2 seconds. Client always complains about a slow application. All indicators are normal. No I/O bottleneck, full RAM, or high CPU usage.

I tested the server at night when nobody was using the system. I use SQL Server Activity Monitor and Windows Resource Monitor for monitoring.

Query plans are exactly the same. 

Server:



My Notebook:



I tried SQL Server 2014 on same server. Still 4000 ms and mine 1600-1700 ms.

Changing the query to Select Count(*) from Stokkart, the results are:


Server : CPU time = 16 ms,  elapsed time = 11 ms.
My Laptop : CPU time = 0 ms,  elapsed time = 3 ms.


I tested the server using its own local SSMS (local); my laptop, my SSMS, so not a network issue.
As mentioned in comments on the question:


  sp_BlitzErik: If your CPUs have higher clock speeds why wouldn't they read data from memory faster?
  Sean Gallardy: So, you don't think a CPU that has a 59.1% faster clock speed (turbo boost up to 3.5Ghz) would have anything to do with a difference of 57.5% performance in a purely cpu/memory workload?
  Simon Hellings: If the query is not going parallel then the core speed matters. Core count is irrelevant at that point. If one car is travelling at 40mph and one at 70mph which one gets across the line in a straight line race?  Same thing with clock speed...


...the difference you are seeing in executing this query almost certainly down to the clock speed differentials between the two machines.

If the query doesn't generate a parallel execution plan, it's only going to be using one core no matter how many cores you have, or what settings you have for MAXDOP.

Effectively, this test is one 2.2GHz processor vs. one 3.5GHz processor. This is why you aren't seeing high overall CPU usage figures for the server: most of the cores are just sat there twiddling their thumbs.

Where the server is going to win out over your laptop is when it's not just one query running but many queries simultaneously.

I'm ridiculously over-simplifying here and I'm going to ignore all other possible resource contention (RAM etc.), but if 4 instances of this query hit the engine at the same time then your quad core laptop can execute all four together, but if we up that number of queries to 5 then one is going to have to wait for a core to become available. If it's 10 queries then 6 are going to have to wait their turn. On the 64-core server however, it can run those ten queries simultaneously, and another 54 for good measure!

Also, which edition of SQL Server 2008 is it? Your mention of it only using 64GB makes me wonder if it's Standard Edition, in which case it's worth bearing in mind that it will only use four physical processors anyway (32 of the servers cores) see Features Supported by the Editions of SQL Server 2008 R2 for details.

You mention that the client is complaining of a slow application, which I'd imagine is really what you'd like to get a handle on. I think the results from your test query have just led you down a blind alley. If you want to better address the performance issues the client is complaining about then you need to see what's actually happening on the server during use.

I'd start by installing Adam Machanic's excellent sp_whoisactive stored procedure and running that during times of high load to see what is going on. Even better might be to log the results to a table so you can get a more general picture.

Having mentioned Brent Ozar's site, I'd highly recommend downloading their FirstResponder kit as the tools in there can be invaluable in finding where the problems lie.

It's also worth having a look at the server's wait stats to see where the bottlenecks are - if you run the query found in Paul Randal's Wait statistics, or please tell me where it hurts, it will tell you what sort thing the server is waiting around for.