/*
================================================================================
  ESXi ORTAMI - MS SQL SERVER PERFORMANS ANALİZ & OPTİMİZASYON SCRİPTİ
  VMware ESXi Sanallaştırma + MS SQL Server Best Practices

  Her sorgu BEGIN TRY / END CATCH içindedir.
  Herhangi bir sorgu hata verse sessizce atlar, script durmaz.
================================================================================
*/

SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;

IF OBJECT_ID('tempdb..#AnalysisResults') IS NOT NULL DROP TABLE #AnalysisResults;
CREATE TABLE #AnalysisResults (
    Kategori        NVARCHAR(100),
    Parametre       NVARCHAR(200),
    MevcutDeger     NVARCHAR(500),
    Onerilen        NVARCHAR(500),
    Aciklama        NVARCHAR(1000),
    Oncelik         NVARCHAR(20)
);

PRINT '================================================================================';
PRINT '  ESXi + MS SQL SERVER PERFORMANS ANALİZİ';
PRINT '  Tarih: ' + CONVERT(NVARCHAR(30), GETDATE(), 120);
PRINT '================================================================================';

-- ============================================================
-- BÖLÜM 01: SİSTEM GENEL BİLGİLERİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 01: Sistem Genel Bilgileri';

BEGIN TRY
    SELECT
        @@SERVERNAME                              AS [SQL Server Adi],
        @@VERSION                                 AS [Versiyon],
        SERVERPROPERTY('Edition')                 AS [Edition],
        SERVERPROPERTY('ProductVersion')          AS [ProductVersion],
        SERVERPROPERTY('ProductLevel')            AS [SP_CU];
END TRY BEGIN CATCH PRINT '  [!] Sistem bilgisi alinamadi: ' + ERROR_MESSAGE(); END CATCH;

BEGIN TRY
    SELECT
        sqlserver_start_time                      AS [SQL Baslangic],
        DATEDIFF(HOUR, sqlserver_start_time, GETDATE()) AS [Uptime Saat],
        DATEDIFF(DAY,  sqlserver_start_time, GETDATE()) AS [Uptime Gun]
    FROM sys.dm_os_sys_info;
END TRY BEGIN CATCH PRINT '  [!] Uptime bilgisi alinamadi.'; END CATCH;

-- ============================================================
-- BÖLÜM 02: SANALLAŞTIRMA TESPİTİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 02: Sanallaştırma Tespiti';

DECLARE @HypervisorDetected NVARCHAR(100) = 'Tespit edilemedi';

BEGIN TRY
    DECLARE @vmtype NVARCHAR(100);
    EXEC sp_executesql
        N'SELECT @o = virtual_machine_type_desc FROM sys.dm_os_sys_info',
        N'@o NVARCHAR(100) OUTPUT',
        @o = @vmtype OUTPUT;
    SET @HypervisorDetected = ISNULL(@vmtype, 'NONE');
END TRY BEGIN CATCH SET @HypervisorDetected = 'Kolon yok - eski versiyon'; END CATCH;

SELECT
    @HypervisorDetected AS [Hypervisor],
    CASE @HypervisorDetected
        WHEN 'HYPERVISOR' THEN 'VM uzerinde - ESXi optimizasyonlari kritik!'
        WHEN 'NONE'       THEN 'Fiziksel sunucu'
        ELSE 'Manuel dogrulama gerekli'
    END AS [Degerlendirme];

INSERT INTO #AnalysisResults VALUES (
    'ESXi', 'Hypervisor Tespiti', @HypervisorDetected,
    'HYPERVISOR olmali',
    'VM tespiti icin sys.dm_os_sys_info.virtual_machine_type_desc kullanildi.',
    CASE @HypervisorDetected WHEN 'HYPERVISOR' THEN 'BILGI' ELSE 'ORTA' END
);

BEGIN TRY
    SELECT node_id, node_state_desc, memory_node_id,
           online_scheduler_count, active_worker_count, avg_load_balance
    FROM sys.dm_os_nodes
    WHERE node_state_desc <> 'ONLINE DAC';
END TRY BEGIN CATCH PRINT '  [!] NUMA node bilgisi alinamadi.'; END CATCH;

BEGIN TRY
    INSERT INTO #AnalysisResults
    SELECT
        'ESXi', 'vNUMA Durumu',
        CAST(COUNT(*) AS NVARCHAR) + ' NUMA node',
        CASE WHEN COUNT(*) = 1 THEN 'vNUMA aktif degil - vCPU sayisina gore yapilandirin' ELSE 'vNUMA aktif' END,
        '8+ vCPU icin vNUMA zorunludur. VMX: numa.vcpu.preferHT = TRUE',
        CASE WHEN COUNT(*) = 1 THEN 'YUKSEK' ELSE 'BILGI' END
    FROM sys.dm_os_nodes WHERE node_state_desc <> 'ONLINE DAC';
END TRY BEGIN CATCH PRINT '  [!] vNUMA analizi yapilamadi.'; END CATCH;

-- ============================================================
-- BÖLÜM 03: CPU ANALİZİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 03: CPU Analizi';

DECLARE @maxdop    INT = 0;
DECLARE @ctfp      INT = 5;
DECLARE @cpu_count INT = 4;
DECLARE @numa_cnt  INT = 1;
DECLARE @MaxRunnable INT = 0;

BEGIN TRY SELECT @maxdop     = CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'max degree of parallelism'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @ctfp       = CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'cost threshold for parallelism'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @cpu_count  = cpu_count FROM sys.dm_os_sys_info; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @numa_cnt   = COUNT(*) FROM sys.dm_os_nodes WHERE node_state_desc <> 'ONLINE DAC'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @MaxRunnable = MAX(runnable_tasks_count) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'; END TRY BEGIN CATCH END CATCH;

BEGIN TRY
    SELECT scheduler_id, cpu_id, status, is_online,
           current_tasks_count, runnable_tasks_count,
           current_workers_count, active_workers_count,
           work_queue_count, pending_disk_io_count
    FROM sys.dm_os_schedulers
    WHERE status = 'VISIBLE ONLINE'
    ORDER BY runnable_tasks_count DESC;
END TRY BEGIN CATCH PRINT '  [!] Scheduler bilgisi alinamadi.'; END CATCH;

INSERT INTO #AnalysisResults VALUES (
    'CPU', 'Runnable Task Queue (CPU Baskisi)',
    'Max Runnable: ' + CAST(ISNULL(@MaxRunnable,0) AS NVARCHAR),
    CASE WHEN ISNULL(@MaxRunnable,0) > 0 THEN '0 olmali - CPU darbogazi!' ELSE '0 - Normal' END,
    'ESXi vCPU scheduling gecikmesi runnable task birikimini tetikler. CPU Ready degerini vSphere''den kontrol edin.',
    CASE WHEN ISNULL(@MaxRunnable,0) > 2 THEN 'KRITIK' WHEN ISNULL(@MaxRunnable,0) > 0 THEN 'YUKSEK' ELSE 'BILGI' END
);

DECLARE @rec_maxdop INT;
SET @rec_maxdop = CASE
    WHEN @cpu_count <= 8 THEN @cpu_count
    WHEN @numa_cnt > 1   THEN @cpu_count / @numa_cnt
    ELSE 8 END;

INSERT INTO #AnalysisResults VALUES (
    'CPU', 'MAXDOP',
    'Mevcut: ' + CAST(@maxdop AS NVARCHAR),
    'Onerilen: ' + CAST(@rec_maxdop AS NVARCHAR),
    'EXEC sp_configure ''max degree of parallelism'', ' + CAST(@rec_maxdop AS NVARCHAR) + '; RECONFIGURE;',
    CASE WHEN @maxdop = 0 THEN 'YUKSEK' WHEN @maxdop <> @rec_maxdop THEN 'ORTA' ELSE 'BILGI' END
);

INSERT INTO #AnalysisResults VALUES (
    'CPU', 'Cost Threshold for Parallelism',
    'Mevcut: ' + CAST(@ctfp AS NVARCHAR),
    'Onerilen: 50',
    'Default 5 cok dusuk. ESXi''de paralel plan overhead daha yuksek. EXEC sp_configure ''cost threshold for parallelism'', 50; RECONFIGURE;',
    CASE WHEN @ctfp <= 5 THEN 'YUKSEK' ELSE 'BILGI' END
);

-- ============================================================
-- BÖLÜM 04: MEMORY ANALİZİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 04: Memory Analizi';

DECLARE @max_mem            BIGINT = 2147483647;
DECLARE @min_mem            BIGINT = 0;
DECLARE @total_ram_mb       BIGINT = 0;
DECLARE @avail_ram_mb       BIGINT = 0;
DECLARE @lpim_kb            BIGINT = 0;
DECLARE @ple_val            BIGINT = 0;
DECLARE @qmem_per_query     BIGINT = 0;  -- min memory per query (KB)
DECLARE @qmem_limit_pct     BIGINT = 25; -- query wait (memory grant timeout sn)
DECLARE @qmem_wait          BIGINT = 0;
DECLARE @index_create_mem   BIGINT = 0;
DECLARE @mem_model          NVARCHAR(50) = '';

BEGIN TRY SELECT @max_mem          = CAST(value_in_use AS BIGINT) FROM sys.configurations WHERE name = 'max server memory (MB)'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @min_mem          = CAST(value_in_use AS BIGINT) FROM sys.configurations WHERE name = 'min server memory (MB)'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @qmem_per_query   = CAST(value_in_use AS BIGINT) FROM sys.configurations WHERE name = 'min memory per query (KB)'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @qmem_wait        = CAST(value_in_use AS BIGINT) FROM sys.configurations WHERE name = 'query wait (s)'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @index_create_mem = CAST(value_in_use AS BIGINT) FROM sys.configurations WHERE name = 'index create memory (KB)'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @total_ram_mb = total_physical_memory_kb / 1024, @avail_ram_mb = available_physical_memory_kb / 1024 FROM sys.dm_os_sys_memory; END TRY BEGIN CATCH PRINT '  [!] Sistem memory bilgisi alinamadi.'; END CATCH;
BEGIN TRY SELECT @lpim_kb = locked_page_allocations_kb FROM sys.dm_os_process_memory; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @ple_val = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' AND object_name LIKE '%Buffer Manager%'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY
    -- AWE / konvansiyonel memory model tespiti
    SELECT @mem_model = CASE WHEN locked_page_allocations_kb > 0 THEN 'Lock Pages (AWE)' ELSE 'Konvansiyonel' END
    FROM sys.dm_os_process_memory;
END TRY BEGIN CATCH END CATCH;

-- Sistem bellek genel durumu
BEGIN TRY
    SELECT
        total_physical_memory_kb / 1024     AS [Toplam RAM MB],
        available_physical_memory_kb / 1024 AS [Bos RAM MB],
        total_page_file_kb / 1024           AS [Page File MB],
        available_page_file_kb / 1024       AS [Bos Page File MB],
        system_memory_state_desc            AS [Bellek Durumu]
    FROM sys.dm_os_sys_memory;
END TRY BEGIN CATCH PRINT '  [!] dm_os_sys_memory okunamadi.'; END CATCH;

-- SQL process bellek detayı
BEGIN TRY
    SELECT
        physical_memory_in_use_kb / 1024    AS [SQL RAM Kullanim MB],
        locked_page_allocations_kb / 1024   AS [LPIM MB],
        large_page_allocations_kb / 1024    AS [Large Pages MB],
        page_fault_count                    AS [Page Fault],
        memory_utilization_percentage       AS [Kullanim Yuzde],
        virtual_address_space_committed_kb / 1024 AS [VAS Committed MB],
        virtual_address_space_available_kb  / 1024 AS [VAS Available MB]
    FROM sys.dm_os_process_memory;
