November 13, 2012 at 5:47 am
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.
November 13, 2012 at 10:54 pm
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.
November 14, 2012 at 6:05 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply