SQL Server Performance

First Query TimeOut - Replication

Discussion in 'SQL Server 2005 Replication' started by Hollywood, Apr 5, 2007.

  1. Hollywood New Member

    Hi all,

    We often meet problems of Timeout during first requests SQL on large Tables when a publication is activated on the DB. It seems after analysis that this Timeouts is largely due to the time which is used by the requests to create/update their statistics, this time is actually stretched out due to the tables of replications ( Ms_Merge, ...). Are there actions which we can take to avoid these timeouts?

    Furthermore, why still have updates/creations of statistics during our first requests after having made a complete Rebuild of index of the DB and an update of the statistics ?

    Best Regards
  2. satya Moderator

    Have you tried to increased the query timeout option?


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  3. Hollywood New Member

    Yes, from 30 --> 60 sec, but somes query (just 1 insert on a 1000 rows table for exemple) takes 2 minutes to be completed ...

  4. satya Moderator

    Have you perform update stats and recompile of SPs on that table?>

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. Hollywood New Member

    Yes, I perform a full Index Rebuild, update Stat and then I execute a query (insert) and it take 2 min. to be completed, the trace show us that the statistics are rebuilded before the query complete ...
  6. MohammedU New Member

    May be auto update stats is kicking off but it is based on row modifications not the replication...you can try disabling autoupdate stats during this time...


    Following article is for sql 2000 but it applies to 2005 also...

    Merge Replication Performance Tuning and Optimization
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/mergperf.mspx


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  7. Olu New Member

    is this coincidental (ie. the fact that it only shows slow performance when accessing large tables), and it is indeed the case that during "cleanup" UDATE STATISTICS run post cleanup all the time :-( but with NORECOMPUTE, which is good(for the purpose of limiting unecessary cpu high usage) but if the cleanup jobs is scheduled to run every 5 mins you could effectivly see high cpu usage all the time. (see sp_MSdistribution_cleanup)




    Olu Adedeji
  8. satya Moderator

  9. Hollywood New Member

    Thanks for your support,

    So , When I Rebuild All Index and then I execute an Insert Query, this query should not update/creates statistics (already up to date with the rebuild) ?

    Regards
  10. Olu New Member

    no problem Hollywood, index rebuild rebuilds stats also..however if sp_MSdistribution_cleanup is running frequently, this does stats rebuild of MSrepl_Command and MSRepl_Transaction, which could be whats hindering cpu usage. so need to check what the frequency of distribution cleanup is. and check using Processor object (with %processor_time) measuring all processors to determine (with profiler if this is what is causing the slow performance)

    HTH

    Olu Adedeji
  11. MohammedU New Member

    You can disable auto update statistics for this table if sp_MSdistribution_cleanup is causing to update stats frequently for MSrepl_Command and MSRepl_Transaction tables...

    Run the UPDATE STATISTICS with NORECOMPUTE option...

    NORECOMPUTE
    Specifies that automatic recomputation of the statistics should be disabled. If this option is specified, the Database Engine continues to use old statistics even as the data changes. The statistics are not automatically updated and maintained by the database engine, which might produce suboptimal plans.


    OR
    Rebuild the index using ALTER INDEX with STATISTICS_NORECOMPUTE option...

    STATISTICS_NORECOMPUTE = { ON | OFF }
    Specifies whether distribution statistics are recomputed. The default is OFF.

    ON
    Out-of-date statistics are not automatically recomputed.

    OFF
    Automatic statistics updating are enabled.

    To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

    Important:
    Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries that involve the table.



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. Olu New Member

    Just a quick one to point out that update statistics with norecompute is hardcoded into sp_MSDistribution_cleanup :-( this was what I was alluding to earlier.

    Olu Adedeji

Share This Page