END TRY BEGIN CATCH PRINT '  [!] dm_os_process_memory detayi alinamadi.'; END CATCH;

-- Tüm memory konfigürasyonlarını tek sorguda göster
BEGIN TRY
    SELECT name AS [Parametre], value AS [Ayarlanan], value_in_use AS [Aktif], description AS [Aciklama]
    FROM sys.configurations
    WHERE name IN (
        'max server memory (MB)',
        'min server memory (MB)',
        'min memory per query (KB)',
        'query wait (s)',
        'index create memory (KB)',
        'max worker threads',
        'AWE enabled'
    )
    ORDER BY name;
END TRY BEGIN CATCH PRINT '  [!] Memory konfigurasyon listesi alinamadi.'; END CATCH;

-- ---- Hesaplamalar ----
DECLARE @rec_max_mem BIGINT;
DECLARE @rec_min_mem BIGINT;
SET @rec_max_mem = CAST(@total_ram_mb * 0.85 AS BIGINT);
IF @rec_max_mem > @total_ram_mb - 4096 SET @rec_max_mem = @total_ram_mb - 4096;
IF @rec_max_mem <= 0 SET @rec_max_mem = 2048;
-- Min memory: max'ın %25-30'u makul bir taban sağlar
SET @rec_min_mem = CAST(@rec_max_mem * 0.25 AS BIGINT);

-- ---- #AnalysisResults eklemeleri ----

-- Max Server Memory
INSERT INTO #AnalysisResults VALUES (
    'Memory', 'Max Server Memory',
    'Mevcut: ' + CAST(@max_mem AS NVARCHAR) + CASE WHEN @max_mem = 2147483647 THEN ' MB (SINIRSIZ - TEHLIKELI)' ELSE ' MB' END,
    'Onerilen: ' + CAST(@rec_max_mem AS NVARCHAR) + ' MB  (RAM''in ~%%85i)',
    'ESXi''de sinirsiz birakmak balloon driver ile baskiya girer, PLE duser. '
    + 'EXEC sp_configure ''max server memory (MB)'', ' + CAST(@rec_max_mem AS NVARCHAR) + '; RECONFIGURE;',
    CASE WHEN @max_mem = 2147483647 THEN 'KRITIK'
         WHEN @max_mem > @rec_max_mem * 1.1 THEN 'YUKSEK'
         WHEN @max_mem < @rec_max_mem * 0.5 THEN 'YUKSEK'  -- cok dusuk ayarlanmis
         ELSE 'BILGI' END
);

-- Min Server Memory
INSERT INTO #AnalysisResults VALUES (
    'Memory', 'Min Server Memory',
    'Mevcut: ' + CAST(@min_mem AS NVARCHAR) + ' MB'
        + CASE WHEN @min_mem = 0 THEN ' (Ayarlanmamis)' ELSE '' END,
    'Onerilen: ' + CAST(@rec_min_mem AS NVARCHAR) + ' MB  (Max''in ~%%25i)',
    'Min memory 0 iken ESXi memory baskisinda SQL buffer pool''u tamamen bosaltabilir. '
    + 'Makul bir taban tanimlamak PLE stabilizasyonu saglar. '
    + 'EXEC sp_configure ''min server memory (MB)'', ' + CAST(@rec_min_mem AS NVARCHAR) + '; RECONFIGURE;  '
    + '-- Not: min memory max memory''yi gecemez.',
    CASE WHEN @min_mem = 0 THEN 'YUKSEK'
         WHEN @min_mem > @max_mem * 0.9 THEN 'KRITIK'  -- min neredeyse max kadar, tehlikeli
         ELSE 'BILGI' END
);

-- Min Memory Per Query
INSERT INTO #AnalysisResults VALUES (
    'Memory', 'Min Memory Per Query (KB)',
    'Mevcut: ' + CAST(@qmem_per_query AS NVARCHAR) + ' KB'
        + CASE WHEN @qmem_per_query = 1024 THEN ' (Default)' ELSE '' END,
    'OLTP: 1024 KB (default) | Analitik/Raporlama: 2048-4096 KB',
    'Her sorguya garantilenen minimum memory. Cok dusuksa buyuk sort/hash join operasyonlari tempdb''e tasar (ESXi I/O baskisi). '
    + 'Cok yuksekse az sorgu essamanda calisir (memory grant kuyruklari olusur). '
    + 'EXEC sp_configure ''min memory per query (KB)'', 2048; RECONFIGURE;',
    CASE WHEN @qmem_per_query < 512  THEN 'YUKSEK'
         WHEN @qmem_per_query > 8192 THEN 'ORTA'
         ELSE 'BILGI' END
);

-- Query Wait (memory grant timeout)
INSERT INTO #AnalysisResults VALUES (
    'Memory', 'Query Wait - Memory Grant Timeout (sn)',
    'Mevcut: ' + CAST(@qmem_wait AS NVARCHAR) + CASE WHEN @qmem_wait = -1 THEN ' (Otomatik - 25x sorgu maliyeti)' ELSE ' sn' END,
    '-1 (Otomatik) veya 30-60 sn onerilir',
    'Memory grant bekleyemeyen sorgular hata verir veya tempdb''e tasar. '
    + 'Default -1 genellikle uygundur. Cok uzun bekleme yasaniyorsa max server memory yetersizdir. '
    + 'EXEC sp_configure ''query wait (s)'', -1; RECONFIGURE;  -- -1 = otomatik',
    CASE WHEN @qmem_wait > 120 THEN 'ORTA'
         WHEN @qmem_wait = 0   THEN 'YUKSEK'  -- 0 = hic bekleme, hemen hata
         ELSE 'BILGI' END
);

-- Index Create Memory
INSERT INTO #AnalysisResults VALUES (
    'Memory', 'Index Create Memory (KB)',
    'Mevcut: ' + CAST(@index_create_mem AS NVARCHAR) + CASE WHEN @index_create_mem = 0 THEN ' KB (Otomatik)' ELSE ' KB' END,
    '0 (Otomatik) onerilir',
    'Index olusturma ve rebuild sirasinda kullanilacak max memory. '
    + '0 = otomatik yonetim en iyi tercih. Manuel deger verilmisse ve cok dusuksa index rebuild islemleri yavaslar, '
    + 'ESXi''de uzun surecek rebuild operasyonu I/O baskisi yaratir. '
    + 'EXEC sp_configure ''index create memory (KB)'', 0; RECONFIGURE;',
    CASE WHEN @index_create_mem > 0 AND @index_create_mem < 4096 THEN 'ORTA'
         WHEN @index_create_mem > 524288 THEN 'ORTA'  -- cok yuksek
         ELSE 'BILGI' END
);

-- Min/Max memory tutarsizlik kontrolü
IF @min_mem > @max_mem
    INSERT INTO #AnalysisResults VALUES (
        'Memory', 'Min/Max Memory Tutarsizligi!',
        'Min: ' + CAST(@min_mem AS NVARCHAR) + ' MB > Max: ' + CAST(@max_mem AS NVARCHAR) + ' MB',
        'Min memory her zaman max memory''den kucuk olmali',
        'Min server memory max server memory''den buyukse SQL Server baslarken hata verebilir veya beklenmedik davranis gosterir. '
        + 'Hemen duzeltilmeli.',
        'KRITIK'
    );

-- Memory model uyarisi
INSERT INTO #AnalysisResults VALUES (
    'Memory', 'Memory Model (LPIM Durumu)',
    @mem_model,
    'ESXi uzerinde Lock Pages (AWE) aktif olmali',
    'LPIM olmadan ESXi balloon driver SQL buffer pool''u daraltabilir. '
    + 'secpol.msc > Local Policies > User Rights Assignment > Lock Pages in Memory > SQL service account ekle.',
    CASE WHEN @mem_model = 'Konvansiyonel' THEN 'YUKSEK' ELSE 'BILGI' END
);

-- Aktif memory grant bekleyen sorgu sayisi
BEGIN TRY
    DECLARE @grant_waiting INT = 0;
    SELECT @grant_waiting = COUNT(*) FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;
    INSERT INTO #AnalysisResults VALUES (
        'Memory', 'Memory Grant Kuyruktaki Sorgu Sayisi',
        CAST(@grant_waiting AS NVARCHAR) + ' sorgu memory grant bekliyor',
        '0 olmali',
        'Memory grant kuyrugu doluysa max server memory yetersiz veya sorgular asiri memory istiyor. '
        + 'Uzun sure bekleyen sorgular RESOURCE_SEMAPHORE wait ile gorunur.',
        CASE WHEN @grant_waiting > 5 THEN 'KRITIK'
             WHEN @grant_waiting > 0 THEN 'YUKSEK'
             ELSE 'BILGI' END
    );
END TRY BEGIN CATCH END CATCH;

-- Memory grant bekleyen sorgular detay
BEGIN TRY
    SELECT
        session_id                          AS [Session],
        requested_memory_kb / 1024          AS [Istenen MB],
        granted_memory_kb / 1024            AS [Verilen MB],
        used_memory_kb / 1024               AS [Kullanilan MB],
        max_used_memory_kb / 1024           AS [Max Kullanilan MB],
        CASE WHEN grant_time IS NULL THEN 'BEKLIYOR!' ELSE 'Verildi' END AS [Durum],
        wait_time_ms                        AS [Bekleme ms],
        queue_id                            AS [Queue],
        resource_semaphore_id               AS [Semaphore]
    FROM sys.dm_exec_query_memory_grants
    ORDER BY CASE WHEN grant_time IS NULL THEN 0 ELSE 1 END, requested_memory_kb DESC;
END TRY BEGIN CATCH PRINT '  [!] Memory grant detay alinamadi.'; END CATCH;

-- Workspace memory (sort/hash kullanimi)
BEGIN TRY
    SELECT
        SUM(CASE WHEN type = 'MEMORYCLERK_SQLQERESERVATIONS' THEN pages_kb ELSE 0 END) / 1024 AS [Query Exec Rezervasyon MB],
        SUM(CASE WHEN type = 'OBJECTSTORE_LOCK_MANAGER'      THEN pages_kb ELSE 0 END) / 1024 AS [Lock Manager MB],
        SUM(CASE WHEN type = 'CACHESTORE_SQLCP'              THEN pages_kb ELSE 0 END) / 1024 AS [SQL Plan Cache MB],
        SUM(CASE WHEN type = 'CACHESTORE_OBJCP'              THEN pages_kb ELSE 0 END) / 1024 AS [Object Plan Cache MB],
        SUM(CASE WHEN type = 'MEMORYCLERK_SOSNODE'           THEN pages_kb ELSE 0 END) / 1024 AS [SOS Node MB],
        SUM(pages_kb) / 1024                                                           AS [Toplam Clerk MB]
    FROM sys.dm_os_memory_clerks;
END TRY BEGIN CATCH PRINT '  [!] Workspace memory ozeti alinamadi.'; END CATCH;

-- Memory node bazlı dağılım (NUMA)
BEGIN TRY
    SELECT
        memory_node_id                      AS [Memory Node],
        virtual_address_space_reserved_kb / 1024  AS [VAS Reserved MB],
        virtual_address_space_committed_kb / 1024  AS [VAS Committed MB],
        locked_page_allocations_kb / 1024   AS [Locked Pages MB],
        pages_kb / 1024                     AS [Pages MB],
        foreign_committed_kb / 1024         AS [Foreign Committed MB]
    FROM sys.dm_os_memory_nodes
    WHERE memory_node_id <> 64;  -- DAC node haric
