Skip to content

Instantly share code, notes, and snippets.

View DataSic's full-sized avatar

J Rokicki DataSic

View GitHub Profile
@DataSic
DataSic / Allocation Units
Created April 20, 2014 20:52
[SqlServer,SqlOs] Allocation Units per object. OUTER APPLY / TOP(1) pattern.
DECLARE @ObjectId INT
SET @ObjectId = OBJECT_ID('SchemaName.TableName')
SELECT AU.*,
COALESCE(P1.object_id, P2.object_id) OBJECT_ID,
COALESCE(P1.index_id, P2.index_id) INDEX_ID
FROM sys.allocation_units AU
OUTER APPLY (SELECT TOP(1) P.object_id,
P.index_id
FROM sys.partitions P
@DataSic
DataSic / Latch Contention in TempDB
Created April 22, 2014 09:59
[SqlServer,SqlOs] Script to identify latch contention in TempDB
;WITH TasksCTE
AS (SELECT session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
PageID = CAST(Right(resource_description, LEN(resource_description)-
CHARINDEX(':', resource_description, 3)) As Int)
FROM sys.dm_os_waiting_tasks WITH (NOLOCK)
WHERE wait_type Like 'PAGE%LATCH_%'
try
{
# Built-in account
$servicePrincipalConnection = Get-AutomationConnection -Name "AzureRunAsConnection"
# Init variables (Automation Account variables)
$ResourceGroup = Get-AutomationVariable -Name 'ResourceGroup'
$SqlServerName = Get-AutomationVariable -Name 'SqlServerName'
$DatabaseName = Get-AutomationVariable -Name 'DatabaseName'
# Log into Azure with AzureRunAsConnection
@DataSic
DataSic / WorkloadGenerator.sql
Last active December 17, 2017 00:47
[SqlServer,Dev] Workload Generator
;WITH EdgeCTE
AS
(
SELECT parent_object_id Object1,
referenced_object_id Object2,
'[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + OBJECT_NAME(FK.parent_object_id) + '] [T#L#1]' Table1,
'[' + OBJECT_SCHEMA_NAME(FK.referenced_object_id) + '].[' + OBJECT_NAME(FK.referenced_object_id) + '] [T#L#2]' Table2,
(SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY FKS.constraint_column_id) > 1 THEN ' AND ' ELSE '' END +
'[T#L#1].[' + C1.name + '] = ' +
'[T#L#2].[' + C2.name + ']' [text()]
@DataSic
DataSic / QS Wait Stats duplicates
Last active December 18, 2017 22:06
QueryStore Wait Stats duplicates
USE [WWI]
GO
ALTER DATABASE CURRENT SET QUERY_STORE (OPERATION_MODE = READ_WRITE,
QUERY_CAPTURE_MODE = ALL,
INTERVAL_LENGTH_MINUTES = 60)
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR;
GO
SELECT *
FROM [Sales].[CustomerTransactions]
ORDER BY [TaxAmount] DESC
@DataSic
DataSic / QS Wait Stats aggregation
Last active December 18, 2017 22:28
Query Store Wait Stats aggregation
SELECT w.plan_id,
w.runtime_stats_interval_id,
w.execution_type,
w.wait_category,
w.wait_category_desc,
SUM(w.total_query_wait_time_ms) total_query_wait_time_ms,
AVG(w.avg_query_wait_time_ms) avg_query_wait_time_ms,
MIN(w.min_query_wait_time_ms) min_query_wait_time_ms,
MAX(w.max_query_wait_time_ms) max_query_wait_time_ms
FROM sys.query_store_wait_stats w
@DataSic
DataSic / QS Wait Stats Categories
Last active December 19, 2017 13:56
Query Store Wait Stats Categories
SELECT *
FROM (VALUES (0,'Unknown','Unknown'),
(1,'CPU','SOS_SCHEDULER_YIELD'),
(2,'Worker Thread','THREADPOOL'),
(3,'Lock','LCK_M_%'),
(4,'Latch','LATCH_%'),
(5,'Buffer Latch','PAGELATCH_%'),
(6,'Buffer IO','PAGEIOLATCH_%'),
(7,'Compilation*','RESOURCE_SEMAPHORE_QUERY_COMPILE'),
(8,'SQL CLR','CLR%, SQLCLR%'),
@DataSic
DataSic / CosmosDB.Firewall.ps1
Created May 29, 2018 01:26
CosmosDB - Add Firewall Rule
<#
https://docs.microsoft.com/en-us/azure/templates/microsoft.documentdb/databaseaccounts
#>
# Init variables
$resourceGroupName = "ResourceGroupName"
$resourceType = "Microsoft.DocumentDB/databaseAccounts"
$apiVersion = "2015-04-08"
$cosmosDbAccountName = "CosmosDbAccountName"
$propertyObject = @{"databaseAccountOfferType"="Standard";
"ipRangeFilter"="IpAddress1,IpAddress2";}
@DataSic
DataSic / AzureSqlPass.Firewall.sql
Created May 29, 2018 01:29
AzureSqlPass - Add Firewall Rule tSQL
/*
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-database-firewall-rule-azure-sql-database
*/
--[DatabaseName] context
EXEC sp_set_database_firewall_rule @name = N'NewDatabaseFirewallRule',
@start_ip_address = 'IpAddressStart',
@end_ip_address = 'IpAddressEnd'
/*
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-firewall-rule-azure-sql-database
@DataSic
DataSic / AzureAutomation.JITaccess.ps1
Created May 29, 2018 01:31
Azure Automation Runbook - JIT 'Allow access to Azure services'
<#
JIT 'Allow access to Azure services' for runbook on Azure Sql Database/Datawarehouse
#>
try
{
# Init variables
$resourceGroupName = Get-AutomationVariable -Name "ResourceGroup"
$serverName = Get-AutomationVariable -Name "ServerName"
$firewallRuleName = "AllowAllWindowsAzureIps"