New Index TX Log Impact Testing

New Index TX Log Impact Testing.sql

------------------------------------------------------------
GO
------------------------------------------------------------
SELECT [CurrentDB] = (SELECT x.recovery_model_desc FROM sys.databases x WHERE [name] = DB_NAME())
------------------------------------------------------------------------------
GO
------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#tmp_log_track','U') IS NOT NULL DROP TABLE #tmp_log_track; --SELECT * FROM #tmp_log_track
CREATE TABLE #tmp_log_track (
	ID					int				NOT NULL IDENTITY(1,1),
	CheckPointName		nvarchar(300)	NOT NULL,
	log_bytes_used		bigint			NOT NULL,
	CollectionTime		datetime2		NOT NULL DEFAULT SYSUTCDATETIME(),
);

IF OBJECT_ID('tempdb..#tmp_before2','U') IS NOT NULL DROP TABLE #tmp_before2; --SELECT * FROM #tmp_before2
CREATE TABLE #tmp_before2 (
	ID										int				NOT NULL IDENTITY(1,1),
	CheckPointName							nvarchar(300)	NOT NULL,
	database_id								int					NULL,
	total_log_size_in_bytes					bigint				NULL,
	used_log_space_in_bytes					bigint				NULL,
	used_log_space_in_percent				real				NULL,
	log_space_in_bytes_since_last_backup	bigint				NULL,
	CollectionTime							datetime2		NOT NULL DEFAULT SYSUTCDATETIME(),
);

INSERT INTO #tmp_before2 SELECT 'Init', *, SYSUTCDATETIME() FROM sys.dm_db_log_space_usage;

IF OBJECT_ID('tempdb..#tmp_before','U') IS NOT NULL DROP TABLE #tmp_before; --SELECT * FROM #tmp_before
SELECT * INTO #tmp_before FROM sys.dm_os_performance_counters WHERE [object_name] IN ('SQLServer:Catalog Metadata', 'SQLServer:Databases') AND instance_name = DB_NAME();
------------------------------------------------------------------------------
GO
------------------------------------------------------------------------------
--████████████████████████████████████████████████████
--████████████████ Create first index ████████████████
--████████████████████████████████████████████████████
	BEGIN TRAN;
		IF (OBJECT_ID(N'[dbo].[TableA]', 'U') IS NOT NULL
			AND INDEXPROPERTY(OBJECT_ID(N'[dbo].[TableA]'), N'IX_TableA_SomeCol', 'IndexId') IS NULL
		)
		BEGIN;
			RAISERROR('    Execute: CREATE NONCLUSTERED INDEX [IX_TableA_SomeCol] ON [dbo].[TableA]',0,1) WITH NOWAIT;
			CREATE NONCLUSTERED INDEX [IX_TableA_SomeCol] ON [dbo].[TableA] ([SomeCol]);
		END;

		INSERT INTO #tmp_log_track (CheckPointName, log_bytes_used)
		SELECT '[dbo].[TableA].[IX_TableA_SomeCol]', dt.database_transaction_log_bytes_used
		FROM sys.dm_tran_session_transactions st
			JOIN sys.dm_tran_database_transactions dt ON dt.transaction_id = st.transaction_id AND dt.database_id = DB_ID()
		WHERE st.[session_id] = @@SPID;
	COMMIT TRAN;

	INSERT INTO #tmp_before2 SELECT '[dbo].[TableA].[IX_TableA_SomeCol]', *, SYSUTCDATETIME() FROM sys.dm_db_log_space_usage;
--████████████████████████████████████████████████████


--████████████████████████████████████████████████████
--███████████████ Create second index ████████████████
--████████████████████████████████████████████████████
	BEGIN TRAN;
		IF (OBJECT_ID(N'[dbo].[TableA]', 'U') IS NOT NULL
			AND INDEXPROPERTY(OBJECT_ID(N'[dbo].[TableA]'), N'IX_TableA_SomeOtherCol', 'IndexId') IS NULL
		)
		BEGIN;
			RAISERROR('    Execute: CREATE NONCLUSTERED INDEX [IX_TableA_SomeOtherCol] ON [dbo].[TableA]',0,1) WITH NOWAIT;
			CREATE NONCLUSTERED INDEX [IX_TableA_SomeOtherCol] ON [dbo].[TableA] ([SomeOtherCol]);
		END;

		INSERT INTO #tmp_log_track (CheckPointName, log_bytes_used)
		SELECT '[dbo].[TableA].[IX_TableA_SomeOtherCol]', dt.database_transaction_log_bytes_used
		FROM sys.dm_tran_session_transactions st
			JOIN sys.dm_tran_database_transactions dt ON dt.transaction_id = st.transaction_id AND dt.database_id = DB_ID()
		WHERE st.[session_id] = @@SPID;
	COMMIT TRAN;

	INSERT INTO #tmp_before2 SELECT '[dbo].[TableA].[IX_TableA_SomeOtherCol]', *, SYSUTCDATETIME() FROM sys.dm_db_log_space_usage;
