Auto Update Statistics and Auto Update Statistics Asynchronously Investigation

Below are my findings into the Auto Update Statistics and Auto Update Statistics Asynchronously database options.

What I discovered using profiler was:

Scenario 1:

• Auto Update Statistics = True
• Auto Update Statistics Asynchronously = False

This resulted in the relevant table statistics being updated as part of the user’s session before the user query starts retrieving data. This is the default database setting.

Scenario 2:

• Auto Update Statistics = True
• Auto Update Statistics Asynchronously = True

This resulted in the relevant table statistics being updated by a system session (not the user's session) after the user query has completed.

Scenario 3:

• Auto Update Statistics = False
• Auto Update Statistics Asynchronously = True

This resulted in no statistics being updated. This was interesting as it means that if Auto Update Statistics is set to False, it does not matter that Auto Update Statistics Asynchronously is set to True as the statistics will not be auto updated.

The code used to test this was:

-- Create the test table

CREATE TABLE [dbo].[t1](

      [c1] [bigint] IDENTITY(1,1) NOT NULL,

      [c2] [varchar](50) NULL

) ON [PRIMARY]

GO

-- Insert first data row into table

INSERT INTO dbo.t1

            (c2)

VALUES      ('x')

GO 

-- Stats are only created once you add the where clause

SELECT *

FROM   dbo.t1

WHERE  c1 < 5 

-- Use the below to show when stats were last updated

SELECT OBJECT_NAME(OBJECT_ID)          AS TABLENAME,

       NAME                            AS STATS_NAME,

       STATS_DATE(OBJECT_ID, STATS_ID) AS STATISTICS_UPDATE_DATE

FROM   SYS.STATS

WHERE  OBJECT_NAME(OBJECT_ID) = 't1' 

-- Insert several rows to the table

INSERT INTO DBO.T1

            (C2)

VALUES      ('x')

GO 1000

/*

When you run the below the stats will be updated and this is the data I captured in Profiler. Specifically the EventClasses Auto Stats, Lock:Acquired, Lock:Released, SQL:StmtStarting, SQL:StmtCompleted, Performance statistics and all columns.

*/

SELECT *

FROM   DBO.T1

WHERE  C1 > 5