What are the best practices with setting parallelism in general? I know that SQL Server defaults to 0 to use all available processors, but in what instance would you want to change this default behavior?
I remember reading somewhere (I'll have to look for this article) that for OLTP workloads you should turn off parallelism (set maxdop to 1). I don't think I completely understand why you would do this.
When would you keep maxdop up to SQL Server (0)?
When would you turn off parallelism (1)?
When would you explicitly state the maxdop to a particular number of processors?
What causes parallelism?
Best Answer
You usually don't want to disable parallelism as that will also disable it for admin tasks. Your best bet is to fix the queries that are causing the parallelism through adding or fixing indexes or through making full on schema changes.
Based on your updated questions...
Some people will change MAXDOP to 1 for vendor built applications because they can't control the database or schema and they don't want a single query to take over the entire system.
Personally I always keep MAXDOP at 0 except for some rare cases.
Parallelism is caused by a single operation within an execution plan having an execution cost which goes over a pre-set setting (the cost threshold for parallelism setting). When this happens the SQL Server will kick in parallelism so that it can multi-thread the request in an attempt to speed up the process. The default value for the cost threshold for parallelism is 5. In many OLTP platforms you'll want to raise that up to 30 or 40 so that parallelism only kicks in for the really expensive queries.