-- 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
Friday, January 4, 2013
sys.dm_db_stats_properties() Test Script - Update 1
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment