What kind of operations parallelism occur?

Question

What kind of operations parallelism occur?

Answer 1

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