What kind of operations parallelism occur?
Your question is unclear. Parallelism can occur with all types of queries (DQL, DML, etc) for a multitude of operations.
As long as the query exceeds the Cost Threshold for Parallelism and isn't limited to
MAXDOP 1, and doesn't use any parallelism-inhibiting components (per Paul White) such as:
- Modifying the contents of a table variable (e.g. multi-statement Table Valued Functions - though this only limits the query to serialized zones around where the table variable is modified)
- T-SQL scalar functions
- CLR scalar functions marked as performing data access
- Intrinsic functions including OBJECT_NAME, ENCYPTBYCERT, and IDENT_CURRENT
- System table access (e.g. reading from sys.tables)
...then the query should be applicable for parallelism.
Per Erik, it should be further noted that in Standard Edition, certain actions have limitations on parallelization:
- Index creation - No parallelism
- Index rebuild - No parallelism
- Batch mode operations - Limited to 2 degrees of parallelism
“Your question is unclear. Parallelism can occur with all types of queries (DQL, DML, etc) for a multitude of operations” Please, can you give me more details?– LB_Oct 24, 2022 at 21:00
@LB_ All of the details are in the rest of the answer. What else are you looking for?– J.D.Oct 24, 2022 at 21:03