How can I tell how many Cores SQL Server, at peak, it is using?

Question

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

Answer 1

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!