2

The following has happened on more than one occasion.

Updates or inserts to a certain table (I'm not 100% sure but there seem to be a couple of tables it always happens to) fail due to a timeout. If I run the same query from Management Studio, it just keeps running without ever ending or failing (I've let it try up to an hour, on one occasion).

SSMS's Activity Monitor, as well as sp_who2 don't show the process as being blocked by anything. It seems to be running with no issues, except that it isn't doing anything or ending.

This is the same if it's only one row being updated, selected by its primary key; it's not an issue of the query affecting many rows or the row being difficult to be "found", as far as I can tell.

I've tried to look for other questions here and using Google, but all of them seem to point to the process being blocked by something else that's visible by sp_who2, but this isn't the case here.

EDIT 2015-12-15: The problem has resurfaced, and this time I was able to run sp_whoisactive as well. It shows my process, but nothing in the wait_info or blocking_session_id columns. The data it returns is the following (sensitive information removed):

dd hh:mm:ss.mss: 00 00:00:29.016
session_id: 85
sql_text: <?query --
insert into MSmerge_cpmv_66969A68BA3C4DFE9CFC3A88E87970FD (publication_number, tablenick, rowguid, partition_id)
            select distinct 1, @tablenick, bt.rowguid, bt.partition_id
            from (  select [BlockedTable].[blocked_table_id], [BlockedTable].[for_replication], [BlockedTable].[rowguid], msp.partition_id from inserted [BlockedTable] , dbo.MSmerge_partition_groups msp where  (msp.publication_number = 1 and ( ((blocked_table_id in (select blocked_table_id from dbo.GetFilteredIDsForUser(msp.[SUSER_NAME_FN] )) and for_replication = 1)) )) ) as bt 

--?>
login_name: MyUserName
wait_info: NULL
CPU: 28,813
tempdb_allocations: 0
tempdb_current: 0
blocking_session_id: NULL
reads: 25,507,251
writes: 9
physical_reads: 0
used_memory: 3
status: runnable
open_tran_count: 2
percent_complete: NULL
host_name: MyHostName
database_name: MyDatabaseName
program_name: .Net SqlClient Data Provider
start_time: 2015-12-15 10:44:49.200
login_time: 2015-12-15 10:44:49.183
request_id: 0
collection_time: 2015-12-15 10:45:18.237

It's possible that I haven't understood how to read the output of sp_whoisactive, but I've not found any information on that. The existing answer here (as of 2015-12-15) explains how to gather data and how to fix the blocking, but not how to read the output.

The query shown in the output seems to mean it has trouble inserting the new data in partitions for our merge publication. The publication is set to precompute partitions. Could it be that this is what's blocking it?

What else may be blocking the query in such a way?

2

I will prefer to track blocking with any of 2 below rather than SP_who2 or Activity monitor:

  1. Use Sp_whoisactive which is a procedure written by Adam Machanic. Once created , you can gather the data to analyse the blocking using:

a. Create one destination table

b.Create Your Loop to Periodically Log Data

c.Set Up Your Query to Look at the Results

On how to get these details captured, read here.

  1. You can use SQL Server Blocked Process Report Viewer written by Michael.J swart which apart from finding help you in analyzing the blocking root caused in very helpful manner.

You can read the article Okay, You’ve Found Blocking, Now What? to get more detailed info.

Hope these 2 will help as they have been doing us for quite long.

2
reads: 25,507,251
writes: 9
start_time: 2015-12-15 10:44:49.200
collection_time: 2015-12-15 10:45:18.237

Your session is not blocked. It just have to do a tonne of reads before applying any write. Large data, perhaps a blocking operator like a sort or a HT. It likely didn't yet got to the INSERT part.

This is basic performance issue. Start from How to analyse SQL Server performance.

PS. Is this a cartesian join?

from inserted [BlockedTable] , dbo.MSmerge_partition_groups msp 
where  (msp.publication_number = 1 and ( 
   ((blocked_table_id in (... )) and for_replication = 1)) )) 
  • That query seems to have been generated by the replication service, so I have no control over it. – George T Dec 16 '15 at 10:34
  • Could you explain the acronym "HT", please? – George T Dec 16 '15 at 10:34
  • HT: Hash-Table. – Remus Rusanu Dec 16 '15 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.