Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I know SQL Server can use parallelism for certain DDL operation e.g. index rebuild or certain SELECT statements mostly likely in a data warehouse environments when query cost exceeds cost threshold of parallelism.

ORACLE offers parallel DML operations like INSERT, UPDATE or DELETE since version 8i.

Does SQL Server use parallelism for DML operations?

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

SQL Server supports parallelism for some of its physical operators.

DML statements, at a high level, are logical operations. In other words, you specify what you want done, not how it's done. As long as the logical operation is maintained, SQL Server is free to satisfy the statement using any set of physical operators it deems appropriate -- that applies both to DML and DDL.

SQL Server uses the cost threshold server setting to consider using parallelism for the physical operators chosen in the query plan. There's no difference as far as parallelism is concerned between DDL and DML, because as I said, SQL Server parallelizes the physical operations (i.e., an index rebuild can use the same Index Scan physical operator as a SELECT or UPDATE query could).

So, the short answer is yes, SQL Server can parallelize DML, but aside from the cost threshold setting, you have very little control over it. (That said, it can be restricted by using things like MAXDOP hints.)


Related, but a bit aside: for a query plan that includes parallel operators, the DOP at which the query runs is selected dynamically at execution time, not at plan compile time.

share|improve this answer
add comment

OK looking around I can confirm, you are right DML can be run in parallel.

Here is official confirmation taken from http://technet.microsoft.com/en-us/magazine/dd320292.aspx

Certain types of statements cannot be processed in parallel unless they contain clauses, however. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE or DELETE statements contain a WHERE clause, or an INSERT statement contains a SELECT clause, WHERE and SELECT can be executed in parallel. Changes are applied serially to the database in these cases.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.