Parallelism & SQL2005

Hi all,

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.

thanks, Mark

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.

Hi. Thanks for your reply.

So is this setting purely for reducing the likelyhood of deadlocks?

That and I’ve been told that for NAV databases that is not real performance gain to be had with parallel queries.

Thanks for your help. I think we’ll trial this setting out.


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.