.::: Quary T-SQL to simulation High CPU & High Memory Usage In SQL Server :::.

 

A. Simulation High CPU

1. Contoh T-SQL untuk Simulasi CPU Tinggi
 
Untuk mensimulasikan CPU utilization tinggi di SQL Server menggunakan T-SQL, kamu bisa menjalankan perintah yang bersifat berat secara berulang, misalnya:

PERINGATAN!
Skrip di bawah akan membuat CPU usage SQL Server tinggi, gunakan di lingkungan development/test, bukan production.

USE tempdb;
GO

IF OBJECT_ID('dbo.CPU_HOG') IS NOT NULL
    DROP PROCEDURE dbo.CPU_HOG;
GO

CREATE PROCEDURE dbo.CPU_HOG
AS
BEGIN
    DECLARE @i INT = 0;
    DECLARE @j FLOAT = 0;

    WHILE (@i < 10000000)
    BEGIN
        SET @j = SQRT(@i * RAND()); -- operasi matematika berat
        SET @i = @i + 1;
    END
END;
GO
 
-- Membuat prosedur dummy CPU-intensive
USE tempdb;
GO

IF OBJECT_ID('dbo.CPU_HOG') IS NOT NULL
    DROP PROCEDURE dbo.CPU_HOG;
GO

CREATE PROCEDURE dbo.CPU_HOG
AS
BEGIN
    DECLARE @i INT = 0;
    DECLARE @j FLOAT = 0;

    WHILE (@i < 10000000)
    BEGIN
        SET @j = SQRT(@i * RAND()); -- operasi matematika berat
        SET @i = @i + 1;
    END
END;
GO

2. Jalankan Banyak Thread Paralel (gunakan SQLCMD atau SSMS):

-- Buka beberapa tab dan jalankan secara bersamaan

EXEC tempdb.dbo.CPU_HOG;

Untuk simulasi lebih berat, jalankan 5–10 tab secara paralel.

3. check cpu memory usage

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(1) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               100 - SystemIdle - SQLProcessUtilization + SQLProcessUtilization AS [Total Usage CPU],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] , @@SERVERNAME as ServerName,@@servicename as ServiceName
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

SELECT
    (physical_memory_in_use_kb/1024) AS MemoryInUse_MB,
    (locked_page_allocations_kb/1024) AS LockedPages_MB,
    (virtual_address_space_committed_kb/1024) AS VAS_Used_MB
FROM sys.dm_os_process_memory;


DECLARE @total_memory_mb FLOAT, @available_memory_mb FLOAT, @memory_usage_percentagesql FLOAT,@memory_usage_mbsql FLOAT;

-- Mengambil total memori fisik yang tersedia pada SQL Server dalam megabyte
SELECT @total_memory_mb = physical_memory_kb / 1024.0
FROM sys.dm_os_sys_info;

-- Mengambil jumlah memori SQL Server
SELECT @memory_usage_mbsql=cast(physical_memory_in_use_kb / 1024.0 AS NUMERIC(10,2))
FROM sys.dm_os_process_memory

-- Menghitung persentase penggunaan memori SQL Server
SET @memory_usage_percentagesql = (@memory_usage_mbsql / @total_memory_mb) * 100;

-- Menampilkan hasil dengan pembulatan
SELECT @@servername as ServerName,ROUND(@memory_usage_percentagesql, 0) AS 'Memory Usage(%) SQL',ROUND(@memory_usage_mbsql, 0) AS 'Memory Usage(MB) SQL',ROUND(@total_memory_mb, 0) AS 'Memory Total(MB) Server';
 

4. drop store procedure
DROP PROCEDURE tempdb.dbo.CPU_HOG;

USE tempdb;
GO
DROP PROCEDURE dbo.CPU_HOG;

B. Simulation High Memory

Berikut adalah contoh T-SQL untuk menyimulasikan penggunaan memori (memory utilization) di SQL Server, dengan cara membuat objek-objek sementara dan memaksa SQL Server menyimpan data ke dalam memori:

1. Simulasi Memory Utilization (T-SQL):

USE tempdb;
GO

IF OBJECT_ID('tempdb.dbo.tbl_memload') IS NOT NULL
    DROP TABLE dbo.tbl_memload;
GO

-- Membuat tabel dengan data cukup besar
CREATE TABLE dbo.tbl_memload (
    id INT IDENTITY(1,1) PRIMARY KEY,
    filler CHAR(8000) -- setiap baris ~8KB
);
GO

2. Masukkan banyak baris (misal 100.000 baris = ~800 MB data di memori)
-- ?? Jangan jalankan di server produksi tanpa pengawasan

INSERT INTO dbo.tbl_memload (filler)
SELECT TOP (100000) REPLICATE('A', 80000)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
GO

3. Memeriksa Penggunaan Memori oleh Proses SQL Server

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(1) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               100 - SystemIdle - SQLProcessUtilization + SQLProcessUtilization AS [Total Usage CPU],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] , @@SERVERNAME as ServerName,@@servicename as ServiceName
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

SELECT
    (physical_memory_in_use_kb/1024) AS MemoryInUse_MB,
    (locked_page_allocations_kb/1024) AS LockedPages_MB,
    (virtual_address_space_committed_kb/1024) AS VAS_Used_MB
FROM sys.dm_os_process_memory;


DECLARE @total_memory_mb FLOAT, @available_memory_mb FLOAT, @memory_usage_percentagesql FLOAT,@memory_usage_mbsql FLOAT;

-- Mengambil total memori fisik yang tersedia pada SQL Server dalam megabyte
SELECT @total_memory_mb = physical_memory_kb / 1024.0
FROM sys.dm_os_sys_info;

-- Mengambil jumlah memori SQL Server
SELECT @memory_usage_mbsql=cast(physical_memory_in_use_kb / 1024.0 AS NUMERIC(10,2))
FROM sys.dm_os_process_memory

-- Menghitung persentase penggunaan memori SQL Server
SET @memory_usage_percentagesql = (@memory_usage_mbsql / @total_memory_mb) * 100;

-- Menampilkan hasil dengan pembulatan
SELECT @@servername as ServerName,ROUND(@memory_usage_percentagesql, 0) AS 'Memory Usage(%) SQL',ROUND(@memory_usage_mbsql, 0) AS 'Memory Usage(MB) SQL',ROUND(@total_memory_mb, 0) AS 'Memory Total(MB) Server';
 

4. Bersihkan Memori (opsional)

-- Hapus tabel
DROP TABLE dbo.tbl_memload;

-- Bersihkan buffer cache (hati-hati, ini memengaruhi kinerja sementara!)
DBCC DROPCLEANBUFFERS;