SQL Server - Using MAXDOP and other processor settings - Part 1
After discussing about memory settings in my previous post , comes next set of important settings available which are related to processor. Just like memory settings SQL Server has provided several options, which we can tweak to get an optimal performance from the processor(s) hooked to a SQL based Server. In first part of this topic I would write about the most important option MAXDOP, which is short for "Max(imum) Degree of Parallelism". This parameters takes a numeric value, which is equivalent to the number of processor(s) SQL Server can use in parallel in order to process a resource heavy query. The default value for this option is "0" which means SQL Server will use all available CPUs (or CPU Cores) in case it chooses to go for parallelism. Yes, you read that right "if SQL Server chooses to". Some of you may get surprised to know this but SQL Server doesn't always go for parallelism for each query. There is a threshold value defined fo