StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdate Statistics Strategy after Index Maintenance Activity
text
Body
copied!<p>Since Index Rebuilds will update statistics data for the concerned indexes with full scan, so it is not advisable to update statistics on those indexes using the Update Statistics with default sample size. This will worsen the statistics data captured. In that case, when we are doing a statistics update operation on all the tables in the database, is it a correct strategy to eliminate those indexes from the update process for which the indexes are already up to date(based on the assumption that we are running the Index rebuilds before the statistics update). For example, lets say that I am using the following cursor to get a list of all indexes in the DB for which update is required:</p> <pre><code>SET @index_names = CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT NAME ,indid ,rowmodctr FROM sys.sysindexes WHERE id = @table_id AND indid > 0 ORDER BY indid </code></pre> <p>and use the following condition to check if we need to update statistics:</p> <pre><code>IF ((@ind_rowmodctr <> 0)) </code></pre> <p>But this will update statistics for all the indexes irrespective of whether an index already has its statistics updated due to a rebuild.</p> <p>Now assuming that we are running the Index Rebuild task and the statistics update task on the same day and in that order, can we use the following filter to eliminate the indexes which has already been rebuilt:</p> <pre><code>SELECT NAME AS index_name ,CONVERT(VARCHAR(10), STATS_DATE(object_id, index_id), 111) AS statistics_update_date FROM sys.indexes WHERE object_id = OBJECT_ID('CM_Project') AND CONVERT(VARCHAR(10), STATS_DATE(object_id, index_id), 111) <> CONVERT(VARCHAR(10), GETDATE(), 111) </code></pre> <p>If this not a correct/optimal way to achieve this, can you please suggest a standard way to get this done.</p> <p>Thanks. Soumya</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload