I’m reading through a performance orientated powerpoint presentation from Teched 2006. Its titled ‘Microsoft Dunamics NAV and SQL Server 2005: Tweaking for Speed’.
One of the options for altering SQL Server config is ‘max degree of parallelism - set to 1 to minimise parallel queries’.
Is anybody able to explain the logic and reason behind this suggestion? Having not been present when the presenation was delivered I may be missing something. From my understanding setting this to 1 would have a negative effect, limiting processor usage. Our server has 2 dual core processors.
The issue is the way that NAV communicates with SQL Server. Basically it communicates with queries for each record wthin the same transaction, and they need to be processed in the same order as they are sent to SQL Server. If you set DOP to more than 1 (or 0 for max) you run the risk of having one core being done before the other, and you can end up deadlocking within one transaction. It has also happened when SIFT updates are handled by multiple processors.
In a live environment all processors will be used, just not by any one single transaction. It’s best to keep DOP at 1 for NAV implementations.
This because Navision only sends simple SELECTS (only 1 table per query) to SQL. Parallel processing is only useful with complicated queries. If SQL decides to use multiple cores for a Navision query, it mighe hinder performance. It is better to leave the other cores free for other statements.