SQL Server Performance

low performance in sql server 2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by lgl, Nov 30, 2007.

  1. lgl New Member

    We have this environment:
    2 servers with sql server 2005 where we have implemented a replication system. Server A is the distributor and publisher and Server B is the subscriber. There are some merge publications. There are some external programs that work with publication database either with server A or server B, inserting and updating records. This system has been working properly until two weeks ago, when performance in server A has suffered a progressive degradation, increasing time for any DB operation (select, insert, update, and so on). If the same programs work with server B, performance is good and replication system works properly. The only way to increase performance in DB operations with server A is stopping Agent service. when I restart again agent service performance goes down again and timeouts in DB operations with server A goes back. All tables are well indexed, every week maintenance plans for reorganize indexes are executed succesfully, number of records in affected tables is maintained with massive deletes every weekend. The degradation has not been sudden, but day by day, since two weeks ago. Machine has been restarted, Sql server and agent services have been restarted. I,m losing my mind.....
  2. ndinakar Member

    Have you run perfmon counters to see if IO's are taking longer or if SQL Server is being starved of memory? If load on ServerA is going to be higher than B, moving distributor to B might help..
  3. satya Moderator

    Timeout means the system is not able to copeup the current load, see you are getting any blocking or queries are not optimized.
    In this case make sure to schedule the reindexing and check the hardware too, as referred you could take up with PERFMON to see what kind of systems resources usage is functioned.

Share This Page