Before you run your update:
SELECT @@SPID;
Now, run the update, if it is not completing quick enough, then in another window run:
SELECT status, command, wait_type, last_wait_type, blocking_session_id
FROM sys.dm_exec_requests
WHERE session_id = <@@SPID from above>;
If you get a value in blocking_session_id, then you can run the same query for that SPID. You can also say:
DBCC INPUTBUFFER(<blocking spid>);
(Well, as long as you're not on SQL Azure.)
If this doesn't yield a clue, then it's possible the blocking statement was actually at a different point in a larger transaction. You'll have to track that down by figuring out where that spid is executing from and whether it's a batch of ad hoc T-SQL, a stored procedure, etc. There's not really any magic "look here!" we can provide you.
If it's not getting blocked, you may find relevant info in the other columns that can help determine why it's slow (e.g. perhaps it is waiting on a ginormous log autogrow event).
Unfortunately there is not much you can do about this, this behaviour is by design. The problem manifests itself when user sessions time out because the report is taking too much time. You can try to improve the reports, or configure the session timeout to be a bit longer than the longest running report
See this link for an explanation about the why and some ways to work around it.
You can increase the session timeout as some sort of workaround, the script is documented in the article.
From the article:
However, there is one scenario where the ping doesn't work, and that is during the inital report execution. The problem is that while the report is being executed, the user's session is locked (as we are populating the user's temporary snapshot) and the keepalive from the viewer control will be blocked. Normally, this is not a problem because report executions aren't supposed to take a long time and quite often they finish before the session timeout hits. Unfortunately, there are some cases where reports (for whatever reason) take an incredibly long time to execute. What happens in this case is that the user's session is aged out while the report is being executed, resulting in all sorts of strange behavior.
Best Answer
sp_releaseschemalockis an undocumented external proc which simply releases the schema lock:As per our past experience on this, we witnessed the same but never had the issue with
sp_releaseschemalockbeing a culprit.Kindly check the complete details when checking the blocking. That is ,you should not completely ignore the one that is getting blocked rather than completely dependent on blkd_by_spid, as blkd_by shows sp_releaseschemalock as culprit.
Also would like you to refer this
As mentioned, you need to go through the settings for the query getting executed via linked server because the primary server wont have the required statistical information when fetching the same from other server through its linked query.