END TRY BEGIN CATCH PRINT '  [!] Memory node bilgisi alinamadi.'; END CATCH;

-- PLE hesaplama ve ekleme
DECLARE @ple_threshold BIGINT;
SET @ple_threshold = (@total_ram_mb / 4096) * 300;
IF @ple_threshold < 300 SET @ple_threshold = 300;

INSERT INTO #AnalysisResults VALUES (
    'Memory', 'Page Life Expectancy (PLE)',
    'Mevcut: ' + CAST(ISNULL(@ple_val,0) AS NVARCHAR) + ' sn',
    'Min olmasi gereken: ' + CAST(@ple_threshold AS NVARCHAR) + ' sn  (Her 4 GB RAM icin 300 sn baz)',
    'PLE dusukse buffer pool yetersiz veya ESXi balloon driver aktif. '
    + 'PLE aniden dusuyorsa vm.balloon.target degerini vSphere''den kontrol edin.',
    CASE WHEN ISNULL(@ple_val,0) < 300              THEN 'KRITIK'
         WHEN ISNULL(@ple_val,0) < @ple_threshold   THEN 'YUKSEK'
         ELSE 'BILGI' END
);

BEGIN TRY
    SELECT TOP 15 type AS [Clerk Tipi], name AS [Ad], pages_kb / 1024 AS [Kullanim MB]
    FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC;
END TRY BEGIN CATCH PRINT '  [!] Memory clerks alinamadi.'; END CATCH;

-- ============================================================
-- BÖLÜM 05: I/O & DİSK ANALİZİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 05: I/O & Disk Analizi';

BEGIN TRY
    SELECT
        DB_NAME(vfs.database_id)            AS [Veritabani],
        mf.physical_name                    AS [Dosya Yolu],
        mf.type_desc                        AS [Tip],
        vfs.num_of_reads                    AS [Okuma],
        vfs.num_of_writes                   AS [Yazma],
        vfs.io_stall_read_ms                AS [Okuma Gecikme ms],
        vfs.io_stall_write_ms               AS [Yazma Gecikme ms],
        CASE WHEN vfs.num_of_reads  > 0 THEN CAST(vfs.io_stall_read_ms  * 1.0 / vfs.num_of_reads  AS DECIMAL(10,2)) ELSE 0 END AS [Ort Okuma ms],
        CASE WHEN vfs.num_of_writes > 0 THEN CAST(vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes AS DECIMAL(10,2)) ELSE 0 END AS [Ort Yazma ms],
        vfs.num_of_bytes_read  / 1048576    AS [Okunan MB],
        vfs.num_of_bytes_written / 1048576  AS [Yazilan MB]
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
    JOIN sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
    ORDER BY vfs.io_stall DESC;
END TRY BEGIN CATCH PRINT '  [!] I/O istatistikleri alinamadi.'; END CATCH;

BEGIN TRY
    INSERT INTO #AnalysisResults
    SELECT
        'Disk I/O',
        'IO Gecikme: ' + DB_NAME(vfs.database_id) + ' (' + mf.type_desc + ')',
        'Ort Okuma: ' + CAST(CAST(vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads,0) AS DECIMAL(10,1)) AS NVARCHAR) + ' ms',
        'Data < 10ms, Log < 5ms olmali',
        'ESXi: Thin VMDK yerine Eager Zeroed Thick kullan, PVSCSI controller sec, log VMDK ayri datastorda olmali.',
        CASE
            WHEN vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads,0) > 30 THEN 'KRITIK'
            WHEN vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads,0) > 10 THEN 'YUKSEK'
            ELSE 'ORTA' END
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
    JOIN sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
    WHERE vfs.num_of_reads > 100
      AND vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads,0) > 10;
END TRY BEGIN CATCH PRINT '  [!] I/O uyari satirlari eklenemedi.'; END CATCH;

-- ============================================================
-- BÖLÜM 06: TEMPDB ANALİZİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 06: TempDB Analizi';

-- ---- Değişkenler ----
DECLARE @tmpfiles           INT   = 0;   -- mevcut data file sayısı
DECLARE @rec_tmpfiles       INT   = 4;   -- önerilen data file sayısı
DECLARE @tmp_data_size_mb   BIGINT = 0;  -- toplam data boyutu MB
DECLARE @tmp_log_size_mb    BIGINT = 0;  -- log boyutu MB
DECLARE @tmp_data_used_mb   BIGINT = 0;  -- kullanılan alan MB
DECLARE @tmp_log_used_mb    BIGINT = 0;  -- log kullanılan MB
DECLARE @tmp_max_size_mb    BIGINT = 0;  -- en büyük data file max boyutu
DECLARE @tmp_growth_pct     INT   = 0;   -- yüzde büyüme kullanan dosya sayısı
DECLARE @tmp_unequal        INT   = 0;   -- eşit boyutta olmayan dosya sayısı
DECLARE @tmp_diff_path      INT   = 0;   -- farklı fiziksel yolda olan dosya sayısı
DECLARE @tmp_has_maxsize    INT   = 0;   -- max size kısıtı olan dosya sayısı
DECLARE @rec_initial_mb     BIGINT = 0;  -- önerilen başlangıç boyutu MB
DECLARE @rec_growth_mb      BIGINT = 0;  -- önerilen büyüme adımı MB

BEGIN TRY SELECT @tmpfiles       = COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @tmp_data_size_mb = SUM(size) * 8 / 1024 FROM sys.master_files WHERE database_id = 2 AND type = 0; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @tmp_log_size_mb  = SUM(size) * 8 / 1024 FROM sys.master_files WHERE database_id = 2 AND type = 1; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @tmp_growth_pct   = COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0 AND is_percent_growth = 1; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @tmp_has_maxsize  = COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0 AND max_size > 0 AND max_size <> -1; END TRY BEGIN CATCH END CATCH;

-- Kullanılan alan (sadece online iken çalışır)
BEGIN TRY
    SELECT @tmp_data_used_mb = SUM(unallocated_extent_page_count) * 8 / 1024
    FROM sys.dm_db_file_space_usage WHERE database_id = 2;
END TRY BEGIN CATCH END CATCH;

BEGIN TRY
    SELECT @tmp_log_used_mb = log_reuse_wait_desc_int
    FROM (SELECT TOP 1 used_log_space_in_bytes / 1048576 AS log_reuse_wait_desc_int
          FROM sys.dm_db_log_space_usage) x;
END TRY BEGIN CATCH END CATCH;

-- Eşit boyut kontrolü: en büyük ile en küçük aynı mı?
BEGIN TRY
    SELECT @tmp_unequal = CASE WHEN MAX(size) <> MIN(size) THEN COUNT(*) ELSE 0 END
    FROM sys.master_files WHERE database_id = 2 AND type = 0;
END TRY BEGIN CATCH END CATCH;

-- Farklı klasörde dosya var mı?
BEGIN TRY
    SELECT @tmp_diff_path = COUNT(DISTINCT LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1))
    FROM sys.master_files WHERE database_id = 2 AND type = 0;
END TRY BEGIN CATCH END CATCH;

-- Önerilen data file sayısı: CPU sayısı kadar, max 8
SET @rec_tmpfiles = CASE WHEN @cpu_count > 8 THEN 8 ELSE @cpu_count END;

-- Önerilen başlangıç boyutu:
--   Toplam mevcut boyutu önerilen file sayısına böl,
--   en az 1024 MB, 1024'ün katına yuvarla
SET @rec_initial_mb = CASE
    WHEN @tmp_data_size_mb / NULLIF(@rec_tmpfiles, 0) < 1024 THEN 1024
    ELSE ((@tmp_data_size_mb / @rec_tmpfiles) / 1024 + 1) * 1024
END;

-- Önerilen büyüme adımı: başlangıç boyutunun %10'u, 256'dan az olamaz, 1024'ten fazla gerekmez
SET @rec_growth_mb = CASE
    WHEN @rec_initial_mb * 0.1 < 256  THEN 256
    WHEN @rec_initial_mb * 0.1 > 1024 THEN 1024
    ELSE CAST(@rec_initial_mb * 0.1 AS BIGINT)
END;

-- ---- Mevcut Durum Tablosu ----
BEGIN TRY
    SELECT
        file_id                             AS [File ID],
        name                                AS [Dosya Adi],
        type_desc                           AS [Tip],
        physical_name                       AS [Fiziksel Yol],
        size * 8 / 1024                     AS [Boyut MB],
        CASE max_size
            WHEN -1 THEN 'Sinirsiz'
            WHEN  0 THEN 'Buyume Yok!'
            ELSE CAST(max_size * 8 / 1024 AS NVARCHAR) + ' MB (KISITLI!)'
        END                                 AS [Max Boyut],
        CASE is_percent_growth
            WHEN 1 THEN CAST(growth AS NVARCHAR) + '% (YUZDE - ONERILMEZ!)'
            ELSE        CAST(growth * 8 / 1024 AS NVARCHAR) + ' MB'
        END                                 AS [Otomatik Buyume],
        CASE is_percent_growth
            WHEN 1 THEN 'Sabit MB''ye gecin: ' + CAST(@rec_growth_mb AS NVARCHAR) + ' MB'
            WHEN 0 THEN
                CASE WHEN growth * 8 / 1024 < 256  THEN 'Cok kucuk buyume - artirin: ' + CAST(@rec_growth_mb AS NVARCHAR) + ' MB'
                     WHEN growth * 8 / 1024 > 2048 THEN 'Cok buyuk buyume - azaltin: ' + CAST(@rec_growth_mb AS NVARCHAR) + ' MB'
                     ELSE 'Uygun'
                END
        END                                 AS [Buyume Degerlendirme],
        CASE
            WHEN size * 8 / 1024 <> (SELECT MIN(size) * 8 / 1024 FROM sys.master_files WHERE database_id = 2 AND type = 0)
            THEN 'ESIT DEGIL - contention riski!'
            ELSE 'Esit'
        END                                 AS [Esit Boyut Kontrolu]
    FROM sys.master_files
    WHERE database_id = 2
    ORDER BY type, file_id;
END TRY BEGIN CATCH PRINT '  [!] TempDB dosya bilgisi alinamadi.'; END CATCH;

-- ---- Anlık kullanım ----
BEGIN TRY
    SELECT
        SUM(user_object_reserved_page_count)     * 8 / 1024 AS [Kullanici Nesneleri MB],
        SUM(internal_object_reserved_page_count) * 8 / 1024 AS [Ic Nesneler MB (sort/hash/spool)],
        SUM(version_store_reserved_page_count)   * 8 / 1024 AS [Version Store MB (row versioning)],
        SUM(unallocated_extent_page_count)       * 8 / 1024 AS [Bos Alan MB],
        SUM(mixed_extent_page_count)             * 8 / 1024 AS [Mixed Extent MB]
    FROM sys.dm_db_file_space_usage;
END TRY BEGIN CATCH PRINT '  [!] TempDB kullanim detayi alinamadi.'; END CATCH;

-- Version store (row versioning) baskısı
BEGIN TRY
    SELECT
        SUM(version_store_reserved_page_count) * 8 / 1024   AS [Version Store MB],
        SUM(user_object_reserved_page_count)   * 8 / 1024   AS [Kullanici Nesneleri MB],
        SUM(internal_object_reserved_page_count) * 8 / 1024 AS [Ic Nesneler MB],
        CAST(SUM(version_store_reserved_page_count) * 100.0
            / NULLIF(SUM(user_object_reserved_page_count +
                         internal_object_reserved_page_count +
                         version_store_reserved_page_count), 0) AS DECIMAL(5,1)) AS [Version Store Yuzde]
    FROM sys.dm_db_file_space_usage;
