As I mentioned earlier, outdated statistics and indexes can adversely affect query performance as the query plans are based on erroneous data. You can use T-SQL scripts to view table statistics and index fragmentation and if necessary, update those statistics or reorganize or rebuild fragmented indexes.

For all versions of SQL Server or Azure SQL, you can run scripts to update statistics and rebuild indexes.

Within SQL Server Management Studio you can create maintenance plans to update statistics and rebuild indexes for SQL Server on-premise, or in a VM. You can schedule these types of maintenance tasks for Azure SQL using an Azure runbook.

By querying sys.dm_db_index_physical_stats joined with sys.indexes, I can see the fragmentation of indexes on a particular table, in this case customer. I've just created an index on the code column so there is 0% fragmentation.

Now, if I insert just shy of 1 million records with a randomly generated customer code field and re-run the query, the resulting fragmentation is 86%. You have two options when addressing index fragmentation. You can either reorganize the index or you can rebuild it. Microsoft suggests, as a rough guide, that if row store index fragmentation is greater than 30%, you should rebuild the index, and if it's between 5 and 30% fragmented, then reorganize the index.

You can rebuild or reorganize all indexes on a table at once by using the ALL keyword instead of the index name. When a column store index's fragmentation exceeds 20%, it is recommended to reorganize the index. You can rebuild and reorganize indexes on an SQL Server hosted database through SQL Server Management Studio by right-clicking on the index and selecting Rebuild or Reorganize from the context menu.

You can use the DBCC SHOW_STATISTICS command to display statistics for an index. The command returns three result sets. The first one displays the name of the index, the number of rows and the number of rows that were sampled to calculate the statistic.

Steps refers to the number of divisions in the histogram breakdown, which is the third result set. Density is one divided by the distinct values, but is not used by the query optimizer and is only displayed for compatibility for versions of SQL Server prior to 2008. String index means that separate statistics are created when the first column of an index is a char or varchar type of column, for better performance when using the like operator.

If the statistics have been filtered with a predicate that will be displayed in the filter expression column, and as there is no filter expression, the unfiltered rows are the same as the total rows. In terms of the density vector, all density is calculated as one divided by the number of distinct values.

In this case, the index is on a single field called code, which is a varchar 18 and the table has a primary clustered index on an integer identity key. The average length of all code values is 13 more or less, and when we add in the four-byte primary key, we get 17 bytes. The histogram breakdown is the distribution of values over a series of intervals, in this case, 166, but the maximum number of intervals SQL Server will display is 200.

The range hi key is the upper bound of the interval. Range rows are the number of rows in the interval, excluding the upper bound. EQ_rows are the number of rows that equal the upper boundary, while the distinct_range_rows is the number of distinct values within the interval, also excluding the upper boundary, and finally, average_range_rows is the average number of duplicate values within the range interval.

This data lends itself to being displayed as a histogram, hence the name. If I scroll down to the bottom of the histogram breakdown there is no sign of any of the million new records I've inserted with the randomly generated code values. So, what I'll do is run an update statistics command on the customer code index.

Now that's completed I can see that the sampled rows are considerably less than the total number of rows. The show statistics command has used about 13% of the rows to update the statistics on this index. Scrolling down to the bottom of my histogram dataset, I can now see those new values.

If you wanna include all the rows in your statistics update, you can run it with the full scan option. Because of the high degree of fragmentation, I'll rebuild the index and then check the fragmentation again. As you would expect, the rebuild has resulted in no fragmentation.

In terms of automating index and statistics maintenance, you can use the maintenance plans feature within the management node of SQL Server Management Studio if you're working with SQL Server, either on-premise or on a VM or a managed instance. Alternatively, you can run your own scripts as a scheduled job using SQL Server Agent.

In terms of Azure SQL platform as a service, you have a couple of options for automating index and statistics maintenance if you wanna go beyond the auto-tuning facility already present. You can use a runbook through Azure Automation with PowerShell scripts to perform the tasks, or the new Elastic Job agent, which is another way of scheduling PowerShell script jobs.