How to force query to execute on a single processor core without affecting other queries?

For example there is a case when we have database server with eight quad-core processors.
The database executes complex queries to generate reports.
We need to  to force query to execute on a single processor core without affecting other queries.

In this case the best option is using query hint OPRION (MAXDOP 1)

The OPTION (MAXDOP) Transact-SQL query hints can override the max degree of parallelism configuration option in the sp_configure value only for the query that specifies this option.

 If MAXDOP exceeds the value that was configured by using the Resource Governor, the Database Engine uses the Resource Governor MAXDOP value

Guidelines when you configure the MAXDOP value:

  • For servers that use more than eight processors, use the following configuration: MAXDOP=8.
  • For servers that have eight or less processors, use the following configuration where N equals the number of processors: MAXDOP=0 to N.
  • For servers that have NUMA configured, MAXDOP should not exceed the number of CPUs that are assigned to eachNUMA node.
  • For servers that have hyper-threading enabled, the MAXDOP value should not exceed the number of physical processors.

These are general guidelines only. There may be some exceptions to these guidelines. For example, do not use the guidelines under the following conditions:

  • If the server is not an online transaction processing (OLTP)-based server.
  • If the server is running large batch processing when there is no or minimal user load.
  • If the server is running resource-intensive operations.

Example:


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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.