update query is slow

Question

update query is slow

manju.ccc

SSC Eights!

Points: 973
More actions
December 3, 2014 at 11:43 pm

#299211

Dear friends,
I have a table which is using merge replication.
So when I update the table by simple update query then i'll take more time.
Then I run same query by disabling merge triggers on the table.
Then Its working fine. Any guess.
I really appreciate your quick response.
Thanks.
Manjula

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

manju.ccc (12/3/2014)
Dear friends,
I have a table which is using merge replication.
So when I update the table by simple update query then i'll take more time.
Then I run same query by disabling merge triggers on the table.
Then Its working fine. Any guess.
I really appreciate your quick response.
Thanks.
Manjula

You have not asked a question.

You merely appear to have stated that inserts to tables with triggers take longer than inserts to those which do not. Which I would have quite easily 'guessed' myself.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

manju.ccc SSC Eights! Points: 973 More actions · Answer 2

Thanks for your quick reply Mr. Phil

I know triggers slow the update query.

But in my problem You know when we create a merge replication on a table it adds three triggers.

I notice my table getting slow because of those triggers. (Its running well when disable those triggers)

I use merge replication for several databases but this problem occurs only one database.

Do you want any more details?

Thanks for your quick response.

Manjula.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

It is expected. Triggers slow down writes to the table.

This is one of main downsides of merge replication.

-- Gianluca Sartori

manju.ccc SSC Eights! Points: 973 More actions · Answer 4

Hi spaghettidba,

Thanks for your reply.

Ok I got your point.

But I have two servers with same environment.

This problem occurs only a database of one server other one is ok

One server taking 10 - 20 seconds for a update a query. But other server it'll run within 1 or seconds.

Thanks

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

manju.ccc (12/4/2014)
Hi spaghettidba,
Thanks for your reply.
Ok I got your point.
But I have two servers with same environment.
This problem occurs only a database of one server other one is ok
One server taking 10 - 20 seconds for a update a query. But other server it'll run within 1 or seconds.
Thanks

Two separate servers with identical specs and loads?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

manju.ccc SSC Eights! Points: 973 More actions · Answer 6

Yes Phil all servers are identical. There are three servers in three different locations. One is publisher and other two are subscribers. OS is windows 2012 and SQL is 2012 SP2. The problem occurs only one subscriber end.

** The load of this server is comparatively high than other two servers

Thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

manju.ccc (12/4/2014)
Yes Phil all servers are identical. There are three servers in three different locations. One is publisher and other two are subscribers. OS is windows 2012 and SQL is 2012 SP2. The problem occurs only one subscriber end.
** The load of this server is comparatively high than other two servers
Thanks.

Then somehow you need to determine whether the additional load is responsible for the poor performance. Perhaps by experimenting at a time when the load is low.

If not, this most probably comes down to some environmental issue – database settings, server settings etc.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Check the wait stats before and after the query runs on the slow machine to understand what is causing it to slow down. Also, compare the execution plans between the two machines. Are they the same? Is there blocking or other resource contention? Are the statistics updated in the same way on both machines? Do both machines have the same amount of memory allocated to SQL Server? In fact, are all the server settings the same on both machines? Database settings?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

manju.ccc SSC Eights! Points: 973 More actions · Answer 9

Hi friends,

In my problem I found following data.

select count(*) from msmerge_contents with(tablockx)

select count(*) from MSmerge_genhistory with(tablockx)

msmerge_contents returns 745 562 rows

MSmerge_genhistory returns 3459 365 rows

That means MSmerge_genhistory is having more tham 3 million data.

Does it reason for slow updates?

Shall we clean them?

Thanks

manju.ccc SSC Eights! Points: 973 More actions · Answer 10

Hi Grant Fritchey,

I checked all configurations in both servers. But I didn't notice any differences.

What data do I need to trace under wait stat commends.

Thanks.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 11

You can use the dmv, sys.dm_os_wait_stats. If you query that before and after running your query, you'll get a pretty good idea of what is causing things to slow down while the query is running.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

manju.ccc SSC Eights! Points: 973 More actions · Answer 12

Hello Hi Grant Fritchey,

I run sys.dm_os_wait_stats and out put as follows

wait_type --> waiting_task_count --> wait_time_ms --> max_wait_time_ms --> signal_wait_time_ms

SOS_SCHEDULER_YIELD --> 289264119 --> 8132367310 --> 2703 --> 8134163552

PAGELATCH_EX --> 11417759 --> 45420372 --> 1646 --> 24821909

LOGMGR_QUEUE --> 6625479 --> 80925588 --> 255 --> 256859

......

I really appreciate your reply.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 13

OK, so, the last two make sense, it's waiting on the disk and on the log. The first one suggests CPU use. So, we're kind of back to where we were unless you also get equivalent data points from the other server. And, look at the execution plans. If you're getting different behavior between two servers that are in ALL respects identical, something isn't identical and you have to identify that to understand what to modify in order to address the issue.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning