SQL Server Performance

Two quick questions...

Discussion in 'Performance Tuning for SQL Server Replication' started by LongHairedGit, Aug 11, 2005.

  1. LongHairedGit New Member

    1. I have an application that serves users around the world, and hence uses a SQL Server locally in each centre and a "central" server in the main site. The users always update the central database, and poll the local slave. Polling is done every 3 seconds as the data is time critical.
    We've just upgraded from SQL 6.5 to SQL 2000, and the "user experience" has degraded, even though the new servers are much faster than the old ones.
    By memory, there is a setting somewhere that causes SQL to "batch up" transactions, and for SQL 6.5, I turned this off or set it to 1 or something.
    Is there something similar for SQL 2000?

    In the end, when a transaction is done on the central server, I want it sent immediately to the slaves. The server is fast enough to handle the load created by this.

    2. The replication monitor for this same database is showing many more "transactions" being replicated than I would have expected. How can I tell what transactions are being replicated? The distribution database is all binary data, so I can't tell there. Not all tables in my database are replicated, but all columns of replicated tables are.

    Perhaps I should set up a dummy subscriber, and on that have the transaction log kept and then (somehow) read the transaction log?

    Surely there is a simple way to find out?

    ------
    Long Haired Git
  2. satya Moderator

    May be check at network related counters and issue might be cropping up from that side, also ensure SQL 2000 has required levels of service pack and hotfixes with schedule of reindexing and database consistency checks.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. LongHairedGit New Member

    Yeah, all that is under control.
    The replication isn't "slow" by normal means.
    Its just that I got sub-second in SQL 6.5, and now its several seconds.
    Most people and most apps wouldn't mind, but for this application, it can matter....

    ------
    Long Haired Git
  4. satya Moderator

    In SQL 2000 memory is managed dynamically as per the availability from OS and it will release only when it is requested by OS processess. I believe PERFMON stats will be a good information to assess the situation where exactly it is having performance issue.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. LongHairedGit New Member

    Not sure that response was meant for this thread.
    Anyway, my DBA found the "profiles" section for replication, and created a custom profile, where he found a 10 second "polling" interval.
    We've set it to three seconds and are seeing if that improves things.


    ------
    Long Haired Git
  6. satya Moderator

    Can you confirm whether the SQL is a dedicated server with no resource sharing by any other application.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page