Most of these index types offer many options that can be used more effectively by the indices. But maintenance of indexes and index statistics should not be ignored either.There is a direct correlation between index options and index maintenance, as some options have an immediate impact on how often maintenance becomes necessary.
Index options are already defined when creating indices with the additional clause WITH , which is then followed in parentheses by listing the actual options. Let us discuss on the Index statistics and the maintenance of indices.
Maintenance of indices
Fill factor play an important role in maintaining the database. Frequent page splits, however, cause the physical order of the index pages to be out of sync with the index (which is fragmented), making the index less effective and, in the event of heavy fragmentation, even rendering SQL Server unable to use the index , To remedy this condition, it is necessary to rebuild the fragmented index, restoring the fill factor of all index levels. However, such a REBUILD is much more complex than a REORGANIZE , which is why making the decision of which variant makes more sense usually depends on the current degree of fragmentation , which is retrievable in the SQL Server Management Studio via the properties of the index.
Maintenance of Index and Statistics
For each newly created index, SQL Server automatically creates statistics that have the same name as the associated index. These index statistics provide the database engine with valuable additional information about how the data is distributed throughout the index, thereby helping to determine when and how to make the best use of this index.
However, this only works as long as the index statistics are current enough. If these are out of date, this means that the associated index is no longer used, regardless of the degree of fragmentation, since the database engine no longer trusts the statistics. There are several ways to keep index statistics up to date :
- If an index is rebuilt via REBUILD , the associated statistics are also updated since the entire index data must be read in anyway. This implies, conversely, that with regular rebuilding of the indices, the statistics are also automatically maintained. However, for most databases it is too time-consuming to regularly rebuild all indices with REBUILD.
- The database options allow you to set statistics to be automatically created and updated (either synchronously or asynchronously). Synchronous means that an outdated statistic is recreated if necessary and only then the corresponding query execution is continued. In the asynchronous setting, the execution plan is still created based on the old statistics, and the statistics are updated independently to make them available for the next query.
- While automatically generating statistics makes sense, it is important to keep in mind when automatically updating statistics. that these are only updated on a sample basis. If the distribution of the values is not uniform, this can, in extreme cases, lead to misleading statistics, which results in a less favorable execution plan for a query than that which would have been created using an older statistic based on complete data.
- If index statistics are explicitly updated via T-SQL code, a parameter can be used to control whether they are based on samples ( SAMPLED ) or on all data ( FULLSCAN ). The statement is UPDATE STATISTICS Person.Person IX_Person_LastName_FirstName WITH FULLSCAN . In order not to spend too much time maintaining the statistics, it is important to find out which statistics require a FULLSCAN update and when sample-based statistics are sufficient.
Maintenance Jobs
Indices and statistics must be regularly maintained.However, there are no manufacturer-mandated maintenance intervals, so a key problem in maintaining indexes and statistics is finding out what needs to be done when.While fragmentation is the key consideration for indexes, the number of rows changed since the last update plays a key role in updating the statistics.Both can be read out of the system tables with clever queries.
When deciding to develop your own maintenance script, it is important to at least sample-update index statistics before any index rebuilds, so that the FULLSCAN updated statistics are not overwritten by inaccurate sample-based ones.
Whether you develop your own maintenance script or use a completed routine , you need a SQL Server Agent job that starts regular maintenance.At least for databases that do not need to be available 24 hours a day, one common approach is to perform a "small maintenance" daily (in a nocturnal maintenance window) focused on the most fragmented indexes (and depending on the size of the available maintenance window maybe only indexes reorganized and perhaps some statistics updated on a sample basis).This is then complemented by a "big maintenance" that runs every weekend and more extensive maintenance (index rebuilds, updating statistics with FULLSCAN etc.)
Conclusion
Index compression or settting fill factor, significantly increase the effectiveness of indexes. Choosing the right option is the key to regular maintenance of indices and related statistics. The blog covers about the maintenance of index and statistics in SQL Server and how can you increase the effectiveness of indexes.
EmoticonEmoticon