October 23, 2007 at 1:35 am
I haven't seen this before, and I can't seem to find what to search on to find an answer. What condition would there have to be for a process to block itself? There's no deadlock or anything. Just sp_who2 shows the process is blocking itself. Got any explanations I'd be appreciative.
October 23, 2007 at 1:41 am
What you're seeing in parallelism waits. Is there a wait type of CXPacket?
iirc, the display of spids blocking themselves was new in SP3 or 4. Before that you wouldn't see that behaviour.
Basically what's hapenning is that the process is plit up to run in parallel and some of the threads finish before others and have to wait for the slower threads to catch up.
Is nothing to be concerned about.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2007 at 9:46 am
Microsoft KB article on the subject:
http://support.microsoft.com/kb/906344
Nothing to be concerned about.
October 24, 2007 at 5:35 am
reporting of this behaviour as a block was introduced in SP4. I have noticed it is prevelent on I/O intensive operations such as indexdefrags and bulk inserts. It CAN be indicative of an IO system struggling to cope.
So look to see what operations you are seeeing this on, check out IO stats, maybe move some jobs around to quieter times.
---------------------------------------------------------------------
October 24, 2007 at 5:48 am
System SPID
You can define a trace that records only Microsoft® SQL Server™ processes while filtering out any unnecessary system events. Filtering out system server process IDs (SPIDs) saves system resources and time when you run a trace on a busy server.
How to filter system IDs in a trace
Profiler
How to filter system IDs in a trace (SQL Profiler)
To filter system IDs in a trace
On the File menu, point to New, and then click Trace.
In the Connect to SQL Server dialog box, select the server to which you want to connect and a connection method.
In the Trace Properties dialog box, do the following:
In the Trace name box, type a name for the trace.
In the Template name list, select a trace template.
Optionally, specify a save destination for the trace results.
Click the Filters tab, and then in the Trace event criteria box, expand SPID.
Expand Equals, Not equal to, Greater than or equal or Less than or equal, and then enter a value in the field that appears beneath the criterion.
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
KILL
Terminates a user process based on the system process ID (SPID) or unit of work (UOW). If the specified SPID or UOW has a lot of work to undo, the KILL command may take some time to complete, particularly when it involves rolling back a long transaction.
In Microsoft® SQL Server™ 2000, KILL can be used to terminate a normal connection, which internally terminates the transactions associated with the given SPID. In addition, the command can also be used to terminate all orphaned distributed transactions when Microsoft Distributed Transaction Coordinator (MS DTC) is in use. A distributed transaction is orphaned when it is not associated with any current SPID.
Syntax
KILL {spid | UOW} [WITH STATUSONLY]
Arguments
spid
Is the system process ID (SPID) of the process to terminate. The SPID value is a unique integer (smallint) assigned to each user connection when the connection is made, but the assignment is not permanent.
Use KILL spid to terminate regular non-distributed and distributed transactions associated with a given SPID.
UOW
Identifies the Unit of Work ID (UOW) of the DTC transaction. UOW is a character string that may be obtained from the syslockinfo table, which gives the UOW for every lock held by a DTC transaction. UOW also may be obtained from the error log or through the DTC monitor. For more information on monitoring distributed transactions, see the MS DTC user manual.
Use KILL UOW to terminate orphaned DTC transactions, which are not associated with any real SPID and instead are associated artificially with SPID = '-2'. For more information on SPID = '-2', see the Remarks section later in this topic.
WITH STATUSONLY
Specifies that SQL Server generate a progress report on a given spid or UOW that is being rolled back. The KILL command with WITH STATUSONLY does not terminate or roll back the spid or UOW. It only displays the current progress report.
For the KILL command with WITH STATUSONLY option to generate a report successfully, the spid or UOW must be currently in the rollback status. The progress report states the amount of rollback completed (in percent) and the estimated length of time left (in seconds), in this form:
Spid|UOW : Transaction rollback in progress. Estimated rollback completion: yy% Estimated time left: zz seconds.
If the rollback of the spid or UOW has completed when the KILL command with the WITH STATUSONLY option is executed, or if no spid or UOW is being rolled back, the KILL with WITH STATUSONLY will return the following error:
Status report cannot be obtained. KILL/ROLLBACK operator for Process ID|UOW is not in progress.
The same status report can be obtained by executing twice the KILL spid|UOW command without the WITH STATUSONLY option; however, this is not recommended. The second execution of the command may terminate a new process that may have been assigned to the released SPID.
Remarks
KILL is commonly used to terminate a process that is blocking other important processes with locks, or to terminate a process that is executing a query that is using necessary system resources. System processes and processes running an extended stored procedure cannot be terminated.
Use KILL very carefully, especially when critical processes are running. You cannot kill your own process. Other processes not to kill are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Execute sp_who to get a report on valid SPID values. If a rollback is in progress for a specific SPID, the cmd column for the specific the SPID in the sp_who result set will indicate 'KILLED/ROLLBACK'.
Use @@SPID to display the SPID value for the current session.
In SQL Server 2000, the KILL command can be used to resolve SPIDs associated with non-distributed and distributed transactions. KILL also can be used to resolve orphaned or in-doubt distributed transactions. A distributed transaction is orphaned when it is not associated with any current SPID.
The SPID value of '-2' is set aside as an indicator of connectionless, or orphaned, transactions. SQL Server assigns this value to all orphaned distributed transactions, making it easier to identify such transactions in sp_lock (spid column), sp_who (blk column), syslockinfo, and sysprocesses. This feature is useful when a particular connection has a lock on the database resource and is blocking the progress of a transaction. The user would be able to identify the SPID that owns the lock, and end the connection.
The KILL command can be used to resolve in-doubt transactions, which are unresolved distributed transactions resulting from unplanned restarts of the database server or DTC coordinator. For more information on resolving in-doubt transactions, see Troubleshooting DTC Transactions.
Permissions
KILL permissions default to the members of the sysadmin and processadmin fixed database roles, and are not transferable.
Examples
A. Use KILL to terminate a SPID
This example shows how to terminate SPID 53.
KILL 53
B. Use KILL spid WITH STATUSONLY to obtain a progress report.
This example generates a status of the rollback process for the specific spid.
KILL 54
KILL 54 WITH STATUSONLY
--This is the progress report.
spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.
C. Use KILL to terminate an orphan distributed transaction.
This example shows how to terminate an orphan (SPID = -2) transaction with UOW = D5499C66-E398-45CA-BF7E-DC9C194B48CF.
KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'
October 24, 2007 at 5:58 am
VAIYDEYANATHAN.V.S (10/24/2007)
System SPIDYou can define a trace that records only Microsoft® SQL Server™ processes while filtering out any unnecessary system events. Filtering out system server process IDs (SPIDs) saves system resources and time when you run a trace on a busy server.
How to filter system IDs in a trace
That was relevant, how?
Please, if you quote from BoL, or MSDN or other sources, make sure it's relevant and say where its from. It's not your work and should not be presented as such.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2015 at 12:07 pm
Waittype is Latch_Ex. The processes are stored procedures(several of them blocking themselves) I read in Microsoft KB that we can ignore if waittime is low. Could you please let me know how much value low waittime means?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply