Sql server statistics not updating

During the PASS Summit in November I presented a session on Demystifying Database Statistics (if you attended Summit but missed it, you can stream it from here).During the session I went through a demo that showed the automatic update of a statistic.

sql server statistics not updating-90

The SQL Server creates statistics in different ways: When you set the AUTO_CREATE_STATISTICS option on, the Query Optimizer creates statistics on individual columns used in a predicate, if these statistics are not already available.

These statistics are necessary to generate the query plan.

They are created on columns that do not have a histogram in an existing statistics object.

The name of the auto-created statistics includes the column name and the object ID in hexadecimal format: _WA_Sys_.

They’re created at different points and, unless you’re creating the statistics manually yourself, they’re created slightly differently.

The statistics on an index are created with the index.Test Sales Order Detail'); This is my go-to DMV for statistics because in addition to including the date that statistics were last updated and row information, I also get a count of modifications since the last statistic update.I just created the table, so the Last Updated date is current, and I have not made any changes so the modification count is 0.In order to analyze the behavior of updating statistics, first we need to create a sample database with the option AUTO_UPDATE_STATISTICS set to OFF. Customers( Customer Id INT NOT NULL IDENTITY(1,1), Customer Code NVARCHAR(10) NOT NULL, Customer Name NVARCHAR(50) NOT NULL, Customer Address NVARCHAR(50) NOT NULL, Last Modified DATETIME NOT NULL DEFAULT GETDATE() PRIMARY KEY CLUSTERED (Customer Id), INDEX IX_Customer Code NONCLUSTERED (Customer Code) ) GO You can use Tibor Nagy's tip Populating a SQL Server Test Database with Random Data to fill the previous table with data.USE [master] GO CREATE DATABASE [Test DB] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Test DB_file1', FILENAME = N'E:\MSSQL\Test DB_1.mdf', SIZE = 128MB , MAXSIZE = UNLIMITED, FILEGROWTH = 64MB) LOG ON ( NAME = N'Test DB_log_file1', FILENAME = N'E:\MSSQL\Test DB_1.ldf', SIZE = 8MB, MAXSIZE = 2048GB, FILEGROWTH = 8MB) GO ALTER DATABASE Test DB SET RECOVERY SIMPLE GO ALTER DATABASE Test DB SET AUTO_UPDATE_STATISTICS OFF USE Test DB GO IF OBJECT_ID('dbo. Please consider that you should add enough data that gives you time when running the statistics update to run Dynamic Management View queries in another session to see what is occurring.Invalidating Statistics For my demo I want to bulk insert enough rows to invalidate the statistics.

Tags: , ,