update query is slow

  • 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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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.

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

    This is one of main downsides of merge replication.

    -- Gianluca Sartori

  • 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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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.

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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

  • 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

  • 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.

  • 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

  • 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.

  • 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

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

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