ghotz

# Install-Module ReportingServicesTools; # https://github.com/microsoft/ReportingServicesTools
Import-Module ReportingServicesTools;
$rsServerName = "localhost";
$rsAllContent = Get-RsFolderContent -RsFolder "/" -ReportServerUri "http://$rsServerName/ReportServer" -Recurse;
$rsAllReports = $rsAllContent | ? { $_.TypeName -eq "Report" } #| Select * -First 5;

$rsAllReports | % { 
    $ReportName = $_.Path;
    $rsAllReportsDS = Get-RsItemDataSource -RsItem $_.Path -ReportServerUri "http://$rsServerName/ReportServer";
    $rsAllReportsDS | % {

ghotz

DROP TABLE IF EXISTS dbo.test;
CREATE TABLE dbo.Test (
	ID	int IDENTITY(1, 1) NOT NULL PRIMARY KEY
,	PayLoad nvarchar(1000) NOT NULL DEFAULT (REPLICATE(CHAR(33 + ABS(CHECKSUM(NEWID())) % 90), ABS(CHECKSUM(NEWID())) % 1000))
);
GO
SET NOCOUNT ON;
DECLARE	@i int = 10000;
WHILE	@i > 0
BEGIN

ghotz

-- get memory broker events from system health file session
IF OBJECT_ID('tempdb..#events') IS NOT NULL
	DROP TABLE #events;

WITH   XmlDataSet AS
(
		SELECT	CAST(xe.event_data  AS xml) AS XMLDATA
		FROM	sys.fn_xe_file_target_read_file('system_health_*.xel',NULL,NULL,NULL) as xe
		WHERE	xe.[object_name] = 'memory_broker_ring_buffer_recorded'
)

ghotz

dir C:\PerfLogs\Admin\PAL | % { Compress-Archive -Path $_.FullName -CompressionLevel Optimal -DestinationPath (Join-Path $_.DirectoryName $_.BaseName) }

ghotz

DECLARE @handle UNIQUEIDENTIFIER;
WHILE (SELECT COUNT(*) FROM NameOfQueue) > 0
BEGIN
	RECEIVE TOP (1) @handle = conversation_handle FROM NameOfQueue;
	END CONVERSATION @handle WITH CLEANUP
END

ghotz

EXEC	sp_MSforeachdb '
USE [?];
SELECT	DB_NAME() AS database_name, name AS principal_name, default_schema_name
FROM	sys.database_principals
WHERE	default_schema_name NOT IN (''dbo'', ''guest'');
';

ghotz

WITH DBsize AS
(
	SELECT	D1.[name] AS DatabaseName, SUM(CAST(F1.size as bigint)) * 8192 / 1024 / 1024 AS DatabaseSizeMB
	FROM	sys.databases AS D1
	JOIN	sys.master_files AS F1
	  ON	D1.database_id = F1.database_id
	WHERE	D1.database_id > 4 AND F1.type_desc = 'ROWS'
	GROUP BY D1.[name]
)
,	SizeRules AS

ghotz

SELECT	I.start_time, I.end_time, P.query_id, p.plan_id
	,	(SELECT		P2.plan_id
			FROM	sys.query_store_plan AS P2
			WHERE	P.query_id = P2.query_id
			  AND	P.plan_id <> P2.plan_id
			  AND	P.last_force_failure_reason = 0
		) AS plan_id_different
FROM	sys.query_store_runtime_stats_interval AS I
JOIN	sys.query_store_runtime_stats  AS S
  ON	I.runtime_stats_interval_id = S.runtime_stats_interval_id

ghotz

SELECT
	objtype
,	COUNT_BIG(*) AS plans_count
,	SUM(CAST(size_in_bytes AS bigint) / 1024. / 1024.) AS plans_MB
,	AVG(usecounts) AS plans_count_avg
,	SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS plans_single_use_count
,	SUM(CASE WHEN usecounts = 1 THEN CAST(size_in_bytes AS bigint) / 1024. / 1024. ELSE 0 END) AS plans_single_use_MB
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY plans_single_use_MB DESC

ghotz

SELECT	S1.name AS stats_name, C1.name AS column_name
FROM	tempdb.sys.stats AS S1
JOIN	tempdb.sys.stats_columns AS S2
  ON	S1.object_id = S2.object_id
 AND	S1.stats_id = S2.stats_id
JOIN	tempdb.sys.all_columns AS C1
  ON	S2.object_id = C1.object_id
 AND	S2.column_id = C1.column_id
WHERE	S1.[object_id] = OBJECT_ID('tempdb..#ftplog_t_dati_1')
ORDER BY