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?


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).

| improve this answer | |
  • Good idea - but I checked the affinity of the sqlservr.exe process, and it's set to run on all processors. Do you think it might be that SQL is unable to break itself up to run in parallel across 2 cores? – Mike Monteiro Aug 15 '12 at 18:14
  • That clears it up perfectly - thanks! So if I don't want to switch away from SQL Express, I want an instance with the most compute units per core possible (so I can load up the work on a single core). – Mike Monteiro Aug 15 '12 at 18:16
  • @MikeMonteiro Yup, exactly. If you need to keep using MSSQLExpress, you're going to have squeeze the most compute power you can out of one core. (And sorry for the rapid edit, I noticed you were using Express after I'd already hit submit. I thought I might have corrected it before anyone noticed, but it seems not. – HopelessN00b Aug 15 '12 at 18:26

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.