0

We have an instance of SQL Server which has multiple databases. A process in one database seems to be blocking a process in another database. When I look in activity monitor I can see a header blocker (A one in the header blocker column). This seems to be blocking other processes in different databases. I can see their ids in the blocked by column when I select one from the drop down. Am I correct that it is cross database blocking? I didn't think this was possible. They are all running exactly the same stored procedure, but they have their own instance in each database. They are doing updates and inserts, but only within their own databases.

eg

UPDATE  SCA
SET SCA.date_last_read_only = TDR.date_seen
FROM [dbo].[SINGLE_CERT_ACC] SCA
INNER JOIN #TMP_DELTA_READONLY TDR
ON SCA.id = TDR.id
SET @RecsUpdated = @RecsUpdated + @@ROWCOUNT
5
  • 1
    Are you executing the same SP twice (or more) in the same server?
    – McNets
    Commented Feb 14, 2020 at 17:23
  • They have the same name but different instances in each database.
    – SQLMIKE
    Commented Feb 14, 2020 at 17:29
  • 1
    I mean, could #TMP_DELTA_READONLY or [SINGLE_CERT_ACC] be the cause of the block?
    – McNets
    Commented Feb 14, 2020 at 17:30
  • yes I think you are correct
    – SQLMIKE
    Commented Feb 14, 2020 at 17:34
  • A lot of times that could be index related, or lack of an index really, along with parallelism. What's the query plan look like? Commented Feb 15, 2020 at 0:07

1 Answer 1

0

On further investigation I believe the update itself might be causing the locking. It is on a large table 500+ million rows. As I understand it the table will become locked as soon as SQL starts to find the join. Perhaps the way activity monitor displays the locking is a little misleading though

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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