Numer of procesors for parallel execution

couberpu

Good Morning,

In my company, we have a Dell PE6600 server which has 8 1.6GHZ CPU. I am having a problem on setting number of processors for parallel execution. Please help me with it. We are on NAV 3.60 with NAV 3.70b run time, SQL option & Win 2003, 14GB RAM.

Thanks,
Coubepu

DenSter

Is pt_dop not working?

You can also go into server properties and set the value there. I don't have SQL2000 handy right now, but it should be in there somewhere. There's also a way to do it using T-SQL, check Books Online for the syntax.

couberpu

Hi Daniel,

It is working! It was set to 1 and I did some testing with 2 and 4. Did not see too much change on the performance. Just want to know if it real matters to set to 1 or any numbers of processors, why can I just use all available?

Need you to come!


Thanks,
Couber

DenSter

Setting degree of paralellism to more than one doesn't make SQL Server use all processors all of a sudden. SQL Server only decides to use multiple processors per query if it thinks it is necessary. Queries in NAV are so small that multiple processors really doesn't make much of a difference, and it has been the cause of problems. There have been instances where SIFT updates were processed in parallel by multiple processors, and caused deadlocks within the same transation. So because there is not very much benefit for NAV to set DOP to higher values, we recommend setting it to 1, not because it gains so much performance, but because it prevents those particular types of deadlocks from happening.

couberpu

Gee, Thanks! :idea:

So I need to make sure all the Key and SIFT were properly tuned, based on the performance requirements, and also a better written code in NAV to achive better performance!

I am not ready to do either of them, what will be my options?

kriki

[Topic moved from 'General Chat' forum to 'SQL General' forum]

stryk

Hi!
Especially the SIFT optimization is crucial to boost perfromance! When it's up to tune the indexes, well, with 3.70 C/SIDE you don't have that much optiones ... maybe you should consider a technical ugrade to 4.0 SP3 (Build 26565 or higher), here you have lots of enhancements and more options for optimizations!

When it's up to MAXDOP: Usually 1 is feasible with NAV (due to the SIFT-self-locking-issue). If you don't encouter this issue, you should try to "open" the MAXDOP to 2 - 4. With NAV the queries are quite simple and not realy challenging SQL Server, thus parallel execution plans can hardly be generated anyway ...

To use the CPU capacity efficiently, you could think about splitting the NAV database files into many; but here also several things have to be regarded! There's are curret discussion about that: http://www.mibuso.com/forum/viewtopic.php?f=34&t=29547

Also have in mind that the MAXDOP setting effects the DBCC DBREINDEX command. THe less CPU are used, the longer the re-indexing would take ... (with SQL 2005 the ALTER INDEX REBUILD is able to increase the MAXDOP for re-indexing).

Regards,
Jörg