Thursday, December 20, 2012

sys.dm_db_stats_properties() - Initial Results on SQL 2008 R2 SP2

My first sys.dm_db_stats_properties() post showed results for SQL Server 2012 SP1. Here, I will show how the results for filtered indexes on 2008 R2 SP2 are different.

The test script is exactly the same as before, just running on a different version.

MSDN Docs

The 2008 R2 version of the docs is here. Because the results were different, I pulled the text for both the 2012 document and the 2008 R2 document and compared them. There are no substantive differences.

Here are the only differences:
2008 R2 SP2"Requires that the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."
2012 SP1 adds the highlighted section"Requires that the user has select permissions on statistics columns or the user owns the table or the user is a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role."

Results

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) 
   Jun 28 2012 08:36:30 
   Copyright (c) Microsoft Corporation
   Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (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
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------

name              filter_definition last_updated        rows  rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid  NULL              2012-12-20 09:10:09 10000 10000        132   10000           0
IX_Test1_Deleted  NULL              2012-12-20 09:10:09 10000 10000        1     10000           0
IX_Test1_StatusId NULL              2012-12-20 09:10:09 10000 10000        1     10000           0
PK_Test1_ColId    NULL              2012-12-20 09:10:09 10000 10000        3     10000           0

Update Count: 5000
name              filter_definition last_updated        rows  rows_sampled steps unfiltered_rows modification_counter
----------------- ----------------- ------------------- ----- ------------ ----- --------------- --------------------
AK_Test1_ColGuid  NULL              2012-12-20 09:10:09 10000 10000        132   10000           0
IX_Test1_Deleted  NULL              2012-12-20 09:10:09 10000 10000        1     10000           5000
IX_Test1_StatusId NULL              2012-12-20 09:10:09 10000 10000        1     10000           5000
PK_Test1_ColId    NULL              2012-12-20 09:10:09 10000 10000        3     10000           0

Observations

Filtered Index Results
Filtered indexes are excluded from these results where they return NULLs in 2012.

The difference appears to be in the fact that the function returns a row in 2012 for a filtered index and does not return a row in 2008 R2 for a filtered index.


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).