Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save LitKnd/f8bcf317b3fc4aa9fa3b8dbf1216915d to your computer and use it in GitHub Desktop.
Save LitKnd/f8bcf317b3fc4aa9fa3b8dbf1216915d to your computer and use it in GitHub Desktop.
/************************************************************
SET UP THE REPRO
************************************************************/
WHILE @@trancount > 0
ROLLBACK
GO
USE master;
GO
IF DB_ID('StatsTest') IS NOT NULL
BEGIN
ALTER DATABASE StatsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE StatsTest;
END
GO
CREATE DATABASE StatsTest;
GO
USE StatsTest;
GO
SET NOCOUNT ON;
GO
/* create */
DROP TABLE IF EXISTS dbo.ModificationCounterClusterCol;
GO
CREATE TABLE dbo.ModificationCounterClusterCol (
i int identity not null,
varcharcol varchar(256) default ('foo'),
tinyintcol tinyint default (2),
intcol int default (20000),
GUIDcol uniqueidentifier default (newid()),
datetime2col datetime2(0) default ('2016-01-01')
);
GO
/* populate */
DECLARE @i INT = 1;
BEGIN TRAN
WHILE @i < 1000000
BEGIN
INSERT dbo.ModificationCounterClusterCol DEFAULT VALUES;
SET @i=@i+1;
END
COMMIT
GO
/* create col stats */
select *
from dbo.ModificationCounterClusterCol
where tinyintcol=2;
GO
select *
from dbo.ModificationCounterClusterCol
where intcol=2;
GO
select *
from dbo.ModificationCounterClusterCol
where datetime2col is null;
GO
select *
from dbo.ModificationCounterClusterCol
where GUIDcol is null;
GO
select *
from dbo.ModificationCounterClusterCol
where varcharcol is null;
GO
CREATE CLUSTERED COLUMNSTORE INDEX ccxtest ON dbo.ModificationCounterClusterCol;
GO
/************************************************************
VERIFICATION: WE SHOULD HAVE FIVE FRESH COLUMN STATS
************************************************************/
SELECT
modification_counter,
sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id('dbo.ModificationCounterClusterCol');
GO
/************************************************************
OK, NOW RUN THE TEST
************************************************************/
/* increment one stat */
UPDATE dbo.ModificationCounterClusterCol
SET varcharcol = 'test'
WHERE i=109;
GO
/* this looks weird, alright */
SELECT
modification_counter,
sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id('dbo.ModificationCounterClusterCol');
GO
/************************************************************
EXTRA CREDIT: TEST ANOTHER COLUMN
************************************************************/
UPDATE dbo.ModificationCounterClusterCol
SET intcol = intcol+1
WHERE i=109000;
GO 100
/* Yep, still weird. */
SELECT
modification_counter,
sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id('dbo.ModificationCounterClusterCol');
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment