Wednesday, December 19, 2012

sys.dm_db_stats_properties() - Initial Results on SQL 2012 SP1

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
  1. create a new table with multiple indexes.
  2. insert 10,000 rows, then UPDATE STATISTICS.
  3. call sys.dm_db_stats_properties().
  4. update the StatusId and Deleted columns of 50% of the rows.
  5. 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).

No comments:

Post a Comment