5

I've seen this question here (and elsewhere), but it doesn't really answer the question I'm trying to answer.

How can I tell how many Cores SQL Server is actually using?

I already know how many cores/CPUs are physically assigned to SQL Server. However, I want to know how many of those cores are, at peak, REALLY being used (ideally, I'd love to know percentiles, utilization %s per core, etc., but I'll take what I can get).

We'd like to migrate this instance to another machine and if we can reduce the number of cores assigned that could free up some core licensing for us. However, I don't want to "downsize" only to find out that our new SQL box falls over because it really needed those extra cores. We will be doing load testing, but the more info. we have around this, the better.

EDIT: I ended up just setting up some dashboard software which makes it easy to see/break out CPU usage (could have used PerfMon).

  • I'm sure there's monitoring software out there that will show this...somehow. But, it may not, specific to SQL Server usage, because the core usage is controlled by the NUMA software. I'll let the experts answer regarding whether there is some DMV or Extended Event that will capture this metric. – SQL_Hacker Aug 30 '17 at 13:50
  • 4
    Perfmon has all sorts of process/processor/NUMA counters available, at least one you can pull out sqlservr.exe. have you looked there? Run a capture for a day/week and have a look. – Kevin3NF Aug 30 '17 at 14:00
7

However, I want to know how many of those cores are, at peak, REALLY being used

This isn't really the way you'd want to figure out hardware size for a new server. You'd want to look at your server's wait stats.

If you want to do that for free, head over to firstresponderkit.org, and grab sp_BlitzFirst. It's a free tool written by the company I work for to help you figure out SQL Server bottlenecks.

There are a couple ways to run it that will be helpful to you:

EXEC master.dbo.sp_BlitzFirst @SinceStartup = 1;

That'll get you a whole bunch of server metrics since startup. Generally, the longer a server is up, the more valuable these metrics are.

Look at your top 5-10 waits here; the results may surprise you. There's a lot written about which resource different waits line up to, so searching is your friend.

If no waits are even close to your server's uptime (and you're not clearing wait stats), then congratulations, your server is bored.

You can also run:

EXEC master.dbo.sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;

If you want to take samples of server metrics for a set amount of time.

I wrote a whitepaper about this for Google (which you can also download and read for free) recently using the same (free!) tools. While it's geared towards people who are sizing instances in the cloud, it can be applied to physical or virtual servers anywhere.

Hope this helps!

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.