August 11, 2004 at 7:41 pm
I found the following in our sql server errorlog. We are win2k3, enterprise edition, 8 proc, 8GB. We are SP3 plus the SP3 security patch 815495 MS03-031: Cumulative security patch for SQL Server.
server Error: 17883, Severity: 1, State: 0
server Process 112:0 (12a8) UMS Context 0x1248CE70 appears to be non-yielding on Scheduler 6.
server Stack Signature for the dump is 0x00000000
I've read all the articles on microsoft support, but I still can't seem to understand a single root cause.
http://support.microsoft.com/default.aspx?scid=kb;en-us;810885&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;816840&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;815056&Product=sql2k
http://support.microsoft.com/default.aspx?scid=kb;en-us;815436&Product=sql2k
http://support.microsoft.com/?kbid=867878
Please, if you have information on fixes or workarounds, I am very interested!
MAK
August 12, 2004 at 12:32 am
See if any UMS threads are running..
You can use this undocumentated DBCC command and see if there are any blocks.
DBCC SQLPERF(UMSSTATS)
Ramesh
August 12, 2004 at 9:03 am
We saw this error on our database server against which a huge query was run simultaneously from 9 different servers. The query eventually updated the application server cache. We had SQL hang after getting all the above error in the SQL Log.
According to microsoft,
On multi-processor systems, SQL Server will try to run queries in parallel but if it does not find enough free resources, it won't run the query in parallel.
In our case, all the queries were trying to run in parallel.(Strangely, our QA server behaved exactly how microsoft said but not production.) Specifying the maxdop hint got us around this issue.
You might want to look into restricting the degree of parallelism to 4 or so if you cannot identify what query is causing issues.
August 12, 2004 at 5:03 pm
Thanks for the information. We've tinkered with parallelism settings in the past. One (ignorant) question would be, if we restricted parallelism to something like 4 on an 8 processor machine, how would I get performance benefit from the other 4 processors? Wouldn't I be wasting hardware at that point? Stated differently, is it true that MSSQL EE 2000 SP3 begins performing worse on machines with more than "n" processors?
Thanks again for the information, very useful!
August 16, 2004 at 11:38 am
Max degree of parallelism restricts # of processors per "a" query. So , if this value is set to 4, processors 0-3 may be used for query 1, then processors 4-7 may be used for query 2. So you are not really wasting the # of processors. You are just limiting how many you can use for a query. I have seen one of our software vendors (we use packages apps a lot) limit maxdop even to 1 on a 4 processor system. They claim, they have seen that work better for their app.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply