SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction replication lock issue


Transaction replication lock issue

Author
Message
qew420
qew420
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 633
Hello team,

I setup transaction replication between 2 servers and facing locks issue where when replication is running no one is able to access any tables on Subscriber
Now I changed the schedule to run only once a day at night time but in couple of months I need to run the replication after every 15 minutes
Please let me know the best way to resolve this issue( at this time I have 94 articles) speed/performance wise all looks very good and 100 transaction will take only lest than 2 minutes to copy over things but if the sync is running(or enabled) then I'm facing this issue

Memory/CPU plus over all hardware is very powerful

Please help

Thanks

Dave
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15150 Visits: 9006
What is the specific kind of locks that you are encountering?

How are your articles designed? Are they whole tables or queries? If queries, can you paste the offending ones here with an execution plan so we can look at them?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
qew420
qew420
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 633
Hello, thanks for the reply. please see below


Brandie Tarvin (4/18/2014)
What is the specific kind of locks that you are encountering?


(294ms)PAGEIOLATCH_SH:testdb:1(*)

If I do a simple record count on a small table, it is taking long time


How are your articles designed? Are they whole tables or queries? If queries, can you paste the offending ones here with an execution plan so we can look at them?


Only the tables are present
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15150 Visits: 9006
I do not think your issue is Replication. I think it is either I/O or bad indexes (or both).

A few things to look at on your side:

How wide is your table (how many columns and what is the predominant datatype)?

How long is your table?

How many indexes does your table have? You might have too few. You might have too many. If the total # of indexes is half or more of the total # of columns, you have too many. If you have 0 indexes, you definitely have too few.

Is your paging file the proper size? Does your hard drive(s) / SAN(s) have the proper amount of free space for reading and writing on both sides of the Replication connection?

Network problems are a possibility, but I would concentrate on indexes and I/O issues first.

EDIT: Get your server admin involved for the I/O issues questions.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15150 Visits: 9006
Hey, run this script on the Subscriber and see if you're getting lots of small waits or some (or lots) of long waits. Post the results, please.


select * from sys.dm_os_wait_stats
where wait_type = 'PAGEIOLATCH_SH';



Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
qew420
qew420
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 633
Take a look

wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
PAGEIOLATCH_SH 9333614 512244039 103128 1638672
qew420
qew420
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 633
Thanks Brandie,

I was thinking the same too but the problem is that after adding the Replication this issue is started

If I stop the replication everything completes in few seconds or minutes(depending on the job)

Some tables are huge also but still all was good before adding replication

I think there is somewhere problem with San too but before telling my Manager I need to make sure there is nothing on my side causing this issue

I can check the missing Indexes but again only counting the # of records .. system is taking so long
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15150 Visits: 9006
It doesn't matter that everything was good until Replication was added. When you added Replication onto the server, you added an extra load plus you added a whole bunch of storage needed for the tempdb and the regular db. Your I/O increased as well. Therefore, you have a bottleneck issue of some flavor.

I would run a Profile trace at the same time as you run Performance Monitor (the Windows version) while you have Transactional Replication turned on. Check all the related I/O counters in both and have the Profiler Trace include the (under TSQL) SQL:BatchCompleted, SQL:BatchStarting, (under Stored Procedures) RPC:Completed, (under Sessions) ExistingConnection, (under Security Audit) Audit Login, Audit Logout, and (under Transactions) TM: Begin Tran Completed, TM: Begin Tran Starting.

And if you still don't think that's the issue, I suggest you hire a local SQL Server consultant to come into your office for a couple of days to resolve your issues. There's no substitute for having someone on site who can look at your systems setup and then troubleshoot.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15150 Visits: 9006
qew420 (4/21/2014)
Some tables are huge also but still all was good before adding replication
...
I can check the missing Indexes but again only counting the # of records .. system is taking so long


If some of the tables are huge, then they probably aren't taking advantage of the indexes that exist. Replication does not necessarily use the same indexes as other queries do. And smaller tables could be blocked by the larger tables not moving well.


I think there is somewhere problem with San too but before telling my Manager I need to make sure there is nothing on my side causing this issue


I/O isn't just about hard drives and disk controllers. It is about reading and writing from the database. Hence the reason I suggest running PerfMon and Profiler together. Profiler now has the ability to hook the two traces together and tell you exactly where the bottleneck is. And if the indexes aren't being used, or there aren't indexes for the tables, then those alone would cause a bottleneck before it even gets to the SAN part. But the traces should tell you that.

There are a lot of moving parts in Replication. Where do you have your Distributor? Is it on the Publisher, the Subscriber, or is it a standalone server / instance?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
qew420
qew420
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 633
Hi Brandie,

Thanks for your assistance

The main issue is fixed now as there were missing Indexes, now things are running much better

But just for my knowledge what will be the solution of I/O wait, Latches if SQL DB is having no issues

So let's say after running PerMon or DMV's I have a report now what will be the next step, there is any SQL side things I need to check (DB is optimized already)

OR what are the pointers I need to report to SAN admin?

Let me know if there is a book or step by step guide with a solution of dealing with the I/O Wait etc

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search