While MOM processes are running at some point, a process goes into deadlock and uses up all existing CPUs.
Sysprocesses shows this it opened up 4 threads and program name is Microsoft? Reliability Analysis Service.
Profiler doesn't show which command it was trying to execute, but last notable command which has started was MRAS_pcLoad EXECUTE @.i_Return_Code = sp_getapplock @.Resource = N'MOM.Datawarehousing.DTSPackageGenerator.exe', @.LockMode = N'Exclusive', @.LockOwner = N'Session', @.LockTimeout =
Can you please help us, what could be the problem. It has been running fine till couple of days back.
--Prabhu
are you running sql2k or sql2k5? Try lowering the "degree of parallelism" (sp_configure 'max degree of parallelism', #) or using Maxdop query hint (maxdop=1) to relief the problem.
Consider updating to latest service pack if you haven't done so.
http://support.microsoft.com/kb/293232
|||
It's SQL 2K, we are running on latest service pack, version 8.00.2187.
I decreased the degree of parallelism to 3 processors, instead of 4 (MAX, in this case), now all these 3 are pegged. I couldn't use the qry hint, as I mentioned it is running DTS executable.
--Prabhu
|||If max degree parallelism is set to either zero (0) or greater than one (1), you'd still encounter parallelism issue. Try setting it to one (1) to see if it helps.
|||In that case, not more than one processor would be used for parallelism, meaning no parallelism for qry execution. Right?
We would like to make use of all existing processors for qry parallelism, too.
--Prabhu
|||That's correct. If you set 'max degree parallelism' you set it for the entire server. Thus, every query will be affected by this.
The only other option is to use query hint maxdop which affects only that query, but you've already said you can't change that.
