Running SQL 2012 BI on a spanky new server with 2690 x 2 CPU, fast SSD, ramdisk for tempdb and 128GB RAM. I have a problem finding a performance bottleneck, in essence.
Deleting records on a where condition is a the simplest example, but some select queries exhibit this behaviour too, for part of their runtime. Back to the delete example!
If CPU idles at 3 or 4% whilst the delete is running.
And RAM use is 13GB of 128GB
And Disk is idling at a few K per second
Why does this delete operation take minutes ? Why wouldn't SQL use more resources to get the job done faster ?