END TRY BEGIN CATCH PRINT '  [!] Version store bilgisi alinamadi.'; END CATCH;

-- TempDB'ye en çok yazan session'lar
BEGIN TRY
    SELECT TOP 10
        s.session_id,
        s.login_name                                        AS [Kullanici],
        s.host_name                                         AS [Host],
        tsu.user_objects_alloc_page_count * 8 / 1024        AS [Kullanici Nesne MB],
        tsu.internal_objects_alloc_page_count * 8 / 1024    AS [Ic Nesne MB],
        (tsu.user_objects_alloc_page_count +
         tsu.internal_objects_alloc_page_count) * 8 / 1024  AS [Toplam TempDB MB],
        tsu.user_objects_dealloc_page_count * 8 / 1024      AS [Serbest Birakilan MB],
        SUBSTRING(st.text, 1, 300)                          AS [Son Sorgu]
    FROM sys.dm_db_session_space_usage tsu
    JOIN sys.dm_exec_sessions s ON tsu.session_id = s.session_id
    OUTER APPLY (
        SELECT TOP 1 text FROM sys.dm_exec_requests r
        CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
        WHERE r.session_id = tsu.session_id
    ) st
    WHERE (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) > 0
    ORDER BY (tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) DESC;
END TRY BEGIN CATCH PRINT '  [!] TempDB session kullanimi alinamadi.'; END CATCH;

-- TempDB I/O istatistikleri
BEGIN TRY
    SELECT
        mf.name                             AS [Dosya],
        mf.physical_name                    AS [Yol],
        vfs.num_of_reads                    AS [Okuma],
        vfs.num_of_writes                   AS [Yazma],
        CASE WHEN vfs.num_of_reads  > 0 THEN CAST(vfs.io_stall_read_ms  * 1.0 / vfs.num_of_reads  AS DECIMAL(10,2)) ELSE 0 END AS [Ort Okuma ms],
        CASE WHEN vfs.num_of_writes > 0 THEN CAST(vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes AS DECIMAL(10,2)) ELSE 0 END AS [Ort Yazma ms],
        vfs.io_stall_read_ms + vfs.io_stall_write_ms AS [Toplam Stall ms]
    FROM sys.dm_io_virtual_file_stats(2, NULL) vfs
    JOIN sys.master_files mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
    ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;
END TRY BEGIN CATCH PRINT '  [!] TempDB I/O istatistikleri alinamadi.'; END CATCH;

-- Trace flag 1118 / 1117 durumu (eski versiyonlarda gerekli)
BEGIN TRY
    INSERT INTO #AnalysisResults
    SELECT
        'TempDB', 'Trace Flag 1118 / 1117 (SQL 2014 ve alti)',
        'Manuel kontrol gerekli',
        'SQL 2016+ otomatik. Eski versiyonlarda DBCC TRACEON(1118,-1) ve DBCC TRACEON(1117,-1)',
        'TF1118: Mixed extent tahsisini onler, tek tip extent kullanir. '
        + 'TF1117: Tum data dosyalarini esit buyutur. '
        + 'SQL 2016 ve sonrasinda bu flaglar otomatik aktiftir.',
        'BILGI'
    WHERE CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) < 13;
END TRY BEGIN CATCH END CATCH;

-- ---- #AnalysisResults eklemeleri ----

-- Data file sayısı
INSERT INTO #AnalysisResults VALUES (
    'TempDB', 'Data File Sayisi',
    'Mevcut: ' + CAST(@tmpfiles AS NVARCHAR) + ' adet',
    'Onerilen: ' + CAST(@rec_tmpfiles AS NVARCHAR) + ' adet  (vCPU sayisi kadar, max 8)',
    'Az data file olunca tum sessionlar ayni GAM/SGAM sayfasina yazip PAGELATCH contention olusturur. '
    + 'Tum dosyalar ESXi''de ayni VMDK uzerinde, ayni klasorde olmali.',
    CASE WHEN @tmpfiles = 1                    THEN 'KRITIK'
         WHEN @tmpfiles < @rec_tmpfiles        THEN 'YUKSEK'
         WHEN @tmpfiles > @rec_tmpfiles * 2    THEN 'ORTA'   -- gereğinden fazla da sorun
         ELSE 'BILGI' END
);

-- Eşit boyut kontrolü
INSERT INTO #AnalysisResults VALUES (
    'TempDB', 'Data File Esit Boyut Kontrolu',
    CASE WHEN @tmp_unequal > 0 THEN 'ESIT DEGIL - ' + CAST(@tmp_unequal AS NVARCHAR) + ' farkli boyutlu dosya!' ELSE 'Esit' END,
    'Tum data dosyalari esit boyutta olmali',
    'Dosyalar esit degilse SQL round-robin tahsisi bozulur, buyuk dosyaya daha fazla yuk biner. '
    + 'Tum dosyalari ' + CAST(@rec_initial_mb AS NVARCHAR) + ' MB''a esitleyin.',
    CASE WHEN @tmp_unequal > 0 THEN 'YUKSEK' ELSE 'BILGI' END
);

-- Yüzde büyüme kontrolü
INSERT INTO #AnalysisResults VALUES (
    'TempDB', 'Data File Buyume Sekli',
    CASE WHEN @tmp_growth_pct > 0
         THEN CAST(@tmp_growth_pct AS NVARCHAR) + ' dosya yuzde bazli buyuyor!'
         ELSE 'MB bazli buyume - dogru' END,
    'Sabit MB buyume olmali. Onerilen: ' + CAST(@rec_growth_mb AS NVARCHAR) + ' MB',
    'Yuzde buyume her adimda farkli miktarda yer talep eder, ESXi datastoreda fragmantasyon ve I/O spike yaratir. '
    + 'Ayrica dosyalar arasi esitsizlige yol acar.',
    CASE WHEN @tmp_growth_pct > 0 THEN 'YUKSEK' ELSE 'BILGI' END
);

-- Max size kısıtı
INSERT INTO #AnalysisResults VALUES (
    'TempDB', 'Data File Max Boyut Kisiti',
    CASE WHEN @tmp_has_maxsize > 0
         THEN CAST(@tmp_has_maxsize AS NVARCHAR) + ' dosyada max boyut kisiti var!'
         ELSE 'Sinirsiz (veya -1)' END,
    'TempDB data dosyalarinda max size kisiti OLMAMALI (-1 = sinirsiz)',
    'Max size kisiti TempDB dolunca "tempdb is full" hatasina neden olur. '
    + 'Disk dolmasini engellemek icin disk kotasi veya monitor kullanin, max size degil. '
    + 'ALTER DATABASE tempdb MODIFY FILE (NAME=N''tempdev'', MAXSIZE=UNLIMITED);',
    CASE WHEN @tmp_has_maxsize > 0 THEN 'KRITIK' ELSE 'BILGI' END
);

-- Farklı path kontrolü
INSERT INTO #AnalysisResults VALUES (
    'TempDB', 'Data File Fiziksel Konum',
    CASE WHEN @tmp_diff_path > 1
         THEN CAST(@tmp_diff_path AS NVARCHAR) + ' farkli klasor/disk kullaniliyor!'
         ELSE 'Tum dosyalar ayni konumda' END,
    'Tum TempDB data dosyalari ayni datastore/klasorde olmali',
    'Farkli datastoreda dosyalar olunca I/O dengesi bozulur, ESXi''de VMDK bant genisligi esit paylasilamaz.',
    CASE WHEN @tmp_diff_path > 1 THEN 'ORTA' ELSE 'BILGI' END
);

-- Hazır ALTER komutları üret
PRINT '';
PRINT '-- ============================================================';
PRINT '-- TEMPDB IDEAL KONFIGURASYON KOMUTLARI (ortama gore duzenleyin)';
PRINT '-- ============================================================';
PRINT '-- Mevcut dosya sayisi  : ' + CAST(@tmpfiles AS NVARCHAR);
PRINT '-- Onerilen dosya sayisi: ' + CAST(@rec_tmpfiles AS NVARCHAR);
PRINT '-- Onerilen boyut/dosya : ' + CAST(@rec_initial_mb AS NVARCHAR) + ' MB';
PRINT '-- Onerilen buyume adimi: ' + CAST(@rec_growth_mb AS NVARCHAR) + ' MB';
PRINT '-- NOT: SQL Server yeniden baslatilmadan data file silinemez!';
PRINT '';
PRINT '-- 1) Mevcut dosyalari esit boyuta getir:';
BEGIN TRY
    SELECT
        'ALTER DATABASE tempdb MODIFY FILE (NAME=N''' + name + ''', SIZE='
        + CAST(@rec_initial_mb AS NVARCHAR) + 'MB, MAXSIZE=UNLIMITED, FILEGROWTH='
        + CAST(@rec_growth_mb AS NVARCHAR) + 'MB);' AS [Calistir]
    FROM sys.master_files
    WHERE database_id = 2 AND type = 0
    ORDER BY file_id;
END TRY BEGIN CATCH PRINT '  [!] ALTER komutlari uretildi.'; END CATCH;

BEGIN TRY
    -- Log dosyası önerisi
    SELECT
        '-- 2) Log dosyasini boyutlandir:' AS [Bilgi]
    UNION ALL
    SELECT
        'ALTER DATABASE tempdb MODIFY FILE (NAME=N''' + name + ''', SIZE='
        + CAST(CASE WHEN @rec_initial_mb / 4 < 256 THEN 256 ELSE @rec_initial_mb / 4 END AS NVARCHAR)
        + 'MB, MAXSIZE=UNLIMITED, FILEGROWTH='
        + CAST(@rec_growth_mb AS NVARCHAR) + 'MB);'
    FROM sys.master_files WHERE database_id = 2 AND type = 1;
END TRY BEGIN CATCH END CATCH;

BEGIN TRY
    -- Eksik data file'lar için ADD FILE önerisi
    IF @tmpfiles < @rec_tmpfiles
    BEGIN
        DECLARE @i INT = @tmpfiles + 1;
        DECLARE @tmppath NVARCHAR(500) = '';
        SELECT TOP 1 @tmppath = LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)))
        FROM sys.master_files WHERE database_id = 2 AND type = 0;

        PRINT '';
        PRINT '-- 3) Eksik data file''lari ekle:';
        WHILE @i <= @rec_tmpfiles
        BEGIN
            PRINT 'ALTER DATABASE tempdb ADD FILE (NAME=N''tempdev' + CAST(@i AS NVARCHAR)
                + ''', FILENAME=N''' + @tmppath + '\tempdev' + CAST(@i AS NVARCHAR) + '.ndf'
                + ''', SIZE=' + CAST(@rec_initial_mb AS NVARCHAR) + 'MB'
                + ', MAXSIZE=UNLIMITED, FILEGROWTH=' + CAST(@rec_growth_mb AS NVARCHAR) + 'MB);';
            SET @i = @i + 1;
        END
    END
END TRY BEGIN CATCH PRINT '  [!] ADD FILE komutlari uretildi.'; END CATCH;

-- ============================================================
-- BÖLÜM 07: SQL SERVER KONFİGÜRASYONU
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 07: SQL Server Konfigurasyon Analizi';

BEGIN TRY
    SELECT name AS [Parametre], value AS [Ayarlanan], value_in_use AS [Aktif], description AS [Aciklama]
    FROM sys.configurations ORDER BY name;
END TRY BEGIN CATCH PRINT '  [!] sys.configurations okunamadi.'; END CATCH;

DECLARE @adhoc  INT = 0;
DECLARE @bkcomp INT = 0;
DECLARE @netpkt INT = 4096;

BEGIN TRY SELECT @adhoc  = CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'optimize for ad hoc workloads'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @bkcomp = CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'backup compression default'; END TRY BEGIN CATCH END CATCH;
BEGIN TRY SELECT @netpkt = CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'network packet size (B)'; END TRY BEGIN CATCH END CATCH;

INSERT INTO #AnalysisResults VALUES (
    'SQL Config', 'Optimize for Ad Hoc Workloads',
    CASE @adhoc WHEN 1 THEN 'Aktif' ELSE 'Pasif' END, 'Aktif olmali',
    'Plan cache bloat onler. EXEC sp_configure ''optimize for ad hoc workloads'', 1; RECONFIGURE;',
    CASE WHEN @adhoc = 0 THEN 'YUKSEK' ELSE 'BILGI' END
);
INSERT INTO #AnalysisResults VALUES (
    'SQL Config', 'Backup Compression',
    CASE @bkcomp WHEN 1 THEN 'Aktif' ELSE 'Pasif' END, 'Aktif olmali',
    'Backup suresi ve I/O azalir. EXEC sp_configure ''backup compression default'', 1; RECONFIGURE;',
    CASE WHEN @bkcomp = 0 THEN 'ORTA' ELSE 'BILGI' END
);
INSERT INTO #AnalysisResults VALUES (
    'SQL Config', 'Network Packet Size',
    'Mevcut: ' + CAST(@netpkt AS NVARCHAR) + ' B', 'Onerilen: 8192 B',
    'VMXNET3 ile buyuk paket boyutu daha verimli. EXEC sp_configure ''network packet size'', 8192; RECONFIGURE;',
    CASE WHEN @netpkt < 8192 THEN 'ORTA' ELSE 'BILGI' END
);
INSERT INTO #AnalysisResults VALUES (
    'SQL Config', 'Instant File Initialization (IFI)',
    'Manuel kontrol gerekli', 'SQL service account''a "Perform Volume Maintenance Tasks" yetkisi verilmeli',
    'IFI aktifken data file buyumesi aninda gerceklesir, ESXi I/O spike olmaz.',
    'YUKSEK'
);

-- ============================================================
-- BÖLÜM 08: WAIT STATS
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 08: Wait Stats (ESXi Odakli)';

BEGIN TRY
    WITH W AS (
        SELECT wait_type, wait_time_ms, waiting_tasks_count,
               100.0 * wait_time_ms / NULLIF(SUM(wait_time_ms) OVER (), 0) AS pct
        FROM sys.dm_os_wait_stats
        WHERE wait_type NOT IN (
            'SLEEP_TASK','SLEEP_SYSTEMTASK','SLEEP_DBSTARTUP','SLEEP_DBTASK',
            'SLEEP_TEMPDBSTARTUP','SERVER_IDLE_CHECK','LAZYWRITER_SLEEP',
            'DISPATCHER_QUEUE_SEMAPHORE','CHECKPOINT_QUEUE','BROKER_TO_FLUSH',
            'BROKER_TASK_STOP','CLR_AUTO_EVENT','CLR_MANUAL_EVENT',
            'DBMIRROR_EVENTS_QUEUE','SQLTRACE_BUFFER_FLUSH','REQUEST_FOR_DEADLOCK_SEARCH',
            'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','FT_IFTS_SCHEDULER_IDLE_WAIT',
            'XE_DISPATCHER_WAIT','XE_TIMER_EVENT','BROKER_EVENTHANDLER','WAITFOR',
            'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','SNI_HTTP_ACCEPT','SP_SERVER_DIAGNOSTICS_SLEEP',
            'HADR_WORK_QUEUE','QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
            'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
            'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','SLEEP_MASTERDBREADY','SLEEP_MASTERMDREADY',
            'SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP','REDO_THREAD_PENDING_WORK',
            'XTP_PREEMPTIVE_TASK','HADR_FILESTREAM_IOMGR_IOCOMPLETION'
        )
        AND wait_time_ms > 0
    )
    SELECT TOP 20
        wait_type                           AS [Wait Tipi],
        waiting_tasks_count                 AS [Sayi],
        wait_time_ms / 1000                 AS [Toplam sn],
        CAST(pct AS DECIMAL(5,1))           AS [Yuzde],
        CASE WHEN waiting_tasks_count > 0 THEN CAST(wait_time_ms * 1.0 / waiting_tasks_count AS DECIMAL(10,2)) ELSE 0 END AS [Ort ms],
        CASE
            WHEN wait_type LIKE 'PAGEIOLATCH%'      THEN 'Disk I/O - Datastore yavas veya Thin VMDK'
            WHEN wait_type = 'SOS_SCHEDULER_YIELD'  THEN 'CPU baskisi - vCPU overcommit, CPU Ready yuksek olabilir'
            WHEN wait_type LIKE 'CXPACKET%'         THEN 'Paralel sorgu - MAXDOP gozden gecir'
            WHEN wait_type = 'WRITELOG'             THEN 'Log yazma yavas - Log VMDK ayri datastorda olmali'
            WHEN wait_type = 'RESOURCE_SEMAPHORE'   THEN 'Memory grant baskisi - Max server memory artir'
            WHEN wait_type = 'THREADPOOL'           THEN 'Thread baskisi'
            WHEN wait_type LIKE 'LCK_M_%'           THEN 'Lock bekleme - Uygulama kaynakli'
            ELSE '-'
        END                                 AS [ESXi Yorumu]
    FROM W ORDER BY wait_time_ms DESC;
END TRY BEGIN CATCH PRINT '  [!] Wait stats alinamadi.'; END CATCH;

BEGIN TRY
    INSERT INTO #AnalysisResults
    SELECT
        'Wait Stats',
        'Kritik Wait: ' + wait_type,
        'Toplam: ' + CAST(wait_time_ms/1000 AS NVARCHAR) + ' sn, Sayi: ' + CAST(waiting_tasks_count AS NVARCHAR),
        CASE
            WHEN wait_type LIKE 'PAGEIOLATCH%'      THEN 'Datastore IOPS artir, Eager Zeroed Thick VMDK kullan'
            WHEN wait_type = 'SOS_SCHEDULER_YIELD'  THEN 'CPU Ready kontrol et, VM CPU rezervasyonu ayarla'
            WHEN wait_type = 'WRITELOG'             THEN 'Log dosyasini SSD/NVMe datastoreya tasiy'
            WHEN wait_type LIKE 'CXPACKET%'         THEN 'MAXDOP azalt, Cost Threshold artir'
            WHEN wait_type = 'RESOURCE_SEMAPHORE'   THEN 'Max Server Memory artir'
            ELSE 'Izlemeye devam'
        END,
        CASE
            WHEN wait_type LIKE 'PAGEIOLATCH%'      THEN 'Storage katmani yavas. Storage IO Control aktif et.'
            WHEN wait_type = 'SOS_SCHEDULER_YIELD'  THEN 'CPU overcommit. Host CPU Ready yuksekse sorun var.'
            WHEN wait_type = 'WRITELOG'             THEN 'Log ve data VMDK ayri datastorda olmali.'
            ELSE wait_type + ' icin ESXi/SQL ayarlarini kontrol et.'
        END,
        CASE
            WHEN wait_type IN ('PAGEIOLATCH_EX','PAGEIOLATCH_SH','WRITELOG','SOS_SCHEDULER_YIELD') THEN 'KRITIK'
            WHEN wait_type IN ('CXPACKET','CXCONSUMER','RESOURCE_SEMAPHORE','LATCH_EX')           THEN 'YUKSEK'
            ELSE 'ORTA' END
    FROM sys.dm_os_wait_stats
    WHERE wait_type IN (
        'PAGEIOLATCH_EX','PAGEIOLATCH_SH','PAGEIOLATCH_UP',
        'SOS_SCHEDULER_YIELD','WRITELOG','CXPACKET','CXCONSUMER',
        'RESOURCE_SEMAPHORE','RESOURCE_SEMAPHORE_QUERY_COMPILE',
        'LATCH_EX','LATCH_SH','THREADPOOL','ASYNC_IO_COMPLETION'
    )
    AND wait_time_ms > 1000
    AND waiting_tasks_count > 10;
END TRY BEGIN CATCH PRINT '  [!] Wait stats analiz satirlari eklenemedi.'; END CATCH;

-- ============================================================
-- BÖLÜM 09: AĞ & BAĞLANTI
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 09: Ag & Baglanti Analizi';

BEGIN TRY
    SELECT login_name,
           COUNT(*) AS [Baglanti],
           SUM(CASE WHEN status = 'running'  THEN 1 ELSE 0 END) AS [Aktif],
           SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) AS [Uyku],
           MIN(login_time) AS [En Eski]
    FROM sys.dm_exec_sessions
    WHERE is_user_process = 1
    GROUP BY login_name ORDER BY COUNT(*) DESC;
END TRY BEGIN CATCH PRINT '  [!] Session bilgisi alinamadi.'; END CATCH;

BEGIN TRY
    SELECT net_transport, COUNT(*) AS [Sayi],
           AVG(CAST(net_packet_size AS FLOAT)) AS [Ort Paket B]
    FROM sys.dm_exec_connections GROUP BY net_transport;
END TRY BEGIN CATCH PRINT '  [!] Baglanti protokol bilgisi alinamadi.'; END CATCH;

INSERT INTO #AnalysisResults VALUES (
    'Ag', 'ESXi Network Adapter',
    'Manuel kontrol (vSphere''den)',
    'VMXNET3 olmali (E1000/E1000e degil)',
    'E1000 eski emulasyon kullanir. VMXNET3 paravirtualized ve daha hizli. VM Settings > Network Adapter tipini kontrol et.',
    'YUKSEK'
);

-- ============================================================
-- BÖLÜM 10: DOSYA YAPISI
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 10: Veritabani Dosya Yapisi';

BEGIN TRY
    SELECT DB_NAME(database_id) AS [Veritabani], name, type_desc, physical_name,
           size * 8 / 1024 AS [Boyut MB],
           CASE is_percent_growth WHEN 1 THEN CAST(growth AS NVARCHAR)+'%' ELSE CAST(growth*8/1024 AS NVARCHAR)+' MB' END AS [Buyume],
           CASE max_size WHEN -1 THEN 'Sinirsiz' WHEN 0 THEN 'Yok' ELSE CAST(max_size*8/1024 AS NVARCHAR)+' MB' END AS [Max],
           state_desc AS [Durum]
    FROM sys.master_files ORDER BY database_id, type, file_id;
END TRY BEGIN CATCH PRINT '  [!] Dosya listesi alinamadi.'; END CATCH;

BEGIN TRY
    INSERT INTO #AnalysisResults
    SELECT
        'Dosya Yapisi',
        'Yuzde Buyume: ' + DB_NAME(database_id) + ' - ' + name,
        'Mevcut: ' + CAST(growth AS NVARCHAR) + ' yuzde',
        'Sabit MB buyume onerilir (256-1024 MB)',
        'Yuzde buyume ESXi datastoreda fragmantasyon ve I/O spike yaratir.',
        'ORTA'
    FROM sys.master_files
    WHERE is_percent_growth = 1 AND database_id > 4 AND growth > 10;
END TRY BEGIN CATCH PRINT '  [!] Dosya buyume analizi yapilamadi.'; END CATCH;

