Filtered Stats and Incremental Stats on Partitioned table

• Robin35

  SSCertifiable

  Points: 6471

  More actions

  November 21, 2016 at 5:16 pm

  #337624

  Hello,

  We have just implemented table partition on a very large table in SQL Server 2014 , we used have filtered stats on the same table before partition not after the table partition we enabled Incremental Statistics but we are thinking to create filtered stats which would allow for a much more granular level of statistics....

  At this point, i'm not sure if a partitioned table can have both Filtered stats and Incremental Stats. I know incremental stats will not use by optimizer. If we are allowed to create a filtered stats, as we already have filtered stats on old table (before partition), can we script out and create them in a partitioned table ? We created filtered stats on Datetime column and we did the partitioning on the same datetime column. I tried searching online about the options but i dont see any details.

  Any help would be appreciated.

  Thanks in advance...

• MadAdmin

  SSChampion

  Points: 11261

  More actions

  November 23, 2016 at 4:06 am

  #1914182

  Statistics are independent of each other so you can have 1 stat with sampling, another with full scan, another with incremental, another with filter.

  The test is below, from the below URL, I just added a filtered statistic after the setup from Navarez' Article.

  https://sqlperformance.com/2014/02/sql-statistics/2014-incremental-statistics

  USE ADVENTUREWORKS

  GO

  CREATE PARTITION FUNCTION TransactionRangePF1 (DATETIME) AS RANGE RIGHT FOR VALUES

  ( '20071001', '20071101', '20071201', '20080101', '20080201', '20080301', '20080401', '20080501', '20080601', '20080701', '20080801');

  GO

  CREATE PARTITION SCHEME TransactionsPS1 AS PARTITION TransactionRangePF1 TO

  ( [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],

  [PRIMARY], [PRIMARY], [PRIMARY]);

  GO

  CREATE TABLE dbo.TransactionHistory

  ( TransactionID INT NOT NULL, -- not bothering with IDENTITY here

  ProductID INT NOT NULL,

  ReferenceOrderID INT NOT NULL,

  ReferenceOrderLineID INT NOT NULL DEFAULT (0),

  TransactionDate DATETIME NOT NULL DEFAULT (GETDATE()),

  TransactionType NCHAR(1) NOT NULL,

  Quantity INT NOT NULL,

  ActualCost MONEY NOT NULL,

  ModifiedDate DATETIME NOT NULL DEFAULT (GETDATE()),

  CONSTRAINT CK_TransactionType CHECK (UPPER(TransactionType) IN (N'W', N'S', N'P'))

  ) ON TransactionsPS1 (TransactionDate);

  GO

  INSERT INTO dbo.TransactionHistory SELECT * FROM Production.TransactionHistory

  GO

  CREATE STATISTICS incrstats ON dbo.TransactionHistory(TransactionDate) WITH FULLSCAN, INCREMENTAL = ON;

  CREATE STATISTICS FILT on dbo.TransactionHistory(TransactionDate) where Quantity=5

  Catch-all queries done right [/url]
  Gail Shaw's Performance Blog[/url]