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?




a. Have you check wait state of the running query ? Select * from sys.sysprocesses will do b. is there any resource governor in place ? c. have you use recompile hint to test it ? d. have you check the query plan ? e. have you tried to force the plan to run in paralel mode using dbcc cpuweight ? f. have you update the statistics of the tables?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.