Update Statistics

Update Statistics:

SQL Server tries to use statistics to “react intelligently” in its query optimization. Knowing number of records, density of pages, histogram, or available indexes help the SQL Server optimizer “guess” more accurately how it can best retrieve data

Query to get statistics on Tables

SELECT OBJECT_NAME(object_id) AS [ObjectName]

      ,[name] AS [StatisticName]

      ,STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate]

FROM sys.stats order by STATS_DATE([object_id], [stats_id])desc

How are statistics created?

Statistics can be created different ways
- Statistics are automatically created for each index key you create.

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

- CREATE STATISTICS

What do statistics look like?

If you’re curious, there’s a couple ways you can peek at what statistics look like.

Option 1 – 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


Option 2 – you can use DBCC SHOW_STATISTICS WITH HISTOGRAM


The histograms are a great way to visualize the data distribution in your table.

How are statistics updated?

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 basically means, if there is an incoming query but statistics are stale, SQL Server will update statistics first before it generates an execution plan.
- 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.

However, if you want to manually update statistics, you can use either sp_updatestats or UPDATE STATISTICS <statistics name>

How do we know statistics are being used?

One good check you can do is when you generate execution plans for your queries:

check out your “Actual Number of Rows” and “Estimated Number of Rows”.

If these numbers are (consistently) fairly close, then most likely 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.

What configuration settings should we set?

There may be cases when you may 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.

However, for the most part, you will want to keep the SQL Server settings:
- auto create statistics
- auto update statistics

StatisticsHeader Details

CREATE TABLE StatisticsHeader

 (

      Name                   sysname,

      Updated                datetime,

      [Rows]                 bigint,

      RowsSampled            bigint,

      Steps                  tinyint,

      Density                decimal(9,5),

      AverageKeyLength       decimal(9,5),

      StringIndex            nchar(3),

      FilterExpression       nvarchar(1000),

      UnfilteredRows         bigint

 )

 go

 

 INSERT StatisticsHeader

 EXEC ('DBCC SHOW_STATISTICS (''FactSMCSubscriptionBaseLIR'', ''IXC_AgreementKey'')

      WITH stat_header')

 go

 

 SELECT * FROM StatisticsHeader

 Go