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
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?