MarkPryceMaherMSFT’s gists

MarkPryceMaherMSFT / dmvs.sql

Created May 21, 2026 23:19

dmvs - some dmvs

	-- https://learn.microsoft.com/en-us/fabric/data-warehouse/monitor-using-dmv
	-- connections/sessions/queries
	select * from sys.dm_exec_connections;
	select * from sys.dm_exec_sessions;
	select * from sys.dm_exec_requests;
	select * from sys.dm_exec_query_stats;

	-- query text
	select st.text
	FROM sys.dm_exec_requests r

MarkPryceMaherMSFT / tpc-h-10tb-100percentpool.csv

Last active May 1, 2026 15:22

tpc-h-10tb-100percentpool

MarkPryceMaherMSFT / summary_results.csv

Created May 1, 2026 12:06

The timings for tpc-h 10tb - call outs

MarkPryceMaherMSFT / timezonefunction.sql

Created April 15, 2026 10:46

timezone function

	CREATE FUNCTION dbo.ConvertUtcToFixedOffset
	(
	@UtcTime DATETIME,
	@TimeZoneName VARCHAR(100)
	)
	RETURNS DATETIME
	AS
	BEGIN
	DECLARE @OffsetMinutes INT;

MarkPryceMaherMSFT / create_offset_table.sql

Last active April 15, 2026 12:06

Create a table with consistent timezone offsets

	--DROP TABLE dbo.TimeZoneOffsets

	CREATE TABLE dbo.TimeZoneOffsets (
	TimeZoneName VARCHAR(100) ,
	OffsetMinutes INT
	);

	INSERT INTO dbo.TimeZoneOffsets (TimeZoneName, OffsetMinutes)
	VALUES
	-- UTC

MarkPryceMaherMSFT / update_indexes.sql

Created March 18, 2026 16:49

Simple script to update all the stats

	DECLARE @sql NVARCHAR(MAX) = N'';
	SELECT @sql +=
	'BEGIN TRY '+ CHAR(13) + ' UPDATE STATISTICS [' + s.name + '].[' + t.name + '] [' + st.name + '] WITH FULLSCAN;' + CHAR(13) + 'END TRY' + CHAR(13)+ ' BEGIN CATCH' + CHAR(13) + ' PRINT ''Failed to update statistics on [' + s.name + '].[' + t.name + '] [' + st.name + ']'';' + CHAR(13) + 'END CATCH;' + CHAR(13)
	FROM sys.stats st
	JOIN sys.tables t ON st.object_id = t.object_id
	JOIN sys.schemas s ON t.schema_id = s.schema_id
	where st.name not in ('ClusteredIndex') and st.name not like 'ACE%'
	print @sql
	EXEC sp_executesql @sql;

MarkPryceMaherMSFT / exec_requests_history.sql

Last active March 10, 2026 12:37

query-insights exec_requests_history

	SELECT [distributed_statement_id],
	[database_name],
	[submit_time],
	[start_time],
	[end_time],
	[is_distributed],
	[statement_type],
	[total_elapsed_time_ms],
	[login_name],
	[row_count],

MarkPryceMaherMSFT / tables-columns-datatypes.sql

Created March 10, 2026 10:47

query to get the datatypes for each column

MarkPryceMaherMSFT / hardensystemtable.sql

Created March 2, 2026 10:20

Harden a System Table

	declare @list varchar(max)
	declare @sql nvarchar(max)
	drop table if exists sys_tables
	create table sys_tables (object_id bigint, tablename varchar(50))

	SELECT @list = STRING_AGG('(' + CAST([object_id] AS VARCHAR(20)) + ', ''' + CAST([name] AS VARCHAR(128)) + ''')', ',') FROM sys.tables
	SET @sql = 'INSERT INTO sys_tables (object_id, tablename) VALUES ' + @list
	--print @sql
	exec sp_executesql @sql

MarkPryceMaherMSFT / sql_monitor_mirroring.sql

Created December 24, 2025 19:46

query to see when tables were last checked for updates

	SELECT * FROM (
	select distinct eventtime,TRIM( replace(replace(replace(replace(
	case when CHARINDEX('END TRY',table_details) > 3 then LEFT(table_details, CHARINDEX('END TRY',table_details)-4)
	else table_details end, ''', N''',','),'N''',''),''';',''),'''','')) as bl_details
	from (
	select *, CHARINDEX('sys.fn_cdc_get_min_lsn(',SQLText) no_,
	SUBSTRING(SQLText,CHARINDEX('sys.fn_cdc_get_min_lsn(',SQLText) + len('sys.fn_cdc_get_min_lsn('),150) sss_
	,
	case when CHARINDEX('sys.sp_cdc_help_change_data_capture',SQLText) > 0 then SUBSTRING(SQLText,CHARINDEX('sys.sp_cdc_help_change_data_capture',SQLText) + len('sys.sp_cdc_help_change_data_capture'),150)
	when CHARINDEX('cdc_get_ddl_history',SQLText) > 0 then SUBSTRING(SQLText,CHARINDEX('cdc_get_ddl_history @capture_instance = ',SQLText) + len('cdc_get_ddl_history @capture_instance = '),150)

Mark Pryce-Maher MarkPryceMaherMSFT