ghotz

WITH cte AS
(
	SELECT	*
	,	AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Pagination)[1]', 'bigint') AS pagination_scalability_ms
	,	AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)[1]', 'bigint') AS pagination_estimated_kb
	,	AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Rendering)[1]', 'bigint') AS rendering_scalability_ms
	,	AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Rendering)[1]', 'bigint') AS rendering_estimated_kb
	,	AdditionalInfo.value('(/AdditionalInfo/ScalabilityTime/Processing)[1]', 'bigint') AS processing_scalability_ms
	,	AdditionalInfo.value('(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)[1]', 'bigint') AS processing_estimated_kb
	FROM	ExecutionLog2

ghotz

SELECT	'ALTER INDEX [' + I1.name + '] ON [' + SCHEMA_NAME(O1.[schema_id]) + '].[' + O1.name + '] REBUILD;'
FROM	sys.indexes AS I1
JOIN	sys.objects AS O1
  ON	I1.[object_id] = O1.[object_id]
WHERE	I1.type_desc = 'NONCLUSTERED COLUMNSTORE'

ghotz

# Set culture information
$currentThread=[System.Threading.Thread]::CurrentThread
$culture=[System.Globalization.CultureInfo]::GetCultureInfo("en-US")
$currentThread.CurrentCulture=$culture
$currentThread.CurrentUICulture=$culture
Set-Culture $culture

ghotz

dir *.blg | % { relog.exe "$_" -f SQL -o "SQL:Relog!$($_.BaseName)" }

ghotz

WITH BackupSets AS (
      SELECT database_name
      ,            CASE [type]
                   WHEN 'D' THEN 'Full'
                   WHEN 'I' THEN 'Differential'
                   WHEN 'L' THEN 'Log'
                   WHEN 'F' THEN 'File/Filegroup'
                   END backup_type
      ,            backup_start_date, backup_finish_date, physical_device_name
      ,            ROW_NUMBER() OVER (PARTITION BY database_name, [type] ORDER BY backup_start_date DESC) as rn

ghotz

--
-- Generate Columnstore indexes based on all columns in a table
--
SELECT
	'CREATE COLUMNSTORE INDEX [ci_' + T1.name + '] ON [' + S1.name + '].[' + T1.name + '] ('
+	STUFF(
	(
		SELECT	DISTINCT ',[' + C1.name + ']'
		FROM	sys.columns AS C1
		WHERE	T1.object_id = C1.object_id

ghotz

DECLARE	@schema VARCHAR(MAX)
EXEC	sp_whoisactive @return_schema = 1, @schema = @schema OUTPUT
SET		@schema = 'IF object_id(''tempdb..##tmp'') IS NOT NULL DROP TABLE ##tmp; ' + REPLACE(@schema, '<table_name>', '##tmp')
EXEC	(@schema)
EXEC	sp_whoisactive @destination_table = '##tmp'
SELECT	*
FROM	##tmp
WHERE	wait_info NOT LIKE '%BROKER_RECEIVE_WAITFOR%'
  OR	wait_info IS NULL

ghotz

$SqlCmdVariables = "DatabaseName=master", "ColumnName=name";
$Results = (Invoke-Sqlcmd -ServerInstance "localhost\sql2017" -Query "SET STATISTICS XML ON; SELECT `$(ColumnName) FROM sys.databases WHERE [name] = '`$(DataBaseName)';" -Variable $SqlCmdVariables -MaxCharLength 10MB);
$Plan = [xml]$Results[$Results.GetUpperBound(0)].Item(0);
$Plan.Save("C:\temp\test.sqlplan");

ghotz

Get-WmiObject -namespace root\wmi –class MSStorageDriver_FailurePredictStatus -ErrorAction Silentlycontinue |  Select InstanceName, PredictFailure, Reason | Format-Table –Autosize

ghotz

-- Generates enabled/disabled updates for all jobs with their original setting
SELECT	'EXEC msdb.dbo.sp_update_job @job_name = ''' + REPLACE(J1.[name], '''', '''''') + ''', @enabled = ' + CAST(J1.[enabled] AS varchar(1)) + ' ;' AS SqlCmd
FROM	msdb.dbo.sysjobs AS J1
ORDER BY J1.[name]

-- Generates enabled updates for all jobs
SELECT	'EXEC msdb.dbo.sp_update_job @job_name = ''' + REPLACE(J1.[name], '''', '''''') + ''', @enabled = 0;' AS SqlCmd
FROM	msdb.dbo.sysjobs AS J1
ORDER BY J1.[name]