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 ActÃvity 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.
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
|||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.
|||
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.
|||
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.
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?
|||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
|||
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
|||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.
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
|||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|||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
No comments:
Post a Comment