The SQL Server team just released a new feature, the DMF sys.dm_db_stats_properties(). This new feature is available in 2008 R2 SP2 and 2012 SP1.
These tests were run on SQL Server
2012 SP1 and will illustrate what the function returns in the normal case.
First, some references:
The test code below will
- create a new table with multiple indexes.
- insert 10,000 rows, then UPDATE STATISTICS.
- call sys.dm_db_stats_properties().
- update the StatusId and Deleted columns of 50% of the rows.
- call sys.dm_db_stats_properties().
Test Code
USE tempdb;GOPRINT @@VERSION;GOIF EXISTS (SELECT * FROM sys.tables WHERE OBJECT_ID('dbo.Test1') IS NOT NULL)
DROP TABLE dbo.Test1;CREATE TABLE dbo.Test1 (
ColId INT IDENTITY,
ColGuid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
StatusId tinyint NOT NULL DEFAULT 1,
Deleted bit NOT NULL DEFAULT 0,
CONSTRAINT PK_Test1_ColId PRIMARY KEY CLUSTERED (ColId),
CONSTRAINT AK_Test1_ColGuid UNIQUE (ColGuid)
);GOCREATE NONCLUSTERED INDEX IX_Test1_ColId_WhereDeletedEq1 ON dbo.Test1 (
ColId) WHERE Deleted = 1;GOCREATE NONCLUSTERED INDEX IX_Test1_StatusId_WhereDeletedEq1 ON dbo.Test1 (
StatusId) WHERE Deleted = 1;GOCREATE NONCLUSTERED INDEX IX_Test1_Deleted ON dbo.Test1 (
Deleted);GOCREATE NONCLUSTERED INDEX IX_Test1_StatusId ON dbo.Test1 (
StatusId);GO-- ==================== Initial insertINSERT INTO dbo.Test1 DEFAULT VALUES;GO 10000-- ==================== Call sys.dm_db_stats_properties()SELECT
s.name,
s.filter_definition,
CONVERT(VARCHAR(19), p.last_updated, 120) AS last_updated,
p.[rows],
p.rows_sampled,
p.steps,
p.unfiltered_rows,
p.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS pWHERE s.[object_id] = OBJECT_ID('dbo.Test1')ORDER BY s.has_filter, s.name;GO-- ==================== UPDATE STATISTICSUPDATE STATISTICS dbo.Test1;GO-- ==================== Call sys.dm_db_stats_properties()SELECT
s.name,
s.filter_definition,
CONVERT(VARCHAR(19), p.last_updated, 120) AS last_updated,
p.[rows],
p.rows_sampled,
p.steps,
p.unfiltered_rows,
p.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS pWHERE s.[object_id] = OBJECT_ID('dbo.Test1')ORDER BY s.has_filter, s.name;GO-- ==================== Update 50% of records (StatusId, Deleted)UPDATE dbo.Test1SET
StatusId = 2,
Deleted = 1WHERE ColId % 2 = 0;PRINT 'Update Count: ' + CONVERT(VARCHAR(11), @@ROWCOUNT);GO-- ==================== Call sys.dm_db_stats_properties()SELECT
s.name,
s.filter_definition,
CONVERT(VARCHAR(19), p.last_updated, 120) AS last_updated,
p.[rows],
p.rows_sampled,
p.steps,
p.unfiltered_rows,
p.modification_counterFROM sys.stats AS sCROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS pWHERE s.[object_id] = OBJECT_ID('dbo.Test1')ORDER BY s.has_filter, s.name;GO
Results
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
Beginning execution loop
Batch execution completed 10000 times.
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------- ---- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL NULL NULL NULL NULL NULL NULL
IX_Test1_Deleted NULL NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId NULL NULL NULL NULL NULL NULL NULL
PK_Test1_ColId NULL NULL NULL NULL NULL NULL NULL
IX_Test1_ColId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-19 14:59:41 10000 10000 136 10000 0
IX_Test1_Deleted NULL 2012-12-19 14:59:41 10000 10000 1 10000 0
IX_Test1_StatusId NULL 2012-12-19 14:59:41 10000 10000 1 10000 0
PK_Test1_ColId NULL 2012-12-19 14:59:41 10000 10000 3 10000 0
IX_Test1_ColId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
Update Count: 5000
name filter_definition last_updated rows rows_sampled steps unfiltered_rows modification_counter
--------------------------------- ----------------- ------------------------- ------------ ----- --------------- --------------------
AK_Test1_ColGuid NULL 2012-12-19 14:59:41 10000 10000 136 10000 0
IX_Test1_Deleted NULL 2012-12-19 14:59:41 10000 10000 1 10000 5000
IX_Test1_StatusId NULL 2012-12-19 14:59:41 10000 10000 1 10000 5000
PK_Test1_ColId NULL 2012-12-19 14:59:41 10000 10000 3 10000 0
IX_Test1_ColId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
IX_Test1_StatusId_WhereDeletedEq1 ([Deleted]=(1)) NULL NULL NULL NULL NULL NULL
Observations
NULLs
Not surprisingly, the result columns are all NULL before calling UPDATE STATISTICS.
Steps
Steps is an interesting statistic. Steps indicates the "number of steps in the histogram." Note that "The maximum number of steps is 200." (
ref) You won't get 200 by simply adding more rows. Inserting 100,000 rows instead of 10,000 gives a steps value of 56 for the GUID index.
Filtered Indexes
I was never able to get filtered rows to return any statistics even though the
docs say for the rows column, "If the statistics are filtered or correspond to a filtered index, the number of rows might be less than the number of rows in the table."
Is this a bug in SQL Server or in my query?
modification_counter
As expected, modification_counter is 0 before there are any modifications and after modifying 5,000 rows, the counts are 5,000 (except for the filtered indexes).