--████████████████████████████████████████████████████
------------------------------------------------------------------------------
GO
------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#tmp_after','U') IS NOT NULL DROP TABLE #tmp_after; --SELECT * FROM #tmp_after
SELECT * INTO #tmp_after FROM sys.dm_os_performance_counters WHERE [object_name] IN ('SQLServer:Catalog Metadata', 'SQLServer:Databases') AND instance_name = DB_NAME();

IF OBJECT_ID('tempdb..#tmp_after2','U') IS NOT NULL DROP TABLE #tmp_after2; --SELECT * FROM #tmp_after2
SELECT * INTO #tmp_after2 FROM sys.dm_db_log_space_usage;
------------------------------------------------------------------------------
GO
------------------------------------------------------------------------------
SELECT a.[object_name], a.counter_name, a.instance_name, cntr_diff = FORMAT(x.cntr_diff, 'N0'), a.cntr_type
FROM #tmp_after a
	JOIN #tmp_before b ON b.[object_name] = a.[object_name] AND b.counter_name = a.counter_name AND b.instance_name = a.instance_name
	CROSS APPLY (SELECT cntr_diff = (a.cntr_value - b.cntr_value)) x
WHERE a.cntr_value <> b.cntr_value
ORDER BY a.[object_name], a.counter_name, a.instance_name;

SELECT N'█ Before:' [██]
	, total_log_size_TB		= FORMAT(b.total_log_size_in_bytes   / 1024.0 / 1024.0 / 1024.0 / 1024.0, 'N3')
	, used_log_space_GB		= FORMAT(b.used_log_space_in_bytes   / 1024.0 / 1024.0 / 1024.0, 'N3')
	, used_log_space_pct	= FORMAT(b.used_log_space_in_percent / 100.0, 'P3')
	, N'█ After:' [██]
	, total_log_size_TB		= FORMAT(a.total_log_size_in_bytes   / 1024.0 / 1024.0 / 1024.0 / 1024.0, 'N3')
	, used_log_space_GB		= FORMAT(a.used_log_space_in_bytes   / 1024.0 / 1024.0 / 1024.0, 'N3')
	, used_log_space_pct	= FORMAT(a.used_log_space_in_percent / 100.0, 'P3')
	, N'█ Delta:' [██]
	, total_log_size_KB		= FORMAT((a.total_log_size_in_bytes   - b.total_log_size_in_bytes)   / 1024.0, 'N0')
	, used_log_space_KB		= FORMAT((a.used_log_space_in_bytes   - b.used_log_space_in_bytes)   / 1024.0, 'N0')
	, used_log_space_pct	= FORMAT((a.used_log_space_in_percent - b.used_log_space_in_percent) / 100.0, 'P3')
FROM #tmp_after2 a
	CROSS JOIN #tmp_before2 b;
------------------------------------------------------------------------------

------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#results','U') IS NOT NULL DROP TABLE #results; --SELECT * FROM #results
SELECT l.CheckPointName
	, SizeDiffMB = CONVERT(decimal(10,2), (l.used_log_space_in_bytes - LAG(l.used_log_space_in_bytes) OVER (ORDER BY l.CollectionTime)) / 1024.0 / 1024.0)
	, TimeDiffSec = CONVERT(decimal(10,2), DATEDIFF(MILLISECOND, LAG(l.CollectionTime) OVER (ORDER BY l.CollectionTime), l.CollectionTime) / 1000.0)
	, TimeDiffMin = CONVERT(decimal(10,2), DATEDIFF(MILLISECOND, LAG(l.CollectionTime) OVER (ORDER BY l.CollectionTime), l.CollectionTime) / 1000.0 / 60.0)
	, ps.row_count
	, IndexSizeMB = CONVERT(decimal(10,2), (ps.used_page_count * 8) / 1024.0)
INTO #results
FROM #tmp_before2 l
	LEFT JOIN sys.indexes i ON CONCAT_WS('.', QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id])), QUOTENAME(OBJECT_NAME(i.[object_id])), QUOTENAME(i.[name])) = l.CheckPointName
	LEFT JOIN sys.dm_db_partition_stats ps ON ps.index_id = i.index_id AND ps.[object_id] = i.[object_id]
------------------------------------------------------------------------------

------------------------------------------------------------------------------
SELECT LogSizeDiffGB  = CONVERT(decimal(10,2), SUM(SizeDiffMB) / 1024.0)
	, TotalTimeMin    = CONVERT(decimal(10,2), SUM(TimeDiffSec) / 60.0)
	, TotalIndexSizes = CONVERT(decimal(10,2), SUM(IndexSizeMB) / 1024.0)
FROM #results

SELECT * FROM #results WHERE CheckPointName <> 'Init'
ORDER BY TimeDiffMin DESC
------------------------------------------------------------

------------------------------------------------------------