-- ============================================================
-- BÖLÜM 11: BUFFER POOL & MEMORY PRESSURE
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 11: Buffer Pool & Memory Pressure';

BEGIN TRY
    SELECT DB_NAME(database_id) AS [Veritabani],
           COUNT(*) * 8 / 1024  AS [Buffer MB],
           CAST(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sys.dm_os_buffer_descriptors) AS DECIMAL(5,2)) AS [Yuzde]
    FROM sys.dm_os_buffer_descriptors
    WHERE database_id <> 32767
    GROUP BY database_id ORDER BY COUNT(*) DESC;
END TRY BEGIN CATCH PRINT '  [!] Buffer pool bilgisi alinamadi.'; END CATCH;

BEGIN TRY
    SELECT session_id,
           requested_memory_kb/1024 AS [Istenen MB],
           granted_memory_kb/1024   AS [Verilen MB],
           used_memory_kb/1024      AS [Kullanilan MB],
           wait_time_ms             AS [Bekleme ms]
    FROM sys.dm_exec_query_memory_grants
    WHERE granted_memory_kb IS NULL OR wait_time_ms > 0
    ORDER BY requested_memory_kb DESC;
END TRY BEGIN CATCH PRINT '  [!] Memory grant bilgisi alinamadi.'; END CATCH;

BEGIN TRY
    SELECT TOP 10
        ring_buffer_type,
        CAST(record AS XML).value('(/Record/ResourceMonitor/Notification)[1]',   'varchar(100)') AS [Notification],
        CAST(record AS XML).value('(/Record/MemoryRecord/MemoryUtilization)[1]', 'bigint')        AS [Memory Util]
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type IN ('RING_BUFFER_RESOURCE_MONITOR','RING_BUFFER_OOM')
    ORDER BY timestamp DESC;
END TRY BEGIN CATCH PRINT '  [!] Ring buffer bilgisi alinamadi (normal).'; END CATCH;

-- ============================================================
-- BÖLÜM 12: EN ÇOK KAYNAK TÜKETEN SORGULAR
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 12: En Cok Kaynak Tuketen Sorgular';

-- CPU'yu en çok tüketen sorgular
BEGIN TRY
    SELECT TOP 20
        CAST(qs.total_worker_time / 1000000.0 AS DECIMAL(18,2))        AS [Toplam CPU sn],
        CAST(qs.total_worker_time * 1.0 / qs.execution_count / 1000.0 AS DECIMAL(18,2)) AS [Ort CPU ms],
        qs.execution_count                                              AS [Calisma Sayisi],
        CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(18,2))       AS [Toplam Sure sn],
        CAST(qs.total_elapsed_time * 1.0 / qs.execution_count / 1000.0 AS DECIMAL(18,2)) AS [Ort Sure ms],
        qs.total_logical_reads                                          AS [Toplam Logical Read],
        CAST(qs.total_logical_reads * 1.0 / qs.execution_count AS DECIMAL(18,0)) AS [Ort Logical Read],
        qs.total_physical_reads                                         AS [Toplam Physical Read],
        qs.total_logical_writes                                         AS [Toplam Logical Write],
        DB_NAME(st.dbid)                                                AS [Veritabani],
        OBJECT_NAME(st.objectid, st.dbid)                               AS [Nesne],
        SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
            ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS [Sorgu],
        qp.query_plan                                                   AS [Sorgu Plani]
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    ORDER BY qs.total_worker_time DESC;
END TRY BEGIN CATCH PRINT '  [!] CPU sorgu analizi alinamadi.'; END CATCH;

-- I/O'yu en çok tüketen sorgular
BEGIN TRY
    SELECT TOP 20
        CAST(qs.total_logical_reads * 1.0 / qs.execution_count AS DECIMAL(18,0))  AS [Ort Logical Read],
        CAST(qs.total_physical_reads * 1.0 / qs.execution_count AS DECIMAL(18,0)) AS [Ort Physical Read],
        qs.total_logical_reads                                                     AS [Toplam Logical Read],
        qs.total_physical_reads                                                    AS [Toplam Physical Read],
        qs.execution_count                                                         AS [Calisma Sayisi],
        CAST(qs.total_worker_time * 1.0 / qs.execution_count / 1000.0 AS DECIMAL(18,2)) AS [Ort CPU ms],
        DB_NAME(st.dbid)                                                           AS [Veritabani],
        OBJECT_NAME(st.objectid, st.dbid)                                          AS [Nesne],
        SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
            ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1)  AS [Sorgu]
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    ORDER BY qs.total_logical_reads DESC;
END TRY BEGIN CATCH PRINT '  [!] IO sorgu analizi alinamadi.'; END CATCH;

-- En çok çalışan sorgular
BEGIN TRY
    SELECT TOP 20
        qs.execution_count                                                          AS [Calisma Sayisi],
        CAST(qs.total_worker_time * 1.0 / qs.execution_count / 1000.0 AS DECIMAL(18,2)) AS [Ort CPU ms],
        CAST(qs.total_elapsed_time * 1.0 / qs.execution_count / 1000.0 AS DECIMAL(18,2)) AS [Ort Sure ms],
        CAST(qs.total_logical_reads * 1.0 / qs.execution_count AS DECIMAL(18,0))   AS [Ort Logical Read],
        qs.creation_time                                                            AS [Plan Olusturma],
        DB_NAME(st.dbid)                                                            AS [Veritabani],
        OBJECT_NAME(st.objectid, st.dbid)                                           AS [Nesne],
        SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
            ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1)   AS [Sorgu]
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    ORDER BY qs.execution_count DESC;
END TRY BEGIN CATCH PRINT '  [!] Sik calisma sorgu analizi alinamadi.'; END CATCH;

-- Şu an çalışan aktif sorgular
BEGIN TRY
    SELECT
        r.session_id                        AS [Session],
        r.status                            AS [Durum],
        r.blocking_session_id               AS [Bloklayan Session],
        r.wait_type                         AS [Wait Tipi],
        r.wait_time                         AS [Bekleme ms],
        r.cpu_time                          AS [CPU ms],
        r.logical_reads                     AS [Logical Read],
        r.reads                             AS [Physical Read],
        r.writes                            AS [Write],
        r.total_elapsed_time / 1000         AS [Gecen Sure sn],
        DB_NAME(r.database_id)              AS [Veritabani],
        s.login_name                        AS [Kullanici],
        s.host_name                         AS [Host],
        s.program_name                      AS [Program],
        SUBSTRING(st.text, (r.statement_start_offset/2)+1,
            ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
              ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS [Aktif Sorgu]
    FROM sys.dm_exec_requests r
    JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
    WHERE r.session_id <> @@SPID
      AND s.is_user_process = 1
    ORDER BY r.total_elapsed_time DESC;
END TRY BEGIN CATCH PRINT '  [!] Aktif sorgu listesi alinamadi.'; END CATCH;

-- ============================================================
-- BÖLÜM 13: INDEX ANALİZİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 13: Index Analizi';

-- Eksik index önerileri (SQL'in kendi önerileri)
BEGIN TRY
    SELECT TOP 20
        DB_NAME(mid.database_id)            AS [Veritabani],
        OBJECT_NAME(mid.object_id, mid.database_id) AS [Tablo],
        migs.avg_user_impact                AS [Tahmini Kazanim Yuzde],
        migs.user_seeks                     AS [Seek Sayisi],
        migs.user_scans                     AS [Scan Sayisi],
        migs.avg_total_user_cost            AS [Ort Sorgu Maliyeti],
        CAST(migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS BIGINT) AS [Oncelik Skoru],
        mid.equality_columns                AS [Esitlik Kolonlari],
        mid.inequality_columns              AS [Esitsizlik Kolonlari],
        mid.included_columns                AS [Include Kolonlari],
        'CREATE INDEX [IX_' + OBJECT_NAME(mid.object_id, mid.database_id)
            + '_Missing_' + CAST(mid.index_handle AS NVARCHAR) + '] ON '
            + mid.statement
            + ' (' + ISNULL(mid.equality_columns,'')
            + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
            + ISNULL(mid.inequality_columns,'') + ')'
            + ISNULL(' INCLUDE (' + mid.included_columns + ')', '')  AS [Onerilen CREATE INDEX]
    FROM sys.dm_db_missing_index_details mid
    JOIN sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
    JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
    ORDER BY CAST(migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS BIGINT) DESC;
END TRY BEGIN CATCH PRINT '  [!] Eksik index analizi alinamadi.'; END CATCH;

-- Kullanılmayan indexler
BEGIN TRY
    SELECT
        DB_NAME()                           AS [Veritabani],
        OBJECT_NAME(i.object_id)            AS [Tablo],
        i.name                              AS [Index Adi],
        i.type_desc                         AS [Index Tipi],
        ius.user_seeks                      AS [Seek],
        ius.user_scans                      AS [Scan],
        ius.user_lookups                    AS [Lookup],
        ius.user_updates                    AS [Guncelleme],
        ius.last_user_seek                  AS [Son Seek],
        ius.last_user_scan                  AS [Son Scan],
        ps.reserved_page_count * 8 / 1024  AS [Boyut MB],
        'DROP INDEX [' + i.name + '] ON [' + OBJECT_NAME(i.object_id) + '];' AS [Drop Komutu]
    FROM sys.indexes i
    JOIN sys.dm_db_index_usage_stats ius
        ON i.object_id = ius.object_id AND i.index_id = ius.index_id
        AND ius.database_id = DB_ID()
    JOIN sys.dm_db_partition_stats ps
        ON i.object_id = ps.object_id AND i.index_id = ps.index_id
    WHERE i.type_desc <> 'HEAP'
      AND i.is_primary_key = 0
      AND i.is_unique_constraint = 0
      AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
      AND (ius.user_seeks + ius.user_scans + ius.user_lookups) = 0
      AND ius.user_updates > 0
    ORDER BY ius.user_updates DESC;
END TRY BEGIN CATCH PRINT '  [!] Kullanilmayan index analizi alinamadi.'; END CATCH;

-- Duplicate (tekrarlayan) indexler
BEGIN TRY
    SELECT
        OBJECT_NAME(i1.object_id)           AS [Tablo],
        i1.name                             AS [Index 1],
        i2.name                             AS [Index 2],
        i1.type_desc                        AS [Tip],
        STUFF((
            SELECT ', ' + c.name
            FROM sys.index_columns ic
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i1.object_id AND ic.index_id = i1.index_id AND ic.is_included_column = 0
            ORDER BY ic.key_ordinal
            FOR XML PATH('')
        ), 1, 2, '')                        AS [Index1 Kolonlar],
        STUFF((
            SELECT ', ' + c.name
            FROM sys.index_columns ic
            JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
            WHERE ic.object_id = i2.object_id AND ic.index_id = i2.index_id AND ic.is_included_column = 0
            ORDER BY ic.key_ordinal
            FOR XML PATH('')
        ), 1, 2, '')                        AS [Index2 Kolonlar]
    FROM sys.indexes i1
    JOIN sys.indexes i2
        ON i1.object_id = i2.object_id
        AND i1.index_id < i2.index_id
        AND i1.type_desc = i2.type_desc
    WHERE OBJECTPROPERTY(i1.object_id, 'IsUserTable') = 1
      AND i1.type_desc <> 'HEAP'
      AND (
          SELECT STRING_AGG(CAST(ic.column_id AS NVARCHAR), ',') WITHIN GROUP (ORDER BY ic.key_ordinal)
          FROM sys.index_columns ic WHERE ic.object_id = i1.object_id AND ic.index_id = i1.index_id AND ic.is_included_column = 0
      ) = (
          SELECT STRING_AGG(CAST(ic.column_id AS NVARCHAR), ',') WITHIN GROUP (ORDER BY ic.key_ordinal)
          FROM sys.index_columns ic WHERE ic.object_id = i2.object_id AND ic.index_id = i2.index_id AND ic.is_included_column = 0
      )
    ORDER BY OBJECT_NAME(i1.object_id);
END TRY BEGIN CATCH PRINT '  [!] Duplicate index analizi alinamadi (STRING_AGG eski versiyonda yok).'; END CATCH;

-- Index fragmantasyon (yüksek fragmanlı indexler)
BEGIN TRY
    SELECT
        DB_NAME()                           AS [Veritabani],
        OBJECT_NAME(ips.object_id)          AS [Tablo],
        i.name                              AS [Index],
        ips.index_type_desc                 AS [Tip],
        CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5,1)) AS [Fragmentasyon Yuzde],
        ips.page_count                      AS [Sayfa Sayisi],
        CASE
            WHEN ips.avg_fragmentation_in_percent < 10  THEN 'Normal - islem gerekmez'
            WHEN ips.avg_fragmentation_in_percent < 30  THEN 'REORGANIZE onerilir'
            ELSE 'REBUILD onerilir'
        END                                 AS [Oneri],
        CASE
            WHEN ips.avg_fragmentation_in_percent >= 30
            THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(ips.object_id) + '] REBUILD WITH (ONLINE=ON);'
            ELSE 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(ips.object_id) + '] REORGANIZE;'
        END                                 AS [Komut]
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
    JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
    WHERE ips.avg_fragmentation_in_percent > 10
      AND ips.page_count > 100
      AND i.name IS NOT NULL
    ORDER BY ips.avg_fragmentation_in_percent DESC;
END TRY BEGIN CATCH PRINT '  [!] Index fragmentasyon analizi alinamadi.'; END CATCH;

BEGIN TRY
    INSERT INTO #AnalysisResults
    SELECT TOP 1
        'Index', 'Eksik Index Uyarisi',
        CAST(COUNT(*) AS NVARCHAR) + ' eksik index onerisi mevcut',
        'Oncelik skoruna gore en yuksek kazanimli indexleri olusturun',
        'sys.dm_db_missing_index_details sonuclarina bakin. Yuksek avg_user_impact + seek sayisi olanlardan baslayin.',
        CASE WHEN COUNT(*) > 10 THEN 'YUKSEK' WHEN COUNT(*) > 0 THEN 'ORTA' ELSE 'BILGI' END
    FROM sys.dm_db_missing_index_details
    WHERE database_id = DB_ID();
END TRY BEGIN CATCH END CATCH;

BEGIN TRY
    INSERT INTO #AnalysisResults
    SELECT TOP 1
        'Index', 'Yuksek Fragmentasyon',
        CAST(COUNT(*) AS NVARCHAR) + ' index %30+ fragmentasyonda',
        'REBUILD veya REORGANIZE yapilmali',
        'Yuksek fragmentasyon ESXi''de ekstra I/O gerektirir. Maintenance window''da index bakimi planlayin.',
        CASE WHEN COUNT(*) > 5 THEN 'YUKSEK' WHEN COUNT(*) > 0 THEN 'ORTA' ELSE 'BILGI' END
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
    WHERE avg_fragmentation_in_percent > 30 AND page_count > 100;
END TRY BEGIN CATCH END CATCH;

-- ============================================================
-- BÖLÜM 14: BLOCKING & DEADLOCK ANALİZİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 14: Blocking & Deadlock Analizi';

-- Şu an blocking yapan sessionlar
BEGIN TRY
    SELECT
        blocking.session_id                 AS [Bloklayan Session],
        blocked.session_id                  AS [Bloklanan Session],
        blocked.wait_type                   AS [Wait Tipi],
        blocked.wait_time                   AS [Bekleme ms],
        blocked.total_elapsed_time / 1000   AS [Gecen Sure sn],
        DB_NAME(blocked.database_id)        AS [Veritabani],
        s_blocking.login_name               AS [Bloklayan Kullanici],
        s_blocked.login_name                AS [Bloklanan Kullanici],
        s_blocking.program_name             AS [Bloklayan Program],
        s_blocked.program_name              AS [Bloklanan Program],
        SUBSTRING(st_blocking.text, 1, 500) AS [Bloklayan Sorgu],
        SUBSTRING(st_blocked.text,  1, 500) AS [Bloklanan Sorgu]
    FROM sys.dm_exec_requests blocked
    JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
    JOIN sys.dm_exec_sessions s_blocking ON blocking.session_id = s_blocking.session_id
    JOIN sys.dm_exec_sessions s_blocked  ON blocked.session_id  = s_blocked.session_id
    CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) st_blocking
    CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)  st_blocked
    WHERE blocked.blocking_session_id > 0;
END TRY BEGIN CATCH PRINT '  [!] Blocking analizi alinamadi.'; END CATCH;

-- Blocking özeti #AnalysisResults'a
BEGIN TRY
    DECLARE @blocking_count INT = 0;
    SELECT @blocking_count = COUNT(DISTINCT blocking_session_id)
    FROM sys.dm_exec_requests
    WHERE blocking_session_id > 0;

    INSERT INTO #AnalysisResults VALUES (
        'Blocking', 'Anlık Blocking Durumu',
        CAST(@blocking_count AS NVARCHAR) + ' bloklayan session',
        CASE WHEN @blocking_count > 0 THEN 'Bloklayan session incelenmeli!' ELSE 'Blocking yok' END,
        'Blocking ESXi''de CPU ve lock wait birikimini arttirir. sp_who2 veya Activity Monitor ile detay inceleyin.',
        CASE WHEN @blocking_count > 3 THEN 'KRITIK' WHEN @blocking_count > 0 THEN 'YUKSEK' ELSE 'BILGI' END
    );
END TRY BEGIN CATCH PRINT '  [!] Blocking ozet eklenemedi.'; END CATCH;

-- Deadlock geçmişi (System_health extended event'ten)
BEGIN TRY
    SELECT TOP 10
        xdr.value('@timestamp', 'datetime2')    AS [Zaman],
        xdr.query('.')                          AS [Deadlock XML]
    FROM (
        SELECT CAST(target_data AS XML) AS target_data
        FROM sys.dm_xe_session_targets t
        JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
        WHERE s.name = 'system_health'
          AND t.target_name = 'ring_buffer'
    ) data
    CROSS APPLY target_data.nodes('//RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xdr)
    ORDER BY xdr.value('@timestamp', 'datetime2') DESC;
END TRY BEGIN CATCH PRINT '  [!] Deadlock gecmisi alinamadi (normal - system_health olmayabilir).'; END CATCH;

-- Uzun süren transactionlar
BEGIN TRY
    SELECT
        s.session_id,
        s.login_name                        AS [Kullanici],
        s.host_name                         AS [Host],
        s.program_name                      AS [Program],
        DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS [Transaction Suresi Dakika],
        at.transaction_begin_time           AS [Baslangic],
        at.transaction_type                 AS [Transaction Tipi],
        at.transaction_state                AS [Durum],
        DB_NAME(dt.database_id)             AS [Veritabani],
        SUBSTRING(st.text, 1, 500)          AS [Son Sorgu]
    FROM sys.dm_tran_active_transactions at
    JOIN sys.dm_tran_session_transactions st2 ON at.transaction_id = st2.transaction_id
    JOIN sys.dm_exec_sessions s ON st2.session_id = s.session_id
    JOIN sys.dm_tran_database_transactions dt ON at.transaction_id = dt.transaction_id
    CROSS APPLY sys.dm_exec_sql_text(
        (SELECT TOP 1 sql_handle FROM sys.dm_exec_requests WHERE session_id = s.session_id)
    ) st
    WHERE DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) > 5
    ORDER BY at.transaction_begin_time;
END TRY BEGIN CATCH PRINT '  [!] Uzun transaction listesi alinamadi.'; END CATCH;

-- ============================================================
-- BÖLÜM 15: STATİSTİK GÜNCELLİK ANALİZİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 15: Istatistik Guncellik Analizi';

BEGIN TRY
    SELECT
        DB_NAME()                           AS [Veritabani],
        OBJECT_NAME(s.object_id)            AS [Tablo],
        s.name                              AS [Istatistik],
        sp.last_updated                     AS [Son Guncelleme],
        sp.rows                             AS [Satir Sayisi],
        sp.rows_sampled                     AS [Orneklenen Satir],
        CAST(sp.rows_sampled * 100.0 / NULLIF(sp.rows, 0) AS DECIMAL(5,1)) AS [Ornekleme Yuzde],
        sp.modification_counter             AS [Degisiklik Sayisi],
        DATEDIFF(DAY, sp.last_updated, GETDATE()) AS [Kac Gun Once],
        CASE
            WHEN sp.last_updated IS NULL                                         THEN 'Hic guncellenmemis!'
            WHEN DATEDIFF(DAY, sp.last_updated, GETDATE()) > 30                  THEN '30+ gun eski'
            WHEN sp.modification_counter > sp.rows * 0.2                        THEN 'Cok fazla degisiklik - guncelle'
            ELSE 'Guncel'
        END                                 AS [Durum],
        'UPDATE STATISTICS [' + OBJECT_NAME(s.object_id) + '] [' + s.name + '] WITH FULLSCAN;' AS [Guncelleme Komutu]
    FROM sys.stats s
    CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
    WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
      AND (
          sp.last_updated IS NULL
          OR DATEDIFF(DAY, sp.last_updated, GETDATE()) > 7
          OR sp.modification_counter > sp.rows * 0.1
      )
    ORDER BY DATEDIFF(DAY, sp.last_updated, GETDATE()) DESC;
END TRY BEGIN CATCH PRINT '  [!] Istatistik analizi alinamadi.'; END CATCH;

BEGIN TRY
    DECLARE @stale_stats INT = 0;
    SELECT @stale_stats = COUNT(*)
    FROM sys.stats s
    CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
    WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
      AND (sp.last_updated IS NULL OR DATEDIFF(DAY, sp.last_updated, GETDATE()) > 7);

    INSERT INTO #AnalysisResults VALUES (
        'Istatistik', 'Eski Istatistik Sayisi',
        CAST(@stale_stats AS NVARCHAR) + ' istatistik 7+ gun guncellenmemis',
        'Duzenli AUTO_UPDATE_STATISTICS veya manuel UPDATE STATISTICS yapilmali',
        'Eski istatistikler yanlis sorgu plani uretir, fazla I/O ve CPU tuketir. ESXi ortaminda etki daha buyuk.',
        CASE WHEN @stale_stats > 20 THEN 'YUKSEK' WHEN @stale_stats > 0 THEN 'ORTA' ELSE 'BILGI' END
    );
END TRY BEGIN CATCH PRINT '  [!] Istatistik ozet eklenemedi.'; END CATCH;

-- Auto update statistics kontrolü
BEGIN TRY
    INSERT INTO #AnalysisResults
    SELECT
        'Istatistik',
        'Auto Update Statistics: ' + name,
        CASE is_auto_update_stats_on WHEN 1 THEN 'Aktif' ELSE 'PASIF - Tehlikeli!' END,
        'Aktif olmali',
        'Auto update statistics kapali olan veritabanlarinda sorgu planlari bozulabilir. ALTER DATABASE [' + name + '] SET AUTO_UPDATE_STATISTICS ON;',
        CASE WHEN is_auto_update_stats_on = 0 THEN 'YUKSEK' ELSE 'BILGI' END
    FROM sys.databases
    WHERE is_auto_update_stats_on = 0
      AND state_desc = 'ONLINE'
      AND database_id > 4;
END TRY BEGIN CATCH PRINT '  [!] Auto update stats kontrolu yapilamadi.'; END CATCH;

-- ============================================================
-- BÖLÜM 16: SQL AGENT JOB ANALİZİ
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 16: SQL Agent Job Analizi';

-- Son başarısız joblar
BEGIN TRY
    SELECT TOP 20
        j.name                              AS [Job Adi],
        jh.step_name                        AS [Adim],
        CONVERT(DATETIME,
            STUFF(STUFF(CAST(jh.run_date AS NVARCHAR), 7, 0, '-'), 5, 0, '-')
            + ' ' +
            STUFF(STUFF(RIGHT('000000' + CAST(jh.run_time AS NVARCHAR), 6), 5, 0, ':'), 3, 0, ':')
        )                                   AS [Calisma Zamani],
        jh.run_duration                     AS [Sure (HHMMSS)],
        CASE jh.run_status
            WHEN 0 THEN 'BASARISIZ'
            WHEN 1 THEN 'Basarili'
            WHEN 2 THEN 'Yeniden Deneme'
            WHEN 3 THEN 'Iptal Edildi'
            WHEN 4 THEN 'Devam Ediyor'
        END                                 AS [Sonuc],
        jh.message                          AS [Mesaj]
    FROM msdb.dbo.sysjobhistory jh
    JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
    WHERE jh.run_status = 0
      AND jh.step_id <> 0
    ORDER BY jh.run_date DESC, jh.run_time DESC;
END TRY BEGIN CATCH PRINT '  [!] Job gecmisi alinamadi (msdb erisimi yok olabilir).'; END CATCH;

-- Uzun süren joblar (ortalamaya göre)
BEGIN TRY
    SELECT
        j.name                              AS [Job Adi],
        COUNT(*)                            AS [Calisma Sayisi],
        AVG(jh.run_duration)                AS [Ort Sure HHMMSS],
        MAX(jh.run_duration)                AS [Max Sure HHMMSS],
        SUM(CASE WHEN jh.run_status = 0 THEN 1 ELSE 0 END) AS [Basarisiz Sayisi],
        CAST(SUM(CASE WHEN jh.run_status = 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,1)) AS [Basarisizlik Yuzde],
        j.enabled                           AS [Aktif],
        CONVERT(DATETIME,
            STUFF(STUFF(CAST(MAX(jh.run_date) AS NVARCHAR), 7, 0, '-'), 5, 0, '-')
        )                                   AS [Son Calisma Tarihi]
    FROM msdb.dbo.sysjobhistory jh
    JOIN msdb.dbo.sysjobs j ON jh.job_id = j.job_id
    WHERE jh.step_id = 0
    GROUP BY j.name, j.enabled
    ORDER BY AVG(jh.run_duration) DESC;
END TRY BEGIN CATCH PRINT '  [!] Job istatistikleri alinamadi.'; END CATCH;

-- Devre dışı bırakılmış joblar
BEGIN TRY
    INSERT INTO #AnalysisResults
    SELECT
        'SQL Agent',
        'Devre Disi Job: ' + name,
        'Disabled',
        'Maintenance joblarinin aktif olmasi gerekiyor',
        'Bu job devre disi. Backup, index bakimi veya statistics update jobu ise kontrol edin.',
        'ORTA'
    FROM msdb.dbo.sysjobs
    WHERE enabled = 0;
END TRY BEGIN CATCH PRINT '  [!] Disabled job kontrolu yapilamadi.'; END CATCH;

-- Son 24 saatte başarısız olan joblar #AnalysisResults'a
BEGIN TRY
    DECLARE @failed_jobs INT = 0;
    SELECT @failed_jobs = COUNT(DISTINCT jh.job_id)
    FROM msdb.dbo.sysjobhistory jh
    WHERE jh.run_status = 0
      AND jh.step_id <> 0
      AND CONVERT(DATETIME,
            STUFF(STUFF(CAST(jh.run_date AS NVARCHAR), 7, 0, '-'), 5, 0, '-')
            + ' ' +
            STUFF(STUFF(RIGHT('000000' + CAST(jh.run_time AS NVARCHAR), 6), 5, 0, ':'), 3, 0, ':')
          ) >= DATEADD(HOUR, -24, GETDATE());

    INSERT INTO #AnalysisResults VALUES (
        'SQL Agent', 'Son 24 Saat Basarisiz Job',
        CAST(@failed_jobs AS NVARCHAR) + ' farkli job basarisiz oldu',
        'Basarisiz joblari inceleyin',
        'Backup veya maintenance joblarinin basarisizligi veri kaybi riski olusturur.',
        CASE WHEN @failed_jobs > 3 THEN 'KRITIK' WHEN @failed_jobs > 0 THEN 'YUKSEK' ELSE 'BILGI' END
    );
END TRY BEGIN CATCH PRINT '  [!] Basarisiz job ozeti eklenemedi.'; END CATCH;

-- ============================================================
-- BÖLÜM 17: ESXi'YE ÖZEL KONTROLLER
-- ============================================================
PRINT '';
PRINT '>> BÖLÜM 17: ESXi Ozgul Performans Kontrolleri';

INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','Windows Power Plan','Manuel kontrol','HIGH PERFORMANCE olmali','Balanced power plan vCPU frekansini dusurebilir. powercfg /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c','KRITIK');
INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','VMware Tools','Manuel kontrol (vSphere''den)','Guncel ve calisir olmali','VMware Tools olmadan balloon driver, CPU steal ve disk sync duzgun calishmaz.','KRITIK');
INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','CPU Hot Add','Manuel kontrol (VMX)','SQL VM icin KAPALI olmali','CPU Hot Add aktifken ESXi vNUMA''yi devre disi birakir. VM Settings > CPU > CPU Hot Plug = Disabled.','YUKSEK');
INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','Memory Hot Add','Manuel kontrol','SQL VM icin KAPALI olmali','Memory Hot Add aktifken NUMA topolojisi bozulur. VM Settings > Memory > Memory Hot Plug = Disabled.','YUKSEK');
INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','VMDK Provisioning','Manuel kontrol (vSphere''den)','Eager Zeroed Thick kullan','Thin ve Lazy Zeroed Thick ilk yazmalarda I/O spike yaratir. Eager Zeroed Thick ile bloklar onceden sifirlanir.','YUKSEK');
INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','SCSI Controller','Manuel kontrol (vSphere''den)','VMware Paravirtual (PVSCSI) olmali','LSI Logic emulasyon katmani ekler. PVSCSI ile dogrudan vStorage API kullanilir, yuksek IOPS''ta ciddi fark var.','YUKSEK');
INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','Balloon Driver / Memory Overcommit','Host memory baskisi varsa aktif olabilir','Host memory overcommit OLMAMALI','Balloon aktifse SQL buffer pool kuculdur, PLE duser. vSphere: VM > Memory > Reserve all guest memory.','KRITIK');
INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','Aktif Snapshot','Manuel kontrol (vSphere''den)','Uretim SQL VM''inde snapshot OLMAMALI','Aktif snapshot varsa tum write I/O delta VMDK''ya gider, performans ciddi dusulebilir.','KRITIK');
INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','VMware Tools Zaman Sync','Manuel kontrol','tools.syncTime = FALSE olmali','VMware Tools time sync ile Windows Time cakisir. VMX: tools.syncTime=FALSE, time.synchronize.continue=FALSE','ORTA');
INSERT INTO #AnalysisResults VALUES ('ESXi Ozel','Storage IO Control (SIOC)','Manuel kontrol (vSphere''den)','SQL datastoreda SIOC aktif ve SQL VM yuksek oncelikli olmali','SIOC datastoru paylasan VM''ler arasinda I/O tahsis eder. SQL VM Disk Shares = High/Custom.','ORTA');

-- ============================================================
-- BÖLÜM 18: ÖZET RAPOR
-- ============================================================
PRINT '';
PRINT '================================================================================';
PRINT '  OZET RAPOR & AKSIYON LISTESI';
PRINT '================================================================================';

SELECT '[ KRITIK ]' AS [Oncelik], Kategori, Parametre, MevcutDeger AS [Mevcut], Onerilen, Aciklama
FROM #AnalysisResults WHERE Oncelik = 'KRITIK' ORDER BY Kategori, Parametre;

SELECT '[ YUKSEK ]' AS [Oncelik], Kategori, Parametre, MevcutDeger AS [Mevcut], Onerilen, Aciklama
FROM #AnalysisResults WHERE Oncelik = 'YUKSEK' ORDER BY Kategori, Parametre;

SELECT '[ ORTA ]'   AS [Oncelik], Kategori, Parametre, MevcutDeger AS [Mevcut], Onerilen, Aciklama
FROM #AnalysisResults WHERE Oncelik = 'ORTA' ORDER BY Kategori, Parametre;

SELECT '[ BILGI ]'  AS [Oncelik], Kategori, Parametre, MevcutDeger AS [Mevcut], Onerilen, Aciklama
FROM #AnalysisResults WHERE Oncelik = 'BILGI' ORDER BY Kategori, Parametre;

SELECT
    COUNT(CASE WHEN Oncelik = 'KRITIK' THEN 1 END) AS [Kritik],
    COUNT(CASE WHEN Oncelik = 'YUKSEK' THEN 1 END) AS [Yuksek],
    COUNT(CASE WHEN Oncelik = 'ORTA'   THEN 1 END) AS [Orta],
    COUNT(CASE WHEN Oncelik = 'BILGI'  THEN 1 END) AS [Bilgi],
    COUNT(*)                                       AS [Toplam]
FROM #AnalysisResults;

-- ============================================================
-- ESXi VMX & SQL REFERANS
-- ============================================================
PRINT '';
PRINT '--- ESXi VMX Dosyasi Onerilen Parametreler ---';
PRINT 'numa.vcpu.preferHT = "TRUE"';
PRINT 'sched.mem.pin = "TRUE"';
PRINT 'MemTrimRate = "0"';
PRINT 'MemAllowAutoScaleDown = "FALSE"';
PRINT 'tools.syncTime = "FALSE"';
PRINT 'time.synchronize.continue = "FALSE"';
PRINT 'time.synchronize.restore = "FALSE"';
PRINT 'disk.EnableUUID = "TRUE"';
PRINT 'ethernet0.virtualDev = "vmxnet3"';
PRINT 'scsi0:0.virtualDev = "pvscsi"';
PRINT 'sched.cpu.latencySensitivity = "high"';
PRINT '';
PRINT '--- Hizli SQL Konfigurasyon ---';
PRINT 'EXEC sp_configure ''show advanced options'', 1; RECONFIGURE;';
PRINT 'EXEC sp_configure ''optimize for ad hoc workloads'', 1; RECONFIGURE;';
PRINT 'EXEC sp_configure ''backup compression default'', 1; RECONFIGURE;';
PRINT 'EXEC sp_configure ''cost threshold for parallelism'', 50; RECONFIGURE;';
PRINT 'EXEC sp_configure ''network packet size'', 8192; RECONFIGURE;';
PRINT '-- MAXDOP ve Max Server Memory analiz sonucuna gore ayarlayin!';

DROP TABLE #AnalysisResults;

PRINT '';
PRINT '================================================================================';
PRINT '  ANALIZ TAMAMLANDI: ' + CONVERT(NVARCHAR(30), GETDATE(), 120);
PRINT '  Her degisikligi once TEST ortaminda uygulayin!';
PRINT '================================================================================';