SQL Server Update Statistics

Updating the statistics of all the tables regularly in your SQL Server Database is an integral part of Database Maintenance. This can be achieved in many ways. We can do this through a script or create a sql job scheduled to run at a certain time.

SQL Server also has a database option called "Auto Update Statistics" which by default is True. This feature follows a basic algorithm as to when the statistics of a table is to be updated . One drawback of this setting is if certain set of conditions are satisfied for a table then it starts updating the stats at that time. This could be in the middle of the day at peak hours and hence could create perforrmance issues in production , so it is sometimes safe to disable this and do the update statistics through a script or a sql job for a very large database. One of the easiest and fundamental steps involved in sql query optimization is updating the statistics of all tables involved in the query. Updated stats help the SQL query optimizer to generate optimized query plans for queries. The SQL Server maintains information like number of data pages used by each table, the total row count of each table and the number of DML operations which affected the keys since the last statistics upgrade. Besides this SQL Server also maintains index statistics To determine whether an index has it's stats updated we can run the following command: DBCC SHOW_STATISTICS (table_name , index_name) To Update the Stats on a table the following command can be run : UPDATE STATISTICS <table name> WITH FULLSCAN The above command will scan all the rows of the table to get the updated statistics.     UPDATE STATISTICS <table name> WITH SAMPLE 50 PERCENT    The above script can be run for very large tables in case we want to specify a percentage of the total no of rows. We should also update statistics for all tables in a database when we are upgrading from any version to a higher SQL Server version