I'm trying to identify the bottleneck on a new SQL Server implementation (2012 Enterprise). I've been running some meaty queries/ETL jobs which are taking a considerable time - but I'm having difficulties identifying what is the limiting factor.
The server is a Dell R720 with 2 x Intel(R) Xeon(R) CPU E5-2609 0 @ 2.40GHz (4 cores each). It has 384Gb of RAM.
Storage for the data is an MD1220 array populated with 24 x 400Gb Dell SSDs. The array is connected via 2 x 6Gb SAS connections to an H810 with 1Gb cache.
Storage for the logs is 15 x 600Gb 15K Dell rotational disks in the internal bays in the R720, connected to the internal H710P contorller.
The database I am working with is 200Gb - so should entirely fit in RAM. The Cache Hit ratio is 99.997% - which would suggest this is the case. I was therefore expecting the limiting factor to be CPU - but the CPU ticks along at about 10%. None of the individual cores are running hot. The IO utilisation to both arrays (data and log) and in both directions (read and write) are also less than 50MB/second - and I believe should be able to come close to saturating the SAS.
I have played around with MAXDOP (currently set to 4) to no avail.
The queries I am using are largely manipulating data - not extracting data and sending it over the network - and the network throughput is minimal.
Any idea where the bottleneck could be? Could it be the speed of the memory or internal bus? Any suggestions as to where I should look next?