sp_MSunmarkreplinfo blocking queries

Question

I'm investigating the slowdown of a system and am finding sp_MSunmarkreplinfo is blocking.

It is trying to get a schema modify lock but is being blocked by other things. This is then blocking a lot of other processes on the system that are trying to get schema shared locks. It is different SPs that are blocking sp_MSunmarkreplinfo so I don't think there is anything that can be done about that.

I would have thought that sp_MSunmarkreplinfo is only called when removing a table from replication? Should I be looking for someone/something that is trying to alter replication or is this sometimes called as part of the replication processing?

Answer 1

Hi  NigelRivett,

>I would have thought that sp_MSunmarkreplinfo is only called when removing a table from replication? 

Yes. You are right.

>Should I be looking for someone/something that is trying to alter replication or is this sometimes called as part of the replication processing?

Yes. That is not one part of the replication. You should look for the cause.

Execute the code as next:

Select * from master.sys.sysprocesses 

And you will get a detail information of the blocking. You can choose to wait or kill the spid.
And there are 5 blocking types as next:

Note:code as next will be helpful

select * from sys.sysprocesses
where blocked <> 0
or spid in (Select blocked from sys.sysprocesses)

select session_id,blocking_session_id,qt.text,start_time,
status,command,DB_NAME(database_id) as DATABASE_NAME,
OBJECT_NAME(user_id) AS USERNAME,
wait_resource,wait_time,wait_type,open_transaction_count
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where (blocking_session_id <> 0)        

select session_id,wait_duration_ms,wait_type,
blocking_session_id,resource_description
from sys.dm_os_waiting_tasks
where blocking_session_id is not NULL
order by session_id



More information : troubleshooting-blocking, sys-sysprocesses-transact-sql

BR,
MIAOYUXI

   

Answer 2

Hi NigelRivett,

Is the reply helpful?

BR,

MIAOYUXI

Answer 3

Thanks.

I found this by tracing the blocking chain. Can't kill anything because they are user queries. The block is held for a max of 30 seconds but often a second or two. Probably can't do anything about the things that are blocking sp_MSunmarkreplinfo. The client says that no one is doing anything to replication. A table is changed maybe every couple of weeks but nothing with the frequency we are seeing here. Looking at what happens during a day it looks like this is run a few times an hour.

At the moment I'm logging all processes when a particular SP is blocked - that runs over once a second so should get them all. I'm trying to set up a profiler trace to see if I can get more info about when it is being executed and what follows it.

Answer 4

Hi NigelRivett,,

If you can’t kill anything， the most recommended way is to optimize your query logic to reduce blocking resources, including modifying the statement itself to reduce complexity, modifying the table design, and adjusting the index.


BR,
MIAOYUXI

Answer 5

Thanks. Not really a good solution. What we want is to stop replication blocking everything on the database.

The query I've been asked to investigate completes in 0-20 millisecs when it's not being blocked.

I need to know why sp_MSunmarkreplinfo is continually being called and why it's taking schema modify locks.

Answer 6

After further investigation it looks like sp_MSunmarkreplinfo is running wvery 5 minutes. It's called twice for each of 26 tables.

Oddly for the 15 minutes it was being traced it was run as above then on the third batch it ran 6 times for each table.

There are a lot of references to MSmerge_... tables in the statements being run. Don't know if that means merge replication has been set up.

Anyway next is probably to get a script of the replication setup.

Answer 7

Suggest you ask professional engineers for help, and they will deal with your problem separately and confidentially:

assistedsupportproducts

Answer 8

This is very unusual. Do you have multiple publications on the same server? Any history we should know - like being part of an upgrade?

Answer 9

Thanks.

Just reviewed the setup and there may be some amendments to this. Just checking. Think this is the publisher for some things and subscriber for others. It looks like the issue is with this acting as a subscriber. I've made the changes below.

It's set up as merge replication with this database as a subscriber along with two other subscribing servers and the subscribers allowed to make and upload changes - although I'm assured that never happens.

This server is set up as a suscriber for the tables which are causing problems but has 3 other publications set up from this server.

What I would like to know is whether it is normal for merge replication allowing changes both ways to run sp_MSunmarkreplinfo on the subscriber and take schema modify locks. (If so that sounds like a bug - I could understand it holding a shared lock).

On top of that, if it was changed to disallow changes at the subscriber would that stop the behaviour. That would seem like a change we might be able to make with little risk.

I am assuming that transactional replication wouldn't have the issue as it works off the transaction log but that would be a large change to their architecture.

I don't have a system to test this on but I'm going to ask the client to set one up as they will probably need it. It's running on terrabytes of data and many transactions a second so anything like this has an impact to their users.

Answer 10

Something is not setup correctly here.

How many publications and how many subscribers?

Are you using pre-computed partitions?

For a high rate of transactions on an OLTP server merge replication is problematic. Are you agents continuously?

Answer 11

>> For a high rate of transactions on an OLTP server merge replication is problematic.

That was my thought.

It's running on sql server 2016

When I first saw the system most things seemed to be running in milliseconds and looked like it was well designed but I'm beginning to wonder.

I don't know about pre-computed partitions but I would guess that it's enabled.

The replication transport doesn't seem to cause a problem, it's just the sp_MSunmarkreplinfo trying to get a schema modify lock.

Answer 12

So the problematic database is a subscriber to merge replication with 26 tables set up to allow and propagate changes from the subscriber.

This seems to cause sp_MSunmarkreplinfo to run on the subscriber for each of the tables and to attempt to gain a schema modify lock so blocking everything accessing that table even dirty reads.

This seems like a bug as it should only need a (at most) shared lock as no schema changes are taking place.

If this was changed to not allow changes on the subscriber would these locks still be taken. I suspect it's a feature of the push from the publisher so would still be an issue.

I'm trying to find out why it is set up like this. something to do with their previous transactional replication changing and merge replication being the easiest way for them to implement what was needed after the upgrade. I've asked what specifically was the effect that was no longer available.