MSSQL Performance Skript

kleines Skript um a bissi die Performance eines SQL Servers zu testen. 

/* ============================================================
   SQL Server Network Wait Performance Test

   ============================================================ */

SET NOCOUNT OFF;
USE tempdb;
GO

---------------------------------------------------------------
-- 1) Wait-Stats Baseline
---------------------------------------------------------------
IF OBJECT_ID('tempdb..#waits_baseline') IS NOT NULL DROP TABLE #waits_baseline;

SELECT
    wait_type,
    wait_time_ms,
    signal_wait_time_ms
INTO #waits_baseline
FROM sys.dm_os_wait_stats;

---------------------------------------------------------------
-- 2) Testtabelle erzeugen (großes Resultset)
---------------------------------------------------------------
IF OBJECT_ID('dbo.NetworkWaitTest') IS NOT NULL
    DROP TABLE dbo.NetworkWaitTest;

CREATE TABLE dbo.NetworkWaitTest
(
    ID INT IDENTITY PRIMARY KEY,
    Payload CHAR(8000) NOT NULL
);

-- ca. 800 MB Payload
INSERT INTO dbo.NetworkWaitTest (Payload)
SELECT TOP (100000)
       REPLICATE('X', 8000)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;

---------------------------------------------------------------
-- 3) Testparameter
---------------------------------------------------------------
DECLARE 
    @Runs INT = 5,               -- Anzahl der Abfragen
    @i INT = 1,
    @StartTime DATETIME2,
    @EndTime DATETIME2,
    @DurationMs BIGINT;

IF OBJECT_ID('tempdb..#runs') IS NOT NULL DROP TABLE #runs;

CREATE TABLE #runs
(
    RunNumber INT,
    DurationMs BIGINT
);

---------------------------------------------------------------
-- 4) Abfragen ausführen & Laufzeiten messen
---------------------------------------------------------------
WHILE @i <= @Runs
BEGIN
    SET @StartTime = SYSDATETIME();

    -- absichtlich großes Resultset
    SELECT * 
    FROM dbo.NetworkWaitTest;

    SET @EndTime = SYSDATETIME();

    SET @DurationMs = DATEDIFF(MILLISECOND, @StartTime, @EndTime);

    INSERT INTO #runs (RunNumber, DurationMs)
    VALUES (@i, @DurationMs);

    SET @i += 1;
END

---------------------------------------------------------------
-- 5) Laufzeitstatistik
---------------------------------------------------------------
SELECT
    COUNT(*)        AS Runs,
    MIN(DurationMs) AS MinMs,
    MAX(DurationMs) AS MaxMs,
    AVG(DurationMs) AS AvgMs
FROM #runs;

---------------------------------------------------------------
-- 6) Wait-Stats Delta (nach Test)
---------------------------------------------------------------
SELECT
    w.wait_type,
    (w.wait_time_ms - b.wait_time_ms) AS wait_time_delta_ms,
    (w.signal_wait_time_ms - b.signal_wait_time_ms) AS signal_wait_delta_ms
FROM sys.dm_os_wait_stats w
JOIN #waits_baseline b
    ON w.wait_type = b.wait_type
WHERE (w.wait_time_ms - b.wait_time_ms) > 0
ORDER BY wait_time_delta_ms DESC;