SQL Server Data Warehouse and Index Statistics

During the data loading of data warehouse, huge amount of source data were poured into and after it's finished, you need to redo index statistics to gain high query performance.

Statistics are automatically created for each index key you create.

If the database setting autocreate stats is on, which is by default setting, then SQL Server will automatically create statistics for non-indexed columns that are used in queries.

You can go to your Statistics node in your SSMS, right click > Properties, then go to Details. Below is a sample of the stats and histogram that’s collected for one of the tables in my database.

Another option is you can use DBCC SHOW_STATISTICS WITH HISTOGRAM

The default settings in SQL Server are to autocreate and autoupdate statistics.

Notice that there are two (2) options with the Auto Update statistics.

• Auto Update Statistics Asynchronously on the other hand means, if there is an incoming query but statistics are stale, SQL Server uses the stale statistics to generate the execution plan, then updates the statistics afterwards.

• Auto Update Statistics basically means, if there is an incoming query but statistics are stale,SQL Server will update statistics first before it generates an execution plan.

You can check statistics comparing your “Actual Number of Rows” and “Estimated Number of Rows”. If these numbers are (consistently) fairly close, then your statistics are up-to-date and used by the optimizer for the query. If not, time for you to re-check your statistics create/update frequency.

There may be cases when you want to disable statistics update temporarily while you’re doing massive updates on a table, and you don’t want it to be slowed down by the autoupdate.

Source: http://blog.idera.com/sql-server/understanding-sql-server-statistics/