March 25, 2010 at 9:02 am
In the effort to provide protection and performance, how often is too often to run ReOrg and Update Statistics maintenance tasks?
http://msdn.microsoft.com/en-us/library/ms190397.aspx
Microsoft article states:
“Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application.”
Currently, the tasks are taking seconds to execute every 8 hours and there has been no performance degradation.
March 25, 2010 at 4:26 pm
In one environment, we ran update stats hourly and rebuilt indexes nightly.
Others we rebuilt indexes weekly and updated stats nightly.
It will depend on your environment.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 26, 2010 at 7:26 am
In my environment I allow auto update stats to do the updating of statistics for the most part. However, there are certain situations where procedures actually update statistics in the middle of the procedure after the insert statement to the table. The reason this causes a statement recompile on the following statements in the procedure that use that table which is exactly what we want. The reason being that there were no stats on the new values that were inserted into the table at the beginning of the procedure.
A good Microsoft paper on stats:
http://technet.microsoft.com/en-us/library/cc966419.aspx
However, in this scenario the procedure is being called per batch, so it isn't having to recompile thousands of times per day. The procedure and/or statements are only executed maybe 100 times per day at the most, so recompilation time is insignificant. The real time killer in this is actually the update statistics statement, which if done on the entire table takes about 15 seconds and climbing with volume. This being the case we only update the statistics for the specifically used indexes in the procedure.
Update statistics explicitely generally makes sense on identity fields or foreign keys with identity fields because the new numbers are out of range for the existing statistics.
Regards,
Toby
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply