Otterpohl

/*
Last updated October 1, 2021
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
*/

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],

Otterpohl

SELECT TOP 10
       d.name,
       st.text,
       qs.creation_time,
       qs.execution_count,
       qs.total_worker_time,
       qs.total_elapsed_time,
       qs.total_logical_reads,
       qs.total_physical_reads,
       qp.query_plan,

Otterpohl

DECLARE @temp_sp_who2 AS TABLE
    (
      SPID INT,
      Status VARCHAR(1000) NULL,
      Login SYSNAME NULL,
      HostName SYSNAME NULL,
      BlkBy SYSNAME NULL,
      DBName SYSNAME NULL,
      Command VARCHAR(1000) NULL,
      CPUTime INT NULL,

Otterpohl

DECLARE @temp_spblitzwho AS TABLE
(
    run_date DATETIME2 NULL,
    elapsed_time NVARCHAR(30) NULL,
    session_id INT NULL,
    database_name NVARCHAR(100) NULL,
    query_text NVARCHAR(MAX) NULL,
    query_plan XML NULL,
    live_query_plan XML NULL,
    query_cost FLOAT NULL,

Otterpohl

DECLARE @options INT
SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'

Otterpohl

SELECT dp.name AS [user_name],
       dp.type_desc AS [user_type],
       ISNULL(sp.name, 'Orphaned!') AS [login_name],
       sp.type_desc AS [login_type]
FROM sys.database_principals AS dp
    LEFT JOIN sys.server_principals AS sp
        ON (dp.sid = sp.sid)
WHERE dp.type IN ( 'S', 'U', 'G' )
      AND dp.principal_id > 4
ORDER BY sp.name;

Otterpohl

/*
https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQLServer_Memory_Information.sql
*/

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET LOCK_TIMEOUT 10000;

DECLARE @ServiceName NVARCHAR(100);
SET @ServiceName = CASE

Otterpohl

SELECT total_physical_memory_kb / 1024 AS [Physical Memory (MB)],
       available_physical_memory_kb / 1024 AS [Available Memory (MB)],
       total_page_file_kb / 1024 AS [Total Page File (MB)],
       available_page_file_kb / 1024 AS [Available Page File (MB)],
       system_cache_kb / 1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory;

Otterpohl

SELECT DB_NAME(a.database_id) AS [Database Name],
       b.name + N' [' + b.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS + N']' AS [Logical File Name],
       UPPER(SUBSTRING(b.physical_name, 1, 2)) AS [Drive],
       CAST(((a.size_on_disk_bytes / 1024.0) / (1024.0 * 1024.0)) AS DECIMAL(9, 2)) AS [Size (GB)],
       a.io_stall_read_ms AS [Total IO Read Stall],
       a.num_of_reads AS [Total Reads],
       CASE
           WHEN a.num_of_bytes_read > 0 THEN
               CAST(a.num_of_bytes_read / 1024.0 / 1024.0 / 1024.0 AS NUMERIC(23, 1))
           ELSE

Otterpohl

DECLARE @temp_sp_who2 TABLE
(
    SPID INT NULL,
    Status VARCHAR(1000) NULL,
    Login sysname NULL,
    HostName sysname NULL,
    BlkBy sysname NULL,
    DBName sysname NULL,
    Command VARCHAR(1000) NULL,
    CPUTime INT NULL,