I'm new to AWS, so I think this might be a really simple question:
I'm using an EC2 t1.micro instance as my SQL DB server for some small-scale analysis that I'm doing (Windows, SQL Express 2008). It was all going smoothly until I recently had a huge SQL query that was maxing out the poor CPU on the micro instance, and taking way too long to run. Seeing that the bottleneck was the CPU, I upgrades to a "High CPU" instance (c1.medium), which has 5 EC2 compute units (2 cores x 2.5 compute units each). I kicked off a test of the query again, it ran much faster, and I happily kicked off the full CPU-intensive query.
When I check the Resource Monitor to see how my new hardware is doing, I see that the sqlservr.exe process is only using exactly 50% of CPU, and the MSSQLSERVER service is similarly using exactly 50%. On the micro instance, it was using 100%. I'm oversimplifying things a bit by assuming the bottleneck is uniformly the CPU, but it seems like SQL should be pushing the CPU to 100% to crunch the query faster (and exactly 50% seems suspicious).
My question is this: why is the CPU only at 50%? Does it have something to do with the 2 cores? Do I need to do anything to let SQL have full use of the computing power I'm paying for? Or is SQL using all the computing power it can, and I'm misinterpreting what I'm seeing in Resource Monitor?
Best Answer
Yes, 50% means it's only using one core.
(EDIT: replaced answer about CPU affinity with answer about SQL Express being limited to one CPU.)
This is because MSSQL sees each vCPU as a CPU, rather than a core, and MSSQL Server Express is limited for use on one CPU only. So letting SQL use all the processing power you're paying for will require using a different version of MSSQL or a different SQL implementation altogether (like MySQL or the like).