-- ==============================================
-- 📌 1. 檢查 SQL Server 版本與執行時間
-- ==============================================
-- 這可以讓你知道目前 SQL Server 的版本、補丁等級，以及 SQL Server 啟動的時間。
SELECT 
    SERVERPROPERTY('ProductVersion') AS SQLServerVersion,  -- SQL Server 版本
    SERVERPROPERTY('ProductLevel') AS SQLServerLevel,      -- SQL Server 補丁等級（SP、CU）
    SERVERPROPERTY('Edition') AS SQLEdition,              -- SQL Server 版本類型（Standard, Enterprise, Express）
    sqlserver_start_time AS SQLServerStartTime            -- 伺服器上次重啟時間
FROM sys.dm_os_sys_info;

-- ==============================================
-- 📌 2. 檢查 SQL Server 記憶體使用狀況
-- ==============================================
-- 這段查詢可以幫助你了解 SQL Server 當前使用的記憶體狀況，並檢查是否有記憶體壓力。
SELECT 
    (physical_memory_in_use_kb / 1024 / 1024) AS SQLServer_Memory_Used_GB,  -- SQL Server 目前使用的記憶體
    (available_commit_limit_kb / 1024 / 1024) AS Available_Commit_Limit_GB, -- SQL Server 允許使用的最大記憶體
    (virtual_address_space_available_kb / 1024 / 1024) AS Available_Virtual_Memory_GB, -- 剩餘的虛擬記憶體
    memory_utilization_percentage AS Memory_Utilization_Percentage, -- SQL Server 記憶體使用率（高於 90% 需注意）
    process_physical_memory_low AS Is_Physical_Memory_Low,  -- 是否發生物理記憶體不足（1 = 記憶體不足）
    process_virtual_memory_low AS Is_Virtual_Memory_Low  -- 是否發生虛擬記憶體不足（1 = 記憶體不足）
FROM sys.dm_os_process_memory;

-- ==============================================
-- 📌 3. 檢查 SQL Server 最大允許連線數與目前使用數
-- ==============================================
-- 這可以幫助你了解 SQL Server 目前的連線數量，以及是否接近上限。
SELECT 
    value AS MaxConnections -- SQL Server 允許的最大連線數
FROM sys.configurations
WHERE name = 'user connections';

SELECT 
    COUNT(*) AS ActiveConnections -- 目前使用中的連線數
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

-- ==============================================
-- 📌 4. 查找 SQL Server 最長時間的等待類型
-- ==============================================
-- 這可以讓你找出系統的瓶頸，例如 CPU 過載、I/O 過慢或記憶體不足。
SELECT TOP 10 
    wait_type, 
    waiting_tasks_count, 
    wait_time_ms / 1000 AS WaitTimeSec -- 以秒為單位顯示等待時間
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

-- ==============================================
-- 📌 5. 查詢當前最耗資源的前 10 個查詢
-- ==============================================
-- 這能讓你找出哪些 SQL 查詢占用了最多的 CPU 或執行時間，以便進行優化。
SELECT TOP 10
    qs.total_worker_time / 1000 AS CPU_Time_ms,  -- 查詢總 CPU 時間
    qs.total_elapsed_time / 1000 AS Total_Time_ms, -- 查詢總執行時間
    qs.execution_count AS ExecutionCount, -- 該查詢執行次數
    qs.total_logical_reads AS LogicalReads, -- 總記憶體讀取數
    qs.total_physical_reads AS PhysicalReads, -- 總磁碟讀取數
    qs.total_logical_writes AS LogicalWrites, -- 總磁碟寫入數
    q.text AS QueryText -- SQL 查詢內容
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) q
ORDER BY qs.total_worker_time DESC;

-- ==============================================
-- 📌 6. 檢查索引的使用情況（找出沒被用到的索引）
-- ==============================================
-- 這可以幫助你找到長期沒被使用的索引，以便考慮是否刪除來減少負擔。
SELECT 
    OBJECT_NAME(i.object_id) AS TableName, -- 資料表名稱
    i.name AS IndexName, -- 索引名稱
    i.index_id, 
    s.user_seeks, -- 索引被查詢優化器選擇使用的次數
    s.user_scans, -- 索引被完整掃描的次數
    s.user_lookups, -- 查詢透過索引查找資料的次數
    s.user_updates -- 該索引有多少次被更新（更新次數高但讀取次數低可能是不必要的索引）
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i 
    ON i.object_id = s.object_id 
    AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY s.user_updates DESC;

-- ==============================================
-- 📌 7. 找出索引過多的資料表
-- ==============================================
-- 太多索引會影響 INSERT / UPDATE / DELETE 的效能，這能幫助你找出索引過多的資料表。
SELECT 
    t.name AS TableName, 
    COUNT(i.index_id) AS IndexCount
FROM sys.indexes i
JOIN sys.tables t 
    ON i.object_id = t.object_id
WHERE i.type IN (1, 2)  -- 只計算 Clustered 和 Non-Clustered 索引
GROUP BY t.name
HAVING COUNT(i.index_id) > 5  -- 假設超過 5 個索引的表值得檢查
ORDER BY COUNT(i.index_id) DESC;

-- ==============================================
-- 📌 8. 檢查 SQL Server 是否有交易鎖
-- ==============================================
-- 這可以幫助你找出哪個交易正在封鎖其他交易，避免死鎖。
SELECT 
    blocking_session_id AS BlockingSessionID, -- 鎖住其他交易的會話 ID
    session_id AS BlockedSessionID, -- 被鎖住的交易會話 ID
    wait_type, 
    wait_time AS WaitTime_MS -- 等待時間
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
ORDER BY wait_time DESC;

-- ==============================================
-- 📌 9. 找出 I/O 負擔最重的資料表
-- ==============================================
-- 這可以幫助你了解哪些表的 I/O 操作最多，找出可能影響效能的表。
SELECT TOP 10
    OBJECT_NAME(ps.object_id) AS TableName,
    SUM(ps.leaf_insert_count + ps.leaf_delete_count + ps.leaf_update_count) AS TotalModifications
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) ps
GROUP BY ps.object_id
ORDER BY TotalModifications DESC;

-- ==============================================
-- 📌 10. 檢查 SQL Server 記憶體快取命中率（Buffer Pool Hit Ratio）
-- ==============================================
-- 這可以幫助你檢查 SQL Server 的記憶體使用是否有效率。
SELECT 
    ROUND((1.0 - (a.cntr_value * 1.0 / b.cntr_value)) * 100, 2) AS BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN sys.dm_os_performance_counters b
    ON a.object_name = b.object_name
WHERE a.counter_name = 'Buffer cache hit ratio base'
    AND b.counter_name = 'Buffer cache hit ratio';