Friday, January 4, 2013

sys.dm_db_stats_properties() Test Script - Update 1

-- Ref:
--  http://www.jasonstrate.com/2012/10/what-happened-to-rowmodctr-from-sysindexes/
--  http://msdn.microsoft.com/en-us/library/jj553546.aspx
--  http://www.sqlskills.com/blogs/erin/post/new-statistics-dmf-in-sql-server-2008r2-sp2.aspx
--  http://persistencevision.blogspot.com/2012/12/sysdmdbstatsproperties-initial-results.html
--  http://persistencevision.blogspot.com/2012/12/sysdmdbstatsproperties-initial-results_20.html

USE tempdb;
GO
PRINT @@VERSION;
GO
IF 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)
);
GO
CREATE NONCLUSTERED INDEX IX_Test1_ColId_WhereDeletedEq1 ON dbo.Test1 (
  
ColId
) WHERE Deleted = 1;
GO
CREATE NONCLUSTERED INDEX IX_Test1_StatusId_WhereDeletedEq1 ON dbo.Test1 (
  
StatusId
) WHERE Deleted = 1;
GO
CREATE NONCLUSTERED INDEX IX_Test1_Deleted ON dbo.Test1 (
  
Deleted
);
GO
CREATE NONCLUSTERED INDEX IX_Test1_StatusId ON dbo.Test1 (
  
StatusId
);
GO
-- ==================== Initial insert
INSERT INTO dbo.Test1 DEFAULT VALUES;
GO 10000
-- ==================== Call sys.dm_db_stats_properties()
SELECT
  
s.name,
  
CONVERT(VARCHAR(23), p.last_updated, 121) AS last_updated,
  
p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
ORDER BY s.has_filter, s.name;
GO
-- ==================== UPDATE STATISTICS
UPDATE STATISTICS dbo.Test1;
GO
-- ==================== Call sys.dm_db_stats_properties()
SELECT
  
s.name,
  
CONVERT(VARCHAR(23), p.last_updated, 121) AS last_updated,
  
p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
ORDER BY s.has_filter, s.name;
GO
-- ==================== Update 50% of records (StatusId, Deleted)
UPDATE dbo.Test1
SET
  
StatusId = 2,
  
Deleted  = 1
WHERE ColId % 2 = 0;
PRINT 'Update Count: ' + CONVERT(VARCHAR(11), @@ROWCOUNT);
GO
-- ==================== Call sys.dm_db_stats_properties()
SELECT
  
s.name,
  
CONVERT(VARCHAR(23), p.last_updated, 121) AS last_updated,
  
p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
  AND
s.name IN ('IX_Test1_Deleted', 'IX_Test1_StatusId')
ORDER BY s.has_filter, s.name;
GO
-- ==================== Update 1 record (StatusId, Deleted)
UPDATE dbo.Test1
SET
  
StatusId = 2,
  
Deleted  = 1
WHERE ColId = 1
  
AND Deleted <> 1;
PRINT 'Update Count: ' + CONVERT(VARCHAR(11), @@ROWCOUNT);
SELECT * FROM dbo.Test1 WHERE ColId = 1;
GO
--WAITFOR DELAY '00:00:00.001';
--GO
-- ==================== Call sys.dm_db_stats_properties()
SELECT
  
s.name,
  
CONVERT(VARCHAR(23), p.last_updated, 121) AS last_updated,
  
p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.[object_id], s.stats_id) AS p
WHERE s.[object_id] = OBJECT_ID('dbo.Test1')
  AND
s.name IN ('IX_Test1_Deleted', 'IX_Test1_StatusId')
ORDER BY s.has_filter, s.name;
GO

No comments:

Post a Comment