March 5, 2015 at 2:27 am
can anyone please explain complete details of AWE and MAXDOP. How it works and what is the use of these
March 7, 2015 at 5:19 pm
Hi New Persopn,
AWE
AWE stands for Address Windowing Extension and is primarily used on 32 bit architectures. According to an article by Microsoft, "AWE should be used only when available physical memory is greater than user-mode virtual address space". I should note that this feature has been deprecated and it is recommended that you avoid this on new/future projects. Additionally, this should only be used on 32 bit servers with Greater Than 4Gb memory.
I won't go into too much detail on this but, you can find more information at the following article:
https://technet.microsoft.com/en-us/library/ms175581%28v=sql.105%29.aspx
MAXDoP
MaxDoP stands for Max Degree of Parallelism. It indicates the number of cores SQL Server will use when a query needs to go parallel. It's important to note that this should generally be set to an even number as SQL Server doesn't like odd parallel executions. This goes hand in hand with the Cost Threshold for Parallelism option in the SQL Server settings. Cost Threshold for Parallelism specifies how "large" a query must be before it is allowed to go parallel. Default for MAXDoP is 0 and default for Cost Threshold for Parallelism is 5. These are generally regarded as inefficient settings and should be changed accordingly.
As with much in Database Administration, the actual settings you should use for your environment really depends on your server's workload. Generally for OLTP instances, I like to start my Cost Threshold for Parallelism at 50 and test from there.
Here is a great article/video blog by Brent Ozar giving an overview of MAXDoP:
http://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/
Hope this helps! 🙂
~Steve
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply