SQL Server Performance Forum – Threads Archive

First Query TimeOut – Replication

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

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.

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

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.

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 …

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.

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

See this kBahttp://support.microsoft.com/kb/315521 too. 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.

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

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

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.

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

]]>