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