Archive for February, 2012

Monitoring statistics

In the DBA world most of us are familiar with the various ways of monitoring column and index level statistics. We know about the STATS_DATE function, the rowmodctr column in the legacy sysindexes table and how to use traces/profiler to capture the occurrences of auto create and auto update statistics.

You may also be familiar with the DBCC SHOW_STATISTICS command but do you know that you receive enhanced output from this command when trace flag 2388 is enabled ? The output shows you a history of when statistics were manually or automatically updated. Additional columns show the historic table cardinality, density and the number of inserts and deletes since the last time statistics were updated (update counts are treated as delete+insert pairs – a single row update statement appears as a 0.5 in the delete count and a 0.5 in the insert count).

Sample output:

You can see the table started off with 400 rows, statistics were automatically updated when a further 500 rows were inserted and from then on auto updates occurred after very (500 + 20%) inserts.

The complete test script is presented below (OPTION (RECOMPILE) is required. Without it you will see that auto update of statistics does not happen. The ‘trigger’ for the auto update is the compilation of any query that detects that statistics are out of date).

-- This script assumes auto create and auto update stats are ON for your database

IF OBJECT_ID('dbo.StatTest') IS NOT NULL DROP TABLE dbo.StatTest;

CREATE TABLE dbo.StatTest
(
id int NOT NULL IDENTITY PRIMARY KEY,
somedata char(3) NOT NULL
);

-- Populate the table with less than 500 rows. In this case, 400
INSERT INTO dbo.StatTest (somedata)
SELECT TOP (400) 'abc' FROM master.dbo.spt_values;

-- An auto created statistic will appear when we run the next command
SELECT id FROM dbo.StatTest WHERE somedata = 'stu' OPTION (RECOMPILE);

-- Save the auto created statistic name use in DBCC SHOW_STATISTICS
DECLARE @statname sysname
SELECT @statname = name
FROM sys.stats
WHERE object_id = OBJECT_ID('dbo.StatTest')
AND auto_created = 1;

-- Enable the trace flag to show additional DBCC SHOW_STATISTICS info
DBCC TRACEON (2388);

DBCC SHOW_STATISTICS ('dbo.StatTest',@statname);

-- Because the table was initially populated with less than 500 rows we need
-- 500 changes before the next auto-update statistics kicks in. Prove this by
-- inserting one less than 500 rows then inserting one additional row
INSERT INTO dbo.StatTest (somedata)
SELECT TOP (499) 'def' FROM master.dbo.spt_values;

-- Query the table (option recompile ensures a fresh plan is built)
SELECT id FROM dbo.StatTest WHERE somedata = 'stu' OPTION (RECOMPILE);

-- Statistics will not have been auto-updated as threshold has not been crossed
DBCC SHOW_STATISTICS ('dbo.StatTest',@statname);

-- Add 1 more row so that threshold is crossed
INSERT INTO dbo.StatTest (somedata)
SELECT TOP (1) 'ghi' FROM master.dbo.spt_values;

-- Query the table (option recompile ensures a fresh plan is built)
SELECT id FROM dbo.StatTest WHERE somedata = 'stu' OPTION (RECOMPILE);

-- Statistics will now have been auto-updated
DBCC SHOW_STATISTICS ('dbo.StatTest',@statname);

-- Table now has 900 rows so next auto-update will occur when
-- 500 + 20% * 900 changes have been made. Add 1 below that
INSERT INTO dbo.StatTest (somedata)
SELECT TOP (500+20*900/100-1) 'jkl' FROM master.dbo.spt_values;

-- Query the table (option recompile ensures a fresh plan is built)
SELECT id FROM dbo.StatTest WHERE somedata = 'stu' OPTION (RECOMPILE);

-- Statistics will not have been auto-updated
DBCC SHOW_STATISTICS ('dbo.StatTest',@statname);

-- Add 1 more row so that change threshold is crossed
INSERT INTO dbo.StatTest (somedata)
SELECT TOP (1) 'mno' FROM master.dbo.spt_values;

-- Query the table (option recompile ensures a fresh plan is built)
SELECT id FROM dbo.StatTest WHERE somedata = 'stu' OPTION (RECOMPILE);

-- Statistics will now have been auto-updated
DBCC SHOW_STATISTICS ('dbo.StatTest',@statname);

-- Table now has 1580 rows so next auto-update will occur when
-- 500 + 20% * 1580 changes have been made. Add 1 below that
INSERT INTO dbo.StatTest (somedata)
SELECT TOP (500+20*1580/100-1) 'pqr' FROM master.dbo.spt_values;

-- Query the table (option recompile ensures a fresh plan is built)
SELECT id FROM dbo.StatTest WHERE somedata = 'stu' OPTION (RECOMPILE);

-- Statistics will not have been auto-updated
DBCC SHOW_STATISTICS ('dbo.StatTest',@statname);

-- Add 1 more row so that change threshold is crossed
INSERT INTO dbo.StatTest (somedata)
SELECT TOP (1) 'stu' FROM master.dbo.spt_values;

-- Query the table (option recompile ensures a fresh plan is built)
SELECT id FROM dbo.StatTest WHERE somedata = 'stu' OPTION (RECOMPILE);

-- Statistics will now have been auto-updated
DBCC SHOW_STATISTICS ('dbo.StatTest',@statname);

IF OBJECT_ID('dbo.StatTest') IS NOT NULL DROP TABLE dbo.StatTest;
Advertisements

Leave a comment