Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ORMs and max dop option Expand / Collapse
Author
Message
Posted Tuesday, November 13, 2012 5:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 12:46 PM
Points: 596, Visits: 1,691
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.



Post #1384046
Posted Tuesday, November 13, 2012 10:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
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.


Post #1384392
Posted Wednesday, November 14, 2012 6:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 12:46 PM
Points: 596, Visits: 1,691

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



Post #1384581
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse