Menu
SELECT [SomeColumn]
FROM [Table1] t1
WHERE Table1ID = (
SELECT [Table1ID]
FROM [Table2] t2
WHERE t2.ID = 2
OPTION (MAXDOP 4)
)
OPTION (MAXDOP 4)
This gives an error Incorrect syntax near the keyword 'OPTION'. on the first OPTION (MAXDOP)
Is it possible to use OPTION (MAXDOP) in a sub query? Or the final OPTION (MAXDOP) will be good enough to set the degree for the sub query as well.
244
The MAXDOP option is utilized to powers the maximum level of parallelism value at the individual T-SQL statement. MAXDOP value cannot be the same as the SQL Server Degree of Parallelism parameter value. Actually, it is more helpful when additional CPU resources are required to execute the specific SQL statement.
Setting MAXDOP to 2, 4, or 8 generally provides the best results in most use cases. We recommend that you test your workload and monitor for any parallelism-related wait types such as CXPACKET . where: cpu_count refers to the number of logical CPUs in the system.
You can override the max degree of parallelism value in queries by specifying the MAXDOP query hint in the query statement. For more information, see Query Hints (Transact-SQL).
you can change this settings without outage / restart. It means that the change takes effect when the RECONFIGURE statement is executed. If you're looking at a default setting for MaxDOP, good chance you also have a default setting for Cost Threshold for Parallelism.
The subquery might not be in one piece at runtime. The optimizer can pick it apart and reorder everything. This query will certainly not execute as written. Therefore a maxdop for a subquery does not apply to SQL Servers execution model.
There can only be one maxdop for the whole query. There is a logical reason for this restriction.
126
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
