Mark Pryce-Maher MarkPryceMaherMSFT

MarkPryceMaherMSFT / wait_for.sql

Created April 7, 2022 00:44

Wait for it

	Create PROC [dbo].[wait_for] @seconds [bigint] AS
	begin
	declare @quit bit = 0;
	declare @ConpareDateTime datetime2;
	set @ConpareDateTime = dateadd(s, @seconds, getdate() )

	while(@quit=0)
	begin
	if getdate() > @ConpareDateTime
	set @quit = 1;

MarkPryceMaherMSFT / get_details_about_table_proc.sql

Created April 7, 2022 00:42

get details about 1 table


	create PROC [dbo].[gettbldetails] @tblname [varchar](255) AS
	begin

	SELECT
	[Fully Entity Name] = t.full_entity_name,
	[Schema Name] = t.schema_name,
	[Entity Name] = t.entity_name,
	[Current Distribution Method] = t.distribution_method,
	[Current Distribution Column] = ISNULL(t.distribution_column, '-'),

MarkPryceMaherMSFT / round_robin_test3.sql

Created April 7, 2022 00:22

inserting more than 32kb

	/*
	create table [dbo].[round_robin_test1]
	with ( distribution = round_robin) as select * from [dbo].[lineitem_source] where 1 = 0

	insert into [dbo].[round_robin_test1]
	select top 158 * from [dbo].[lineitem_source] -- ( just slight more than 32kb)
	*/
	--exec gettbldetails '%round_robin_test1%'

	SELECT

MarkPryceMaherMSFT / round_robin_part_2.sql

Created April 7, 2022 00:04

The size of data that will fill a distribution

	/*
	create table [dbo].[round_robin_test1]
	with ( distribution = round_robin) as select * from [dbo].[lineitem_source] where 1 = 0

	insert into [dbo].[round_robin_test1]
	select top 157 * from [dbo].[lineitem_source]
	*/
	exec gettbldetails '%round_robin_test1%'

	SELECT

MarkPryceMaherMSFT / round_robin_1.sql

Created April 4, 2022 23:32

uneven distribution of data in round_robin tables

	/*
	drop table [dbo].[round_robin_test1]

	create table [dbo].[round_robin_test1]
	with ( distribution = round_robin) as select * from [dbo].[lineitem_source]
	where 1 = 0

	drop table [dbo].[round_robin_test1]

	create table [dbo].[round_robin_test1]

MarkPryceMaherMSFT / pdw_table_mappings.sql

Created April 4, 2022 22:34

pdw_table_mappings

	create table test_table
	(
	col_a int,
	col_b int
	) with ( distribution=round_robin)

	declare @table_object_id bigint;

	select object_id, * from sys.tables where name like 'test_table'
	select @table_object_id = object_id from sys.tables where name like 'test_table'

MarkPryceMaherMSFT / pdw_distributions.sql

Created April 4, 2022 22:20

	-- distributions
	select * from sys.pdw_distributions

MarkPryceMaherMSFT / get_dwu_size2.sql

Created April 4, 2022 22:03

size of dedicated sql pool

	-- Run against master database
	SELECT db.name [Database]
	, ds.edition [Edition]
	, ds.service_objective [Service Objective]
	FROM sys.database_service_objectives AS ds
	JOIN sys.databases AS db ON ds.database_id = db.database_id;

MarkPryceMaherMSFT / get_dwu_size.sql

Last active April 4, 2022 22:02

size of dedicated sql pool

	-- Size of dedicated SQL pool
	SELECT DATABASEPROPERTYEX (DB_NAME(), 'ServiceObjective' ) as ServiceObjective

MarkPryceMaherMSFT / table_details.sql

Created April 4, 2022 15:58

	--https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview
	SELECT
	[Fully Entity Name] = t.full_entity_name,
	[Schema Name] = t.schema_name,
	[Entity Name] = t.entity_name,
	[Current Distribution Method] = t.distribution_method,
	[Current Distribution Column] = ISNULL(t.distribution_column, '-'),
	[Current Rows] = SUM(t.rows_count),
	[Distribution Count] = COUNT(t.rows_count),
	[Current Data Size on Disk MB] = SUM(t.data_size_MB),