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
