Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Transaction replication lock issue Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 3:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:33 AM
Points: 50, Visits: 516
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


Post #1562870
Posted Friday, April 18, 2014 5:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1562973
Posted Monday, April 21, 2014 9:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:33 AM
Points: 50, Visits: 516
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

Post #1563529
Posted Monday, April 21, 2014 10:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1563555
Posted Monday, April 21, 2014 10:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1563559
Posted Monday, April 21, 2014 9:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:33 AM
Points: 50, Visits: 516
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

Post #1563664
Posted Monday, April 21, 2014 9:19 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:33 AM
Points: 50, Visits: 516
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




Post #1563666
Posted Tuesday, April 22, 2014 4:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1563749
Posted Tuesday, April 22, 2014 4:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:11 AM
Points: 7,128, Visits: 6,291
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1563756
Posted Thursday, April 24, 2014 7:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:33 AM
Points: 50, Visits: 516
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








Post #1564639
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse