Index update statistics

Is this page helpful? Please rate your experience Yes No. Any additional feedback? Submit and view feedback for This product This page. View all page feedback. Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services.

Privacy policy. Updates query optimization statistics on a table or indexed view. 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. The actual percentage or number of rows the query optimizer samples might not match the percentage or number specified. For example, the query optimizer scans all rows on a data page. SAMPLE is useful for special cases in which the query plan, based on default sampling, is not optimal. In most situations, it is not necessary to specify SAMPLE because the query optimizer uses sampling and determines the statistically significant sample size by default, as required to create high-quality query plans.

Whenever there is a modification in the database, a cause i. This problem raised when indexes have multiple pages in which the logical ordering depends on the key value, and physical ordering does not match with the data file. Highly fragmented indexes can easily degrade the SQL query performance and begin to slow down the performance.

This huge number of allowed, but not recommended, indexes help us in covering and enhancing the performance of a large number of queries that try to retrieve data from the database table. The drawbacks of having too many indexes in the table include slowing down the data modification operations, due to the fact that all changes performed on the table should be replicated to the related indexes.

In addition, this large number of indexes require extra storage and should be all maintained, although some of these indexes are not used, hurting the overall performance, instead of the expected enhancement from it. So that, it becomes the main task for the database administrator to regularly monitor the usage of these indexes, to identify the indexes that are badly used, or not used, and drop it or replace it with more optimal ones if required.

The indexes that are badly used include the ones that have a large number of write with few numbers of reads and a large number of scans with few numbers of seeks.

SQL Server keeps the information about the index usage statistics automatically in the system tables and flushes that data when the SQL Server service is restarted.

To access these system tables, SQL Server provides us with the sys. The T-SQL script below uses the sys.

This information includes the name of the table, on which the index is created, the name and type of that index, the size of the index, the number of seeks, scans, lookups and updates performs on the index and finally the last seeks, scans, lookups and updates date, as shown below:.

The number of Seeks indicates the number of times the index is used to find a specific row, the number of Scans shows the number of times the leaf pages of the index are scanned, the number of Lookups indicates the number of times a Clustered index is used by the Non-clustered index to fetch the full row and the number of Updates shows the number of times the index data is modified.

The result in our case will be like the below:. The previous readings give you good indications about the database indexes usage, but you need to dig deeper before deciding to remove or replace an index.

You can use the previous results in conjunction with the result of the sys. Similar to all other DMOs, the sys. Rebuilding, reorganizing or disabling the index will not affect its statistics. An index will have an entry in the sys. The below T-SQL script can be used to query the sys.