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

timmy ha revisionato questo gist 8 months ago. Vai alla revisione

Nessuna modifica

timmy ha revisionato questo gist 9 months ago. Vai alla revisione

1 file changed, 36 insertions

check_sql_server_connections.sql(file creato)

		@@ -0,0 +1,36 @@
1	+	-- 查詢當前 SQL Server 的總連線數以及不同 IP 來源的連線數
2	+	SELECT
3	+	COUNT(*) AS TotalConnections, -- 計算總連線數
4	+	COUNT(DISTINCT client_net_address) AS UniqueIPs -- 計算不同來源 IP 的數量
5	+	FROM sys.dm_exec_connections;
6	+
7	+	-- 查詢所有當前連線的詳細資訊
8	+	SELECT
9	+	session_id, -- 連線的會話 ID
10	+	connect_time, -- 連線建立的時間
11	+	client_net_address, -- 連線的客戶端 IP 地址
12	+	local_net_address, -- 伺服器端的本地 IP 地址
13	+	local_tcp_port, -- 伺服器端的本地 TCP 連接埠
14	+	protocol_type, -- 連線使用的通訊協定（如 TCP、Named Pipes）
15	+	encrypt_option -- 連線的加密選項（如 TRUE/FALSE）
16	+	FROM sys.dm_exec_connections
17	+	ORDER BY connect_time DESC; -- 按連線時間排序，最新的在最上方
18	+
19	+	-- 計算每個客戶端 IP 的連線數量
20	+	SELECT
21	+	client_net_address, -- 客戶端 IP 地址
22	+	COUNT(*) AS ConnectionCount -- 來自該 IP 的連線數量
23	+	FROM sys.dm_exec_connections
24	+	GROUP BY client_net_address
25	+	ORDER BY ConnectionCount DESC; -- 按連線數量排序，最多的在最上方
26	+
27	+	-- 查詢所有處於 "sleeping" 狀態的會話，並統計記憶體使用情況
28	+	SELECT
29	+	program_name, -- 連線所使用的程式名稱（如 SQL Server Management Studio、應用程式名稱）
30	+	COUNT(*) AS SessionCount, -- 該程式產生的 "sleeping" 會話數量
31	+	SUM(memory_usage) * 8 AS TotalMemory_KB, -- 計算總記憶體使用量（KB）
32	+	SUM(memory_usage) * 8 / 1024 AS TotalMemory_MB -- 計算總記憶體使用量（MB）
33	+	FROM sys.dm_exec_sessions
34	+	WHERE status = 'sleeping' -- 過濾僅查詢 "sleeping" 狀態的會話
35	+	GROUP BY program_name
36	+	ORDER BY TotalMemory_KB DESC; -- 按記憶體使用量排序，使用最多的在最上方

timmy ha revisionato questo gist 9 months ago. Vai alla revisione

1 file changed, 134 insertions

sql_server_health_check_queries.sql(file creato)

		@@ -0,0 +1,134 @@
