Updating index statistics sql
Index statistics are used to help the SQL Server engine determine the cardinality of different column values in a SQL Server table.If you want to give SQL Server a fighting chance at picking an optimal execution plan then you need to make sure you maintain your index statistics.Modifications to the database can cause index fragmentation when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. According to Paul Randal if an index has less than 1000 pages and is in memory, don’t bother removing fragmentation. Other options are rebuilding indexes and reorganising indexes.This article will provide you with an overview of what statistics are and how to deal with out of date statistics. Index statistics contain information about the distribution of index key values.
The threshold is relative to the number of records in the table.SQL Server uses this information to determine what kind of execution plan to use when processing a query.As you insert, update, or delete rows in a table, the statistics on the updated table become out of date.There are a number of different ways to view when your statistics were last updated.The first method is to use SQL Server Management Studio.This is especially important for table that contains lots of rows.If you have out of date statistics then SQL Server may pick a less than optimal execution plan.With tables that contain millions of records, this means a lot of records need to be updated, inserted and/or deleted before SQL Server will update statistics automatically.The problem with out of date statistics The problem with having out of date statistics is that SQL Server might not know all the different possible values there might be for an indexed columns, and therefore the database engine will pick a sub-optimal execution plan.You can imagine how expensive it might be to create statistics by reading every row for a very large table containing billions of rows. It is important to understand when SQL Server updates your index statistics.The AUTO_UPDATE_STATISTICS database setting controls when statistics are automatically updated.