November 26, 2021 at 1:54 am
Hi everyone
I am in the process of upgrading my hardware. I have been using this link for guidance
https://glennsqlperformance.com/2020/12/07/choosing-a-processor-for-sql-server/
I have a question about how the cores actually get used. Does SQL Server automatically optimize the execution of a query based on number of cores? Or do I have to do something so they get used? I am new to all of this so I don't know if higher core count actually makes a difference for my needs. I will using SQL Server for storing data and then running queries against it for getting reports. I do not plan on using it for any sort of online business that requires tons of transactions to be processed in seconds. The lowest one on Glenn's site is a 4 C Xeon. Looks like it might do the job but not sure for sure.
Any guidance is much appreciated.
Thank you
November 26, 2021 at 7:30 am
Hi,
you should take a look at this parameters:
Cost Threshold for Parallelism
Max Degree of Parallelism
With these parameters, you are able to tell the sql server, how many of your cpu the server should use for parallelism.
Good luck,
Andreas
November 26, 2021 at 3:00 pm
Another thing to note - parallel queries MAY not be what you actually want. Depending on the query, it may not make sense for it to run in parallel. I've had queries that result in self-blocking by going parallel. What I mean is that a query that usually runs in 2 seconds takes 20 minutes because something went haywire and now each of those parallel threads are actually running in series as they need to wait for the previous one to complete! The quick and easy fixes are:
1 - add NOLOCK hint <-- don't like this solution as it was dealing with financial data and dirty reads are bad
2 - add a MAXDOP hint of 1 <-- an OK solution, but it is really limiting as depending on the parameters of the query, sometimes parallelism was not a bad thing
3 - adjust cost threshold of parallelism <-- BINGO! this is the solution we went with. Allowed the query to go parallel when the parameters worked for it and serial when they didn't. Just had to fiddle with the numbers.
Mind you, the parallel optimized version ran in about 2 seconds, forcing it to non-parallel (option 2) make it take 2.5 seconds. So not a huge performance hit on a query that was only run a few times per day.
Now if you have 100 users hitting the SQL instance at the exact same time running queries that don't disrupt each other (ie no blocking), you could be using 100 threads (or more, depending on if any of those went parallel). SQL can run multiple queries in parallel as long as they don't require exclusive access to shared resources.
Now, for a reporting purpose server, I would probably want a lot of cores. The reason being that your data is likely de-normalized to optimize data retrieval by reducing the number of joins needed. If I remember right, joins won't run parallel, so having more cores won't help if you have a lot of joins. BUT if you are grabbing a boatload of data from disk/memory to be presented to a tool (SSRS perhaps), having that run multi-threaded may give you a performance boost. And if 100 people are running the report at the same time, more cores should mean better performance for the end users. I say SHOULD because if you have 1024 1 Ghz cores and 100 users hitting the server it will perform worse than 128 2 Ghz cores, assuming 1 core per user.
Now, on top of cores though, you also need to take into account memory. SQL Server doesn't like to share memory and once it grabs the memory, it will hold onto it for as long as it possibly can. SSRS and SSIS (for example) operate OUTSIDE of SQL Server memory. So if you have 128 GB memory on your server and give SQL 120 GB of that, that leaves 8 for the OS, SSRS, and SSIS which may not be enough and you MAY have performance hits on those systems or even failed SSIS packages. Even if you had the beefiest CPU available, if you don't have enough RAM OR you misconfigure the SQL instance, you can have devastating performance hits.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 26, 2021 at 3:42 pm
Higher core counts also mean higher licensing costs.
November 26, 2021 at 10:24 pm
Thank you everyone. this is very helpful
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply