SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ORMs and max dop option


ORMs and max dop option

Author
Message
Indianrock
Indianrock
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1996 Visits: 2371
Most of our queries are generated by an ORM (object relational mapper). Three weeks ago we put two new sql cluster boxes into service with 512GB of memory. Yesterday a report-type query with a ton of joins and a union was run from the application and it created stack dumps showing error " too many parallel nested transactions." Without more coding, the developers say they can't use a max degree of parallelism option. The server is set at maxdop zero.

Sql 2005 Enterprise SP4 9.0.5000 64bit

SQL Server Assertion: File: <lckmgr.cpp>, line = 385 Failed Assertion = '0' Too many parallel nested transactions. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.



SpringTownDBA
SpringTownDBA
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 1499
How many cpu's?

Ok, here is a blind guess that probably isn't optimal for you but is maybe, just might be, could happen to be more optimal than the default.

Set maxdop to 4
Cost threshhold for parallelism to 35.
Test and see if problem goes away.
Indianrock
Indianrock
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1996 Visits: 2371
I can't change server level settings like that without a lot of meetings etc. I'm experimenting on a test system with doing the first big select statement into a temp table using option(maxdop 1 ), then union that to the last select also using the maxdop option. If this works I can create a stored procedure which the application should be able to call with the numerous parameters used in the where clause.

MaxClockSpeed: 2128
NumberOfCores: 8
NumberOfLogicalProcessors: 16



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search