/*
	============================================================================
	File:		133 - Implementation - Incremental Statistics.sql

	Summary:	This script demonstrates the feature "incremental statistics"
				for partitioned tables

	Date:		September 2019

	SQL Server Version: 2014 / 2016 / 2017 / 2019
------------------------------------------------------------------------------
	Written by Uwe Ricken, db Berater GmbH

	This script is intended only as a supplement to demos and lectures
	given by Uwe Ricken.  
  
	THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
	ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
	TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
	PARTICULAR PURPOSE.
	============================================================================
*/
USE demo_db;
GO

-- Create and fill the table CustomerOrders
EXEC dbo.CreateCustomerOrders;
GO

-- If incremental statistics should be used by default it can be
-- activated on database level
ALTER DATABASE [demo_db] SET AUTO_CREATE_STATISTICS ON (INCREMENTAL = ON);
GO

-- as an alternativ it can be activated on statistics as option
CREATE UNIQUE CLUSTERED INDEX cuix_CustomerOrders_OrderDate
ON dbo.CustomerOrders (OrderDate, Id)
ON OrderDates(OrderDate)
GO

SELECT	object_id,
		name,
		stats_id,
		is_incremental
FROM	sys.stats
WHERE	object_id = OBJECT_ID(N'dbo.CustomerOrders', N'U');
GO

UPDATE STATISTICS dbo.CustomerOrders cuix_CustomerOrders_OrderDate
WITH FULLSCAN, INCREMENTAL = ON;
GO

-- now the stats are incremental - each partition has its on stats!
SELECT	object_id,
		name,
		stats_id,
		is_incremental
FROM	sys.stats
WHERE	object_id = OBJECT_ID(N'dbo.CustomerOrders', N'U');
GO

-- to see the stats of each partition you have to use an internal table
SELECT	node_id
		, last_updated
		, steps
		, next_sibling
		, left_boundary
		, right_boundary
		, partition_number
FROM	sys.dm_db_stats_properties_internal
		(
			OBJECT_ID(N'dbo.CustomerOrders', N'U'),
			1
		)
WHERE	partition_number IS NOT NULL
ORDER BY
		partition_number,
		node_id;
GO

DBCC SHOW_STATISTICS (N'dbo.CustomerOrders', N'cuix_CustomerOrders_OrderDate') WITH HISTOGRAM;
GO

-- To see the data of the partition stats ...
DBCC TRACEON (2309);
DBCC SHOW_STATISTICS(N'dbo.CustomerOrders', N'cuix_CustomerOrders_OrderDate', 22);
DBCC TRACEOFF (2309);
GO

-- Test
SELECT * FROM dbo.CustomerOrders
WHERE	OrderDate = '2019-01-03';
GO

-- Now we add 10,000 more rows for 20170101
INSERT INTO dbo.CustomerOrders
(Customer_Id, OrderNumber, InvoiceNumber, OrderDate, OrderStatus_Id, Employee_Id, InsertUser, InsertDate)
SELECT	TOP (10000)
		Customer_Id,
		OrderNumber,
		InvoiceNumber,
		'20170101',
		OrderStatus_Id,
		Employee_Id,
		InsertUser,
		InsertDate
FROM	CustomerOrders.dbo.CustomerOrders
ORDER BY
		NEWID();
GO

-- Test
SELECT * FROM dbo.CustomerOrders
WHERE	OrderDate = '20170101';
GO

-- Update partial statistics for the partition for 2017 only
-- To see the data of the partition stats ...
DBCC TRACEON (2309);
DBCC SHOW_STATISTICS(N'dbo.CustomerOrders', N'cuix_CustomerOrders_OrderDate', 27);
DBCC TRACEOFF (2309);
GO

BEGIN TRAN;
GO
	UPDATE STATISTICS dbo.CustomerOrders cuix_CustomerOrders_OrderDate
	WITH RESAMPLE ON PARTITIONS (18);
	GO
	
	SELECT * FROM sys.dm_tran_locks
	WHERE	request_session_id = @@SPID;
	GO

COMMIT TRANSACTION
GO

DBCC TRACEON (2309);
DBCC SHOW_STATISTICS(N'dbo.CustomerOrders', N'cuix_CustomerOrders_OrderDate', 27);
DBCC TRACEOFF (2309);
GO

DBCC SHOW_STATISTICS(N'dbo.CustomerOrders', N'cuix_CustomerOrders_OrderDate') WITH HISTOGRAM;

SELECT * FROM dbo.CustomerOrders
WHERE	OrderDate = '20170101'
OPTION (RECOMPILE);
GO

