轉載自：https://www.cnblogs.com/vice/p/9163241.html
作業系統是Windows2008R2 ,資料庫是SQL2014 64位，
近階段服務器出現過幾次死機，管理員反饋機器記憶體使用率100%導致機器卡死，于是做了個監測服務器的軟體實時記錄CPU資料，幾日觀察得出資料如下：
SQL優化方法：
1、查看連接物件
1 USE master2 GO3 --如果要指定資料庫就把注釋去掉4 SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 --AND DB_NAME([dbid])='gposdb'
當前連接物件有67個其中‘WINAME’的主機名，‘jTDS’的行程名不屬于已知常用軟體，找到這臺主機并解決連接問題，在360流量防火墻中查看有哪個軟體連接了服務器IP，除之，
2、然后使用下面陳述句看一下各項指標是否正常，是否有阻塞，正常情況下搜索結果應該為空，
按 Ctrl+C 復制代碼按 Ctrl+C 復制代碼查看是哪些SQL陳述句占用較大可以使用下面代碼
按 Ctrl+C 復制代碼按 Ctrl+C 復制代碼3、如果SQLSERVER存在要等待的資源，那么執行下面陳述句就會顯示出會話中有多少個worker在等待
1 SELECT TOP 10 2 [session_id], 3 [request_id], 4 [start_time] AS '開始時間', 5 [status] AS '狀態', 6 [command] AS '命令', 7 dest.[text] AS 'sql陳述句', 8 DB_NAME([database_id]) AS '資料庫名', 9 [blocking_session_id] AS '正在阻塞其他會話的會話ID',10 der.[wait_type] AS '等待資源型別',11 [wait_time] AS '等待時間',12 [wait_resource] AS '等待的資源',13 [dows].[waiting_tasks_count] AS '當前正在進行等待的任務數',14 [reads] AS '物理讀次數',15 [writes] AS '寫次數',16 [logical_reads] AS '邏輯讀次數',17 [row_count] AS '回傳結果行數'18 FROM sys.[dm_exec_requests] AS der 19 INNER JOIN [sys].[dm_os_wait_stats] AS dows 20 ON der.[wait_type]=[dows].[wait_type]21 CROSS APPLY 22 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 23 WHERE [session_id]>50 24 ORDER BY [cpu_time] DESC
4、查詢CPU占用最高的SQL陳述句
1 SELECT TOP 10 2 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, 3 execution_count, 4 (SELECT SUBSTRING(text, statement_start_offset/2 + 1, 5 (CASE WHEN statement_end_offset = -1 6 THEN LEN(CONVERT(nvarchar(max), text)) * 2 7 ELSE statement_end_offset 8 END - statement_start_offset)/2) 9 FROM sys.dm_exec_sql_text(sql_handle)) AS query_text10 FROM sys.dm_exec_query_stats11 ORDER BY [avg_cpu_cost] DESC
5、索引缺失查詢
1 SELECT 2 DatabaseName = DB_NAME(database_id) 3 ,[Number Indexes Missing] = count(*) 4 FROM sys.dm_db_missing_index_details 5 GROUP BY DB_NAME(database_id) 6 ORDER BY 2 DESC; 7 SELECT TOP 10 8 [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0) 9 , avg_user_impact10 , TableName = statement11 , [EqualityUsage] = equality_columns 12 , [InequalityUsage] = inequality_columns13 , [Include Cloumns] = included_columns14 FROM sys.dm_db_missing_index_groups g 15 INNER JOIN sys.dm_db_missing_index_group_stats s 16 ON s.group_handle = g.index_group_handle 17 INNER JOIN sys.dm_db_missing_index_details d 18 ON d.index_handle = g.index_handle19 ORDER BY [Total Cost] DESC;
找到索引缺失的表，根據查詢結果中的關鍵次逐一建立索引，
做完這些測驗，基本能找到問題，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/34251.html
標籤：SQL Server
下一篇：sqlserver一次性修改多條