timmy / 🧠 SQL Server 監控懶人包：效能瓶頸、記憶體、連線數全面掌握

-- 查詢當前 SQL Server 的總連線數以及不同 IP 來源的連線數 SELECT COUNT(*) AS TotalConnections, -- 計算總連線數 COUNT(DISTINCT client_net_address) AS UniqueIPs -- 計算不同來源 IP 的數量 FROM sys.dm_exec_connections; -- 查詢所有當前連線的詳細資訊 SELECT session_id, -- 連線的會話 ID connect_time, -- 連線建立的時間 client_net_address, -- 連線的客戶端 IP 地址 local_net_address, -- 伺服器端的本地 IP 地址 local_tcp_port, -- 伺服器端的本地 TCP 連接埠 protocol_type, -- 連線使用的通訊協定（如 TCP、Named Pipes） encrypt_option -- 連線的加密選項（如 TRUE/FALSE） FROM sys.dm_exec_connections ORDER BY connect_time DESC; -- 按連線時間排序，最新的在最上方 -- 計算每個客戶端 IP 的連線數量 SELECT client_net_address, -- 客戶端 IP 地址 COUNT(*) AS ConnectionCount -- 來自該 IP 的連線數量 FROM sys.dm_exec_connections GROUP BY client_net_address ORDER BY ConnectionCount DESC; -- 按連線數量排序，最多的在最上方 -- 查詢所有處於 "sleeping" 狀態的會話，並統計記憶體使用情況 SELECT program_name, -- 連線所使用的程式名稱（如 SQL Server Management Studio、應用程式名稱） COUNT(*) AS SessionCount, -- 該程式產生的 "sleeping" 會話數量 SUM(memory_usage) * 8 AS TotalMemory_KB, -- 計算總記憶體使用量（KB） SUM(memory_usage) * 8 / 1024 AS TotalMemory_MB -- 計算總記憶體使用量（MB） FROM sys.dm_exec_sessions WHERE status = 'sleeping' -- 過濾僅查詢 "sleeping" 狀態的會話 GROUP BY program_name ORDER BY TotalMemory_KB DESC; -- 按記憶體使用量排序，使用最多的在最上方

-- ============================================== -- 📌 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';