Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 04-06-2009
Member
 
Join Date: Nov 2008
Posts: 1,259
All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS

I have a website build in ASP.NET 2.0 and SQL 2005. Everything was working properly till yesterday, but all of a sudden I got "All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS" error message in the event viewer.

I am unable to find any information on this error message. So no solution has been rectified for this situation. The main problem is that the SQL server gets restart after this error message.

Can you help me with some solutions for this error ?
Reply With Quote
  #2  
Old 04-06-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS

This happens because a client application is holding a lock for a long time which may be blocking you. In SQL, you can enable an event to capture blocking events which is taking a long time.

The following description of the sp_blocker_pss08 stored procedure captures this information. This information also applies to SQL Server 2005.
  • The starting time (according to the computer that is running SQL Server) so that this sampling of blocking can be time-aligned with other performance information, such as a Microsoft Windows NT Performance Monitor log or a SQL Profiler log.
  • Information about connections to SQL Server, by querying the sysprocesses system table.
  • Information about lock resources, by querying the syslockinfo system table.
  • Information about resource waits, by running DBCC SQLPERF(WAITSTATS).
  • The current running SQL Server Batch for connections that are blocked by others or blocking others, by running the DBCC INPUTBUFFER statement.
  • The ending time, according to the computer that is running SQL Server.

The stored procedure was created with the following optimizations to reduce the effect on performance and blocking by running this stored procedure:
  • There is no output generated unless at least one connection is waiting on a resource.
  • The sysprocesses and syslockinfo system tables from the master database are queried directly, to increase performance and to prevent this stored procedure from becoming blocked. Therefore, this stored procedure is specific to Microsoft SQL Server 2000 or later versions of SQL Server.
  • A small worktable is created by the cursor to obtain the DBCC INPUTBUFFER output; this should have no noticeable effect on the usage in the tempdb database.
  • Because the blocking can change while gathering the information, a fast mode exists, which increases performance by decreasing the results to relevant rows of the sysprocesses and syslockinfo system tables.
  • If trying to track non-lock resource waits, a latch mode exists, which causes the lock output to be omitted.
Reply With Quote
  #3  
Old 04-06-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS

Quote:
Error 17888: All schedulers on Node ## appear deadlocked due to a large number of worker threads waiting on <<RESOURCE>>. Process Utilization ##%.
SQL Server creates a Scheduler Monitor thread for each node. One of the Scheduler Monitor tasks is to watch the schedulers assigned to the node for progress. Slow progress triggers additional checks; among them is the 17888 check.

The 17888 condition is when Scheduler Monitor detects the workers are not progressing and if this is because of a common resource bottleneck.

The message indicates that a large number of workers are waiting on network I/O and until the I/O completes the SQL Server will remain bottlenecked.

To Diagnose and Correct Errors 17883, 17884, 17887, and 17888
Reply With Quote
  #4  
Old 04-06-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS

Just keep in mind this equation:

number of hosts * max thread-pool size * number of CPUs = potential DB sessions

Having a lot of connections and all trying to read and write on your MessageBox at the same time may overwhelm SQL Server locking your system. Because a Windows thread is the most basic executable unit available to a Windows process it is important to allocate enough threads to the .NET thread pool associated with an instance of a host to prevent thread starvation.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on LCK_M_IS"
Thread Thread Starter Forum Replies Last Post
virus has automatically deleted a large number of files? Damodaran-G Networking & Security 6 22-06-2011 03:34 AM
DIFF program capable of comparing a large number of texts kamillahg Windows Software 1 20-04-2011 05:21 PM
Fastest way to setup a large number of IPADS Lolita Portable Devices 4 27-02-2011 06:51 AM
Unable to run dsquery / dsget for large number of group members Dhananjay Active Directory 2 28-02-2010 08:30 PM
Converting CDATA Node into Text Node Jagdish Gada Software Development 3 07-12-2009 09:54 AM


All times are GMT +5.5. The time now is 06:47 AM.