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?