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.

What is the meaning of DOP in the context of sql server?

share|improve this question

2 Answers 2

up vote 13 down vote accepted

DOP in SQL Server stands for Degrees of Parallelism.

Per the MSDN article here, if SQL Server has multiple processors available, and the query meets certain thresholds, SQL Server will try and split up the query over multiple processors to increase its performance.

In earlier versions of SQL Server, I have seen parallelism actually make queries run slower; that is, the time to spend figuring out the parallelism takes longer than the actual query would take without it.

In queries, you can specify the maximum number of processors to use by using the OPTION (MAXDOP X) query hint, where X is the maximum number of processors available. You can turn off parallelism by using OPTION (MAXDOP 1).

There is also a known issue with parallelism and SCOPE_IDENTITY() in SQL Server 2005/2008.

share|improve this answer
    
+1 for scope_identity issue –  garik Jan 7 '11 at 15:00

Degree Of Parallelism

When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.

To enable the server to determine the maximum degree of parallelism, set this option to 0, the default value. Setting maximum degree of parallelism to 0 allows SQL Server to use all the available processors up to 64 processors. To suppress parallel plan generation, set max degree of parallelism to 1. Set the value to a number greater than 1 (up to a maximum of 64) to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the max degree of parallelism value is ignored.

share|improve this answer

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.