Enable or disable the automatic statistics for Database in SQL server

Enable or disable the automatic Statistics for database in SQL Server

Enable or disable for Create the new Statistics for the database:

If statistics for the table or column is missing, the optimizer create the statistics.

-- Enable
ALTER DATABASE [AdventureWorks] SET AUTO_CREATE_STATISTICS ON
GO 

-- Disable 
ALTER DATABASE [AdventureWorks] SET AUTO_CREATE_STATISTICS OFF 
GO 

Enable or disable the statistics for update the existing stats:

If statistics is outdated, then it will update the statistics.

-- Enable the update statistics for existing database:
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS ON
GO  

-- Disable the Update statistics for existing database:
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS OFF 
GO 

Enable or disable the Update Statistics Asynchronously:

Query executed first then it will update statistics after wards.

-- To Enable.
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS_ASYNC ON
 
-- To Disable. 
ALTER DATABASE [AdventureWorks] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.