1	+	-- ==============================================
2	+	-- 📌 1. 檢查 SQL Server 版本與執行時間
3	+	-- ==============================================
4	+	-- 這可以讓你知道目前 SQL Server 的版本、補丁等級，以及 SQL Server 啟動的時間。
5	+	SELECT
6	+	SERVERPROPERTY('ProductVersion') AS SQLServerVersion, -- SQL Server 版本
7	+	SERVERPROPERTY('ProductLevel') AS SQLServerLevel, -- SQL Server 補丁等級（SP、CU）
8	+	SERVERPROPERTY('Edition') AS SQLEdition, -- SQL Server 版本類型（Standard, Enterprise, Express）
9	+	sqlserver_start_time AS SQLServerStartTime -- 伺服器上次重啟時間
10	+	FROM sys.dm_os_sys_info;
11	+
12	+	-- ==============================================
13	+	-- 📌 2. 檢查 SQL Server 記憶體使用狀況
14	+	-- ==============================================
15	+	-- 這段查詢可以幫助你了解 SQL Server 當前使用的記憶體狀況，並檢查是否有記憶體壓力。
16	+	SELECT
17	+	(physical_memory_in_use_kb / 1024 / 1024) AS SQLServer_Memory_Used_GB, -- SQL Server 目前使用的記憶體
18	+	(available_commit_limit_kb / 1024 / 1024) AS Available_Commit_Limit_GB, -- SQL Server 允許使用的最大記憶體
19	+	(virtual_address_space_available_kb / 1024 / 1024) AS Available_Virtual_Memory_GB, -- 剩餘的虛擬記憶體
20	+	memory_utilization_percentage AS Memory_Utilization_Percentage, -- SQL Server 記憶體使用率（高於 90% 需注意）
21	+	process_physical_memory_low AS Is_Physical_Memory_Low, -- 是否發生物理記憶體不足（1 = 記憶體不足）
22	+	process_virtual_memory_low AS Is_Virtual_Memory_Low -- 是否發生虛擬記憶體不足（1 = 記憶體不足）
23	+	FROM sys.dm_os_process_memory;
24	+
25	+	-- ==============================================
26	+	-- 📌 3. 檢查 SQL Server 最大允許連線數與目前使用數
27	+	-- ==============================================
28	+	-- 這可以幫助你了解 SQL Server 目前的連線數量，以及是否接近上限。
29	+	SELECT
30	+	value AS MaxConnections -- SQL Server 允許的最大連線數
31	+	FROM sys.configurations
32	+	WHERE name = 'user connections';
33	+
34	+	SELECT
35	+	COUNT(*) AS ActiveConnections -- 目前使用中的連線數
36	+	FROM sys.dm_exec_sessions
37	+	WHERE is_user_process = 1;
38	+
39	+	-- ==============================================
40	+	-- 📌 4. 查找 SQL Server 最長時間的等待類型
41	+	-- ==============================================
42	+	-- 這可以讓你找出系統的瓶頸，例如 CPU 過載、I/O 過慢或記憶體不足。
43	+	SELECT TOP 10
44	+	wait_type,
45	+	waiting_tasks_count,
46	+	wait_time_ms / 1000 AS WaitTimeSec -- 以秒為單位顯示等待時間
47	+	FROM sys.dm_os_wait_stats
48	+	ORDER BY wait_time_ms DESC;
49	+
50	+	-- ==============================================
51	+	-- 📌 5. 查詢當前最耗資源的前 10 個查詢
52	+	-- ==============================================
53	+	-- 這能讓你找出哪些 SQL 查詢占用了最多的 CPU 或執行時間，以便進行優化。
54	+	SELECT TOP 10
55	+	qs.total_worker_time / 1000 AS CPU_Time_ms, -- 查詢總 CPU 時間
56	+	qs.total_elapsed_time / 1000 AS Total_Time_ms, -- 查詢總執行時間
57	+	qs.execution_count AS ExecutionCount, -- 該查詢執行次數
58	+	qs.total_logical_reads AS LogicalReads, -- 總記憶體讀取數
59	+	qs.total_physical_reads AS PhysicalReads, -- 總磁碟讀取數
60	+	qs.total_logical_writes AS LogicalWrites, -- 總磁碟寫入數
61	+	q.text AS QueryText -- SQL 查詢內容
62	+	FROM sys.dm_exec_query_stats qs
63	+	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) q
64	+	ORDER BY qs.total_worker_time DESC;
65	+
66	+	-- ==============================================
67	+	-- 📌 6. 檢查索引的使用情況（找出沒被用到的索引）
68	+	-- ==============================================
69	+	-- 這可以幫助你找到長期沒被使用的索引，以便考慮是否刪除來減少負擔。
70	+	SELECT
71	+	OBJECT_NAME(i.object_id) AS TableName, -- 資料表名稱
72	+	i.name AS IndexName, -- 索引名稱
73	+	i.index_id,
74	+	s.user_seeks, -- 索引被查詢優化器選擇使用的次數
75	+	s.user_scans, -- 索引被完整掃描的次數
76	+	s.user_lookups, -- 查詢透過索引查找資料的次數
77	+	s.user_updates -- 該索引有多少次被更新（更新次數高但讀取次數低可能是不必要的索引）
78	+	FROM sys.dm_db_index_usage_stats s
79	+	JOIN sys.indexes i
80	+	ON i.object_id = s.object_id
81	+	AND i.index_id = s.index_id
82	+	WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
83	+	ORDER BY s.user_updates DESC;
84	+
85	+	-- ==============================================
86	+	-- 📌 7. 找出索引過多的資料表
87	+	-- ==============================================
88	+	-- 太多索引會影響 INSERT / UPDATE / DELETE 的效能，這能幫助你找出索引過多的資料表。
89	+	SELECT
90	+	t.name AS TableName,
91	+	COUNT(i.index_id) AS IndexCount
92	+	FROM sys.indexes i
93	+	JOIN sys.tables t
94	+	ON i.object_id = t.object_id
95	+	WHERE i.type IN (1, 2) -- 只計算 Clustered 和 Non-Clustered 索引
96	+	GROUP BY t.name
97	+	HAVING COUNT(i.index_id) > 5 -- 假設超過 5 個索引的表值得檢查
98	+	ORDER BY COUNT(i.index_id) DESC;
99	+
100	+	-- ==============================================
101	+	-- 📌 8. 檢查 SQL Server 是否有交易鎖
102	+	-- ==============================================
103	+	-- 這可以幫助你找出哪個交易正在封鎖其他交易，避免死鎖。
104	+	SELECT
105	+	blocking_session_id AS BlockingSessionID, -- 鎖住其他交易的會話 ID
106	+	session_id AS BlockedSessionID, -- 被鎖住的交易會話 ID
107	+	wait_type,
108	+	wait_time AS WaitTime_MS -- 等待時間
109	+	FROM sys.dm_exec_requests
110	+	WHERE blocking_session_id <> 0
111	+	ORDER BY wait_time DESC;
112	+
113	+	-- ==============================================
114	+	-- 📌 9. 找出 I/O 負擔最重的資料表
115	+	-- ==============================================
116	+	-- 這可以幫助你了解哪些表的 I/O 操作最多，找出可能影響效能的表。
117	+	SELECT TOP 10
118	+	OBJECT_NAME(ps.object_id) AS TableName,
119	+	SUM(ps.leaf_insert_count + ps.leaf_delete_count + ps.leaf_update_count) AS TotalModifications
120	+	FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) ps
121	+	GROUP BY ps.object_id
122	+	ORDER BY TotalModifications DESC;
123	+
124	+	-- ==============================================
125	+	-- 📌 10. 檢查 SQL Server 記憶體快取命中率（Buffer Pool Hit Ratio）
126	+	-- ==============================================
127	+	-- 這可以幫助你檢查 SQL Server 的記憶體使用是否有效率。
128	+	SELECT
129	+	ROUND((1.0 - (a.cntr_value * 1.0 / b.cntr_value)) * 100, 2) AS BufferCacheHitRatio
130	+	FROM sys.dm_os_performance_counters a
131	+	JOIN sys.dm_os_performance_counters b
132	+	ON a.object_name = b.object_name
133	+	WHERE a.counter_name = 'Buffer cache hit ratio base'
134	+	AND b.counter_name = 'Buffer cache hit ratio';

Più nuovi Più vecchi