Stats

Statistics are one of the most important objects./aspect from SQL Server.

Today, some  small knowledge about it. For deeper and more information, go search Microsoft site for it.

Statistics are the base when optimizer will choose, when it will generate an execution plan.

Statistics can be created:

•  automatically (if you have AUTO_CREATE_STATISTICS = ON) for an index or if optimizer think it will benefit. ( image a where clause : WHERE city = ‘XXX’ – no index on it, and optimizer will choose to create statistics on it)
• manually –   CREATE STATISTICS nameStats

You can view this information  and different aspect with following commands:

• DBCC Show_Statistics
  • WITH STATS HEADER ; WITH HISTROGRAM
• sys.dm_db_stats_properties
• sys.dm_db_stats_histrogram
• sys.stats
• sys.stats_columns
• sys.dm_db_incremental_stats_properties
• STATS_DATE  – this will present last date time when was updated

If you have the option AUTO_UPDATE_STATISTICS = ON, then there is a threshold that will trigger an update of your statistics:

• regular tables:
  • 0 rows in it – any modification will trigger
  • (0 , 500]  rows –  threshold will be  when you have 500+ modification
  • (500, …) – 20% + 500
• temp table #
  • [0,6) , threshold  will be 6
  • [6,500] , threshold  = 500
  • (500,…)  – 20% + 500
• @ variable tables
  • no threshold for recompilation

To change this recompiled threshold, you can use Trace Flag 2371. This is included on , in sql server 2016, compatibility level 130.

So , next time when a query is executed (one that is using the statistics involved), it is checking if statistics are up-to-date. NOW , there are outdated and the compiled plan is removed from cache, then a recompilation is triggered and  recompilation will trigger the update of statistics.

Also, when a query is compiled for the first time, then statistics is also checked for up-do-date. if they are outdated, then also will trigger the auto Update.

To view the modifications in a table, you can use different approach:

• modification_counter from sys.dm_db_stats_properties
• rowmodctr from sys.sysindexes – this is an old approach  and could be removed in future
• leaf_XXX_count columns from sys.dm_db_index_operational_stats

 

Starting with sql server 2014,  we have something new – Incremental Statistics – applying  at partition level.  This means that is scanning the partition to get/update statistics; including the 20% threshold.
SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = ON)  – setting at db level
or at index level –  WITH (STATISTICS_INCREMENTAL=ON)
You can check it with sys.stats – column is_incremental

 

UPDATE STATISTICS can be :

• FULL SCAN
• SAMPLE

Rebuilding an index will update statistics with full scan.

 

More information can be written about statistics. This is only my view about it.

ps: I hope this info are accurate.

 

 

S