SQL Server Performance

distribution server in grave peril

Discussion in 'Performance Tuning for SQL Server Replication' started by daustinash, Apr 3, 2007.

  1. daustinash New Member

    I have a distribution server with the CPUs pegged at 100%. Every night between 3AM and 7:45AM, jobs running on the publisher create what I presume is an overload of activity, causing the log reader agents on the distributer to wait for hours to read the log (my monitoring app, symantec precise, reports it as NEWTORKIO wait). Distributer agents time out, subscriptions go out of synch, all the bad stuff you would expect.

    Then at 7:45AM, the floodgates open and a torrent of transactions bog down my distributer till 4pm. All the above bad stuff continues.

    I'd like to track down the jobs (not necessicarily sql scheduled) that are causing this flood, and want to start by viewing the insert/update/delete transactions and find out which articles are getting pounded.

    Is there a preferred method for doing this, such as a log reader app like Lumigent or system tables/views/procs I am not aware of?

    Thanks in advance,

    dnash


    daustinash
    dnash@verndale.com
  2. bradmcgehee New Member

    Before purchasing a third party tool, I recommend that you use Profiler to get a trace of what is happening on the publisher, distributor, and subscriber servers.

    Also, is this a new problem, or has it been happening for quite some time?

    --------------------------------
    Brad M. McGehee, SQL Server MVP
    http://www.sqlbrad.com
  3. MohammedU New Member

    Is the server dedicated sql server distribution server or some thing else also on the server?
    Only distribution server should not cause this issue unless you have hundreds of publications...

    Check through the task manager.... to make sure sql is using 100% cpu not something else...
    Are there any virus software running?
    Run the profiler as suggested for long running queries and high cpu?

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page