How do I utilise fewer cores?

Question

Post reply

How do I utilise fewer cores?

Rhys

SSC Enthusiast

Points: 178
More actions
September 15, 2020 at 3:17 pm

#3788570

Hi - I would like to decrease the performance of SQL for certain users or specific queries.
The majority of users will be running very quick queries that last less than a second as seen by the first two peaks in resource monitor. However this runs across all cores so when I run a larger query that could potential run for minutes or even an hour. The entire server becomes unresponsive and other users will eventually get timeout errors.
Can I limit the number of cores per connection? Or give myself 2 cores and everyone else can share 10? I don't mind waiting but I don't want the other users to be impacted.
Thank you
SQL version 13.0.5026.0

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 1

I think Resource Governor can help you with that as long as you have enterprise edition.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Rhys wrote:

Hi - I would like to decrease the performance of SQL for certain users or specific queries.
The majority of users will be running very quick queries that last less than a second as seen by the first two peaks in resource monitor. However this runs across all cores so when I run a larger query that could potential run for minutes or even an hour. The entire server becomes unresponsive and other users will eventually get timeout errors.
Can I limit the number of cores per connection? Or give myself 2 cores and everyone else can share 10? I don't mind waiting but I don't want the other users to be impacted.
Thank you
SQL version 13.0.5026.0

If you're sure that's the typical behavior of your machine, why not set the MAXDOP for the machine to something other than "0". You could also do that within your own code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

You can use MAXDOP on an individual query e.g.

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total  
FROM Sales.SalesOrderDetail  
WHERE UnitPrice < $5.00  
GROUP BY ProductID, OrderQty  
ORDER BY ProductID, OrderQty  
OPTION (MAXDOP 2);    
GO

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

Be sure also to make sure the "cost threshold for parallelism" is not too low. Hint: the default is too low, if you haven't changed it, you should.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".