GWalkey gwalkey

## SQL Saturday.ipynb

      
              1 file
            
          
              1 fork
            
          
              0 comments
            
          
              0 stars
            
          
                gwalkey
                / SQL Saturday.ipynb
            
            
              Created
              October 12, 2022 14:21
            
              
                Azure Data Studio Notebook for DBAs
              
          
      Sorry, something went wrong. Reload?
      Sorry, we cannot display this file.
      Sorry, this file is invalid so it cannot be displayed.
      
          Viewer requires iframe.
      
    
## gist:9a6da12a9b0a8b7b4815ee3db9ef2b65
--- Index Scan/Seek/Lookup/Update Breakdown

SELECT
	DB_NAME([ddius].[database_id]) AS [database name],
	s.[name] AS 'Schema_Name',
	OBJECT_NAME([ddius].[object_id]) AS [Table name],
	CASE
		WHEN ddius.index_id=1 THEN '*'+[i].[name]+'*'
		ELSE [i].[name]
	END AS [index name],

## gist:763d214febb8bf2601b6d32380d02beb
--- Simple
SELECT
	@@SERVERNAME AS 'Server',
	DB_NAME(dbid) as 'Database',
	COUNT(dbid) as 'Number Of Open Connections'
FROM
	sys.sysprocesses
WHERE
	dbid > 0
GROUP BY

## gist:5628793ed34ad1b5d54ed1a1a92f4780
-- Blocked Process Report
CREATE EVENT SESSION [Blocked Process Report] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file(SET filename=N'd:\Traces\Blocked-Process-Report.xel',max_file_size=(1024),max_rollover_files=(4))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

-- Deadlocks
CREATE EVENT SESSION [deadlock_capture] ON SERVER
ADD EVENT sqlserver.database_xml_deadlock_report(

## gist:3969e5b4389b67987eead7e9e36fb9f4
--- 1 Work In Process
--- Ignore me - This is not the query you are looking for
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
USE master
GO

SELECT
	sp.session_Id AS 'Spid'
	,er.blocking_session_id AS 'BlockedBy'
	,DB_NAME(er.database_id) AS 'Database'

## gist:8b53eeec52b1eb627ac7835ac89293c4
SELECT
    HostName,
    "OS UserName",
    Login,
    spid,
    "Database",
    TableID,
    "Table Name",
    IndID,
    "Lock Type",

## gist:3e9b4cc06dd7f0a48b2a01aafb70adef
DECLARE @Starttime DATETIME
DECLARE @EndTime DATETIME
SET @Starttime = DATEADD(hour ,-1,GETDATE())
SET @Endtime = GETDATE()

DROP TABLE IF EXISTS #query1

--- Max Duration
SELECT TOP (100)
    p.query_id query_id,

## gist:00fe9e353ac755e5278bd6d092f20746
# The new [microsoft.data.sqlclient] provider allows new features such as
#     New Azure AD Authentication Methods
#     BulkCopy Speedups
#     DNS Resiliency
#     Data Classification
#     UTF-8 Support
#     Connection Statistics -
#         https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/provider-statistics-sql-server?view=sql-server-ver15

# MS Docs are here

## gist:c6d889884c3bb4a835c99f65b4d39ab6
# https://vexx32.github.io/2018/10/26/Anonymous-Functions/

function Retry()
{
    param(
        [Parameter(Mandatory=$true)][Action]$action,
        [Parameter(Mandatory=$false)][int]$maxAttempts = 3
    )

    $attempts=1

## gist:b168d532b472333e96663cf607bf84eb
Download the CLI Version of NuGet
https://dist.nuget.org/win-x86-commandline/latest/nuget.exe

--Create Work Paths
md c:\nuget
md c:\nuget\source
md c:\nuget\publish

-- One-Time - Create local NuGet Repo/feed using a local drive path
cd c:\nuget
	--- Index Scan/Seek/Lookup/Update Breakdown

	SELECT
	DB_NAME([ddius].[database_id]) AS [database name],
	s.[name] AS 'Schema_Name',
	OBJECT_NAME([ddius].[object_id]) AS [Table name],
	CASE
	WHEN ddius.index_id=1 THEN ''+[i].[name]+''
	ELSE [i].[name]
	END AS [index name],
	--- Simple
	SELECT
	@@SERVERNAME AS 'Server',
	DB_NAME(dbid) as 'Database',
	COUNT(dbid) as 'Number Of Open Connections'
	FROM
	sys.sysprocesses
	WHERE
	dbid > 0
	GROUP BY
	-- Blocked Process Report
	CREATE EVENT SESSION [Blocked Process Report] ON SERVER
	ADD EVENT sqlserver.blocked_process_report
	ADD TARGET package0.event_file(SET filename=N'd:\Traces\Blocked-Process-Report.xel',max_file_size=(1024),max_rollover_files=(4))
	WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
	GO

	-- Deadlocks
	CREATE EVENT SESSION [deadlock_capture] ON SERVER
	ADD EVENT sqlserver.database_xml_deadlock_report(
	--- 1 Work In Process
	--- Ignore me - This is not the query you are looking for
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	USE master
	GO

	SELECT
	sp.session_Id AS 'Spid'
	,er.blocking_session_id AS 'BlockedBy'
	,DB_NAME(er.database_id) AS 'Database'
	SELECT
	HostName,
	"OS UserName",
	Login,
	spid,
	"Database",
	TableID,
	"Table Name",
	IndID,
	"Lock Type",
	DECLARE @Starttime DATETIME
	DECLARE @EndTime DATETIME
	SET @Starttime = DATEADD(hour ,-1,GETDATE())
	SET @Endtime = GETDATE()

	DROP TABLE IF EXISTS #query1

	--- Max Duration
	SELECT TOP (100)
	p.query_id query_id,
	# The new [microsoft.data.sqlclient] provider allows new features such as
	# New Azure AD Authentication Methods
	# BulkCopy Speedups
	# DNS Resiliency
	# Data Classification
	# UTF-8 Support
	# Connection Statistics -
	# https://docs.microsoft.com/en-us/sql/connect/ado-net/sql/provider-statistics-sql-server?view=sql-server-ver15

	# MS Docs are here
	# https://vexx32.github.io/2018/10/26/Anonymous-Functions/

	function Retry()
	{
	param(
	[Parameter(Mandatory=$true)][Action]$action,
	[Parameter(Mandatory=$false)][int]$maxAttempts = 3
	)

	$attempts=1
	Download the CLI Version of NuGet
	https://dist.nuget.org/win-x86-commandline/latest/nuget.exe

	--Create Work Paths
	md c:\nuget
	md c:\nuget\source
	md c:\nuget\publish

	-- One-Time - Create local NuGet Repo/feed using a local drive path
	cd c:\nuget