JM_F


Today I ended up in a situation where I had a process with total six "subthreads" (identified by different execution context) (seen in Activity Monitor). All of these had blocking=1. The server didn't function properly, I don't know the details of these problems, since I was not present at that time. We had to kill the processes. What is the process id 1, "RESOURCE MONITOR" in SQL Server 2005, seen in Activity Monitor Is it fatal if some processes are blocking RESOURCE MONITOR How can one end up in such situation, is it normal or a bug somewhere

The server is a 64-bit Windows server having SQL Server 2005 SP1.

Yesterday I had a CLR stored procedure running on another server. The procedure uses System.Data.SqlClient.SqlConnection to access this server. The procedure started about 11.4.2007 22:22. The procedure created a connection to the SQL Server and created a select that should return 1,5 million rows. During fetching the rows (about after 800 000 rows) the procedure crashes to an error:"".NET Framework execution was aborted by escalation policy because of out of memory. " Naturally the procedure couldn't close the SQL Server connections, since it was forced to end.

The details if the processes as seen from Activity Monitor (I only have screenshots so I can't copy-paste...):

The main process:

Process id: 69

status: suspended

open transactions: 1

command: SELECT

Application: .NET SqlClient Data Provider

Wait time: 578

Wait type: ASYNC_NETWORK_ID

CPU: 1375

Physical IO: 22

Memory usage: 2

Login time: 11.4.2007 22:22:05

Last batch: 11.4.2007 22:22:05

Blocked by: 0

Blocking: 1

Execution context: 0

Two "subthreads", there are five similar.

Process id: 69

status: suspended

open transactions: 0

command: SELECT

Application: .NET SqlClient Data Provider

Wait time: 35293046

Wait type: CXPACKET

CPU: 4875

Physical IO: 2214

Memory usage: 2

Login time: 11.4.2007 22:22:05

Last batch: 11.4.2007 22:22:05

Blocked by: 0

Blocking: 1

Execution context: 1

Process id: 69

status: suspended

open transactions: 0

command: SELECT

Application: .NET SqlClient Data Provider

Wait time: 35293031

Wait type: CXPACKET

CPU: 4875

Physical IO: 2210

Memory usage: 2

Login time: 11.4.2007 22:22:05

Last batch: 11.4.2007 22:22:05

Blocked by: 0

Blocking: 1

Execution context: 2

The rest three subthreads differ from the above by having different wait time, CPU, physical IO and execution context.





Re: Processes blocking RESOURCE MONITOR, normal behaviour?

Jag Sandhu


Alright Chap,

You need to look at the "BLOCKED BY" rather than "BLOCKING" column.

The BLOCKING=1 means that this process is blocking another process.

Looking at the info you provided, the SPID 69 is not being blocked by any process.

Hope that helps.

Jag







Re: Processes blocking RESOURCE MONITOR, normal behaviour?

Dhericean

Hi

First off are you aware of the issue http://support.microsoft.com/kb/928083. I ask because one of its symptoms is the .NET framework message you mention and I just wanted to make certain you could eliminate it as a cause of that problem.

On the wait/blocking issue you may want to check out http://msdn2.microsoft.com/en-us/library/ms179984.aspx which describes the various wait states and a dynamic management view to look at them. The wait type that you are seeing (CXPACKET) is particularly associated with parallellisation of queries. There is a recommendation of trying reducing the degree of parallelism if you see a problem with this type of contention.






Re: Processes blocking RESOURCE MONITOR, normal behaviour?

JM_F

Jag Sandhu wrote:

Alright Chap,

You need to look at the "BLOCKED BY" rather than "BLOCKING" column.

The BLOCKING=1 means that this process is blocking another process.

Looking at the info you provided, the SPID 69 is not being blocked by any process.

You are right, 69 is not blocking anything. I'm not interested in what 69 is blocking.

The problem is that 69 IS blocking SPID 1. SPID 1 is a system process, whose significance I don't know. During the problem 69 had been blocking 1 for a long time and the SQL server had been quite jammed. I was suspecting that the jamming was because the system process 1 couldn't do anythin being blocked by 69.






Re: Processes blocking RESOURCE MONITOR, normal behaviour?

JM_F

Dhericean wrote:

First off are you aware of the issue http://support.microsoft.com/kb/928083. I ask because one of its symptoms is the .NET framework message you mention and I just wanted to make certain you could eliminate it as a cause of that problem.

Thanks for the information, I actually was unaware of the issue. This time I am not using a context connection, so the KB-entry is not valid in my case I have two SQL Server instances and the CLR stored procedures run on instance A and use a "normal" SQL Server connection (instead of context connection) to connect to the server B.

Dhericean wrote:

On the wait/blocking issue you may want to check out http://msdn2.microsoft.com/en-us/library/ms179984.aspx which describes the various wait states and a dynamic management view to look at them. The wait type that you are seeing (CXPACKET) is particularly associated with parallellisation of queries. There is a recommendation of trying reducing the degree of parallelism if you see a problem with this type of contention.

OK, thanks for this information too. I find this CXPACKET issue also very strange since our database is in practise idle most of the time. And when this issue I reported happened the connection 69 had been in CXPACKET state for a while (don't know details, but maybe at least minutes). Shuoldn't the CXPACKET state change to something else after a while Can this have something to do with the RESOURCE MONITOR process being blocked by process 69






Re: Processes blocking RESOURCE MONITOR, normal behaviour?

Jag Sandhu

Hi JM_F,

When BLOCKING column is set to 1 which means IT IS blocking another process. Other possible value for BLOCKING column is 0 which means it is not blocking any processes.

The values are 1 or 0 for Yes or NO respectively.

You have to use SP_who2 or DMV - sys.dm_exec_requests

and look for spid 69 in the BLOCKED by column.

regards

Jag






Re: Processes blocking RESOURCE MONITOR, normal behaviour?

JM_F

Jag Sandhu wrote:

When BLOCKING column is set to 1 which means IT IS blocking another process. Other possible value for BLOCKING column is 0 which means it is not blocking any processes.

The values are 1 or 0 for Yes or NO respectively.

Hello Jag,

If I open Activity Monitor and click help, the following comes:

Blocked By

Process ID (SPID) of a blocking process.

Blocking

Process ID (SPID) of processes that are blocked.

This very clearly states that the value of blocking column contains the process ID. You say it contains 0 or 1. Are you sure of this

JM






Re: Processes blocking RESOURCE MONITOR, normal behaviour?

Jag Sandhu

Hi JM,

To confirm, please see the books online topic - Activity Monitor (Process Info Page).

Process ID

SQL Server Process ID.

User

ID of the user who executed the command.

Database

Database currently being used by the process.

Status

Status of the process (for example, running, sleeping, runnable, and background).

Open Transactions

Number of open transactions for the process.

Command

Command currently being executed.

Application

Name of the application program being used by the process.

Wait Time

Current wait time in milliseconds. When the process is not waiting, the wait time is zero.

Wait Type

Indicates the name of the last or current wait type.

Resource

Textual representation of a lock resource.

CPU

Cumulative CPU time for the process. The entry is updated only for processes performed on behalf of Transact-SQL statements executed when SET STATISTICS TIME ON has been activated in the same session. The CPU column is updated when a query has been executed with SET STATISTICS TIME ON. When zero is returned, SET STATISTICS TIME is OFF.

Physical IO

Cumulative disk reads and writes for the process.

Memory Usage

Number of pages in the procedure cache that are currently allocated to this process. A negative number indicates that the process is freeing memory allocated by another process.

Login Time

Time at which a client process logged into the server. For system processes, the time at which SQL Server startup occurred is displayed.

Last Batch

Last time a client process executed a remote stored procedure call or an EXECUTE statement. For system processes, the displayed time is that at which SQL Server startup occurred.

Host

Name of the workstation.

Net Library

Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that allows them to make the connection. .

Net Address

Assigned unique identifier for the network interface card on each user's workstation. When the user logs in, this identifier is inserted in the Network Address column.

Blocked By

Process ID (SPID) of a blocking process.

Blocking

Indicates whether this process is blocking others. 1 = yes; 0 = no.

Execution Context

Execution context ID used to uniquely identify the subthreads operating on behalf of a single process.






Re: Processes blocking RESOURCE MONITOR, normal behaviour?

JM_F

Jag Sandhu wrote:

To confirm, please see the books online topic - Activity Monitor (Process Info Page).

Blocking

Indicates whether this process is blocking others. 1 = yes; 0 = no.

Interesting... I found the entry you pointed in MSDN (http://msdn2.microsoft.com/en-us/library/ms178520.aspx). However the help page in my local installation (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/12f87b09-bf20-4a69-8333-e67419472337.htm) contains the description I sent before. So these descriptions don't match.

Can I assume this a bug in SQL Server local documentation

I have SQL Server 2005 SP 1 on Windows XP.

regards,

JM






Re: Processes blocking RESOURCE MONITOR, normal behaviour?

oj

MS does a very good job of keeping sql bol on msdn2 current. You should download the latest one from http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx






Re: Processes blocking RESOURCE MONITOR, normal behaviour?

Jag Sandhu

Hi JM,

Please get the latest bol.

By the way, BLOCKING thing is even same for SQL 2000. So its always been like that.

regards

Jag