How do I utilise fewer cores?

  • Rhys

    SSC Enthusiast

    Points: 178

    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.

    Untitled

    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

  • Alejandro Santana

    SSCommitted

    Points: 1809

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

  • Jeff Moden

    SSC Guru

    Points: 997150

    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.

    Untitled

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jonathan AC Roberts

    SSCoach

    Points: 17335

    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: 98559

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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