關於SQL Server 監控與最佳化

最近有個培訓，需要講解SQL Server最佳化，做些儲備。

 

首先做幾個名詞的說明：
執行計畫（Plan）：sql 編譯後的程式碼片段，並不是二進位代碼，而是針對SQL的執行代碼，叫做plan。一個過程可能由於參數和分支執行不同，sql會編譯成多個plan。
Perfmon：Server系統提供的performancemonitor，效能分析工具
DMVs：SQL Server提供的用於分析sql效能的一些系統檢視表
DBCC：SQL Server的一種控制台命令，對於資料庫進行物理或者邏輯處理。

SQL server 效能瓶頸一般可以分為三類：
1. CPU瓶頸
2. 記憶體瓶頸
3. IO讀寫瓶頸

下面我們來分別說明一下。

產生cpu瓶頸：

一、重編譯 
              exec('sql'); 
              withrecompiles; 
              暫存資料表變化；
       處理：Perfmon counters
              Batch　Requests/sec 每秒執行批處理數
              SQLcompilations/sec 每秒編譯sql語句數 參數不同會產生不同plan被系統catch 正常使用

一段時期後應當不應該太多
              SQLRecompilations/sec
              Rationo recompile to batch requests should be very low
             
查詢重編譯語句
select top 25
       SQLText.text,sql_handle,plan_generation_num,
       execution_count,dbid,objectid
from sys.dm_exec_query_stats a
       crosapply sys.dm_exec_sql_text(sql_handle)as SQLText
where plan_generation_num>1
order by plan_generation_num desc

查詢系統中最慢的50條語句
select top 50 
       sum(qs.total_worker_time)as TotalCpuTime,
       sum(qs.execution_count)as TotalExecutionCount,
       count(*)as NumberOfStatements,
       qs.plan_handle
from sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

查看編譯花費時間
select * from sys.dm_exec_query_optimizer_info

解決辦法：
       1. 盡量避免在預存程序中使用set語句設定資料庫狀態（會引起重編譯）
       2. 使用暫存資料表時，使用表變數或者使用keep plan
       3. 盡量避免使用select * from tabel，如果schema變化會引起重編譯
       4. 不要把DML和DDL寫在一起進行互動。會引起schema變化，引起重編譯
       5. 盡量避免使用if 每一個條件都會產生一個執行計畫
      
二、查詢
1.串連查詢（需要根據兩個表資料量不同具體調試）

查看那些查詢最耗費時間
Find queries use most cumulative cpu through
       dm_exec_query_stats
Look for cpu intensive operators through
       dm_exec_cached_plans

三、平行處理

Perfmon：Process-%Processor Time -SqlServr.exe

DMVs:
Find query plans that may run in parallel
select p.*,q.*,cp.plan_handle
from sys.dm_exec_cached_plans cp
       crossapply sys.dm_exec_query_plan(cp.plan_handle) p
       crossapply sys.dm_exec_sql_text(cp.plan_handle)as q
where
       cp.cacheobjtype='CompiledPlan' and
       p.query_plan.value('declarenamespace 

p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)','float')>0

Only parallel query could use more cpu time thanthe elapsed time
select qs.sql_handle,qs.statement_start_offset,
       qs.statement_end_offset,q.dbid,q.objectid,q.number,
       q.encrypted,q.text
from sys.dm_exec_query_stats qs
       crossapply sys.dm_exec_sql_text(qs.plan_handle) as q
where qs.total_worker_time >qs.total_elapsed_time

SQL Traces:
Showplans that have Parallelism operators
       selectEventClass,StmtText
       from::fn_trace_gettable('c:\temp\high_cpu_trace.trc',default)
       whereStmtText like '%Parallelism%'
Parallel query use more cpu time than theelapsed time
       selecteventClass,StmtText
       from::fn_trace_gettable('c:\temp\high_cpu_trace.trc',default)
       whereeventclass in(10,12)
              --Rpc:Completed,SQL:BatchCompleted
       andcpu>duration/1000
              --Cpu is in milliseconds,duration in microseconds

處理：
       1. 增加CPU個數
       2. 最佳化查詢語句
       3. 資料表值函式和CLR函數比較耗費cpu適當最佳化

記憶體最佳化

檢查記憶體壓力來自內部還是外部方法：
使用任務管理工具 在效能選項卡下，“AvailablePhysical Memory”<50M 可用記憶體小於50M
檢查頁檔案Page File size
       -頁檔案超過實體記憶體2倍
       -使用任務管理工具 在效能選項卡下commit change 不應該大於實體記憶體
查看其它應用程式是否佔用了過多記憶體
       -除去SQL Server之外的應用程式是否佔用大量記憶體，例如IE，搶佔sql記憶體

確認內部記憶體壓力：

dbcc memorystatus

查看記憶體配置情況
DMVs
select type,sum(multi_pages_kb)
from sys.dm_os_memory_clerks
where multi_pages_kb !=0
group by type

排錯流程：
1. 你的伺服器是面臨外部記憶體壓力還是內部記憶體壓力
       外部壓力：調節SQLserver 對記憶體的使用量，設定sql屬性，擴大使用記憶體，殺掉外部進程
       內部壓力：DBCC或者DMVs具體解決。
       錯誤701：記憶體不足，外部壓力，擴大記憶體或者減少應用
       錯誤802：bufferpool沒有記憶體，如果釋放buffer pool，則所有plan都沒了
       錯誤8645：執行過程中等待記憶體配置逾時。使用很多暫存資料表，大量並發。找到問題查詢

2. 收集效能資料SQL Server：BufferManager，SQL Server:Memory Manager
3. 驗證配置參數（sp_configure)
       'minmemory per query','min/max server memory',
       'aweenabled'-- 大記憶體支援 超過4g需要使用,'Lock pages in memory'
4. DBCC Memorystatus

IO瓶頸

檢查
1 perfmon
       磁碟隊列 avg.disk queue length>2 說明有等待
       平均讀寫 avg.disk sec/read?0.12 , avg.disksec/write >0.12
       磁碟時間 %Disk time>50%
       avg.diskreads/sec>85%,avg.disk writes/sec>85%
2 使用RAID
Raid 0-- I/Os per disk=(reads+writes)/number ofdisks
效能最大提升
Raid 1-- I/Os per disk = [reads+(2*writes)]/2
Raid 5-- I/Os per disk=[reads+(4*writes)]/number of disks
讀效能提示，寫效能不如raid0
Raid 10--I/Os per disk=[reads+(2*writes)]/number of disks

3 DMVs
-- Physical IO wait when reading and writingbuffer pages
selectwait_type,waiting_tasks_count,wait_time_ms 
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type
--不同的鎖等待的時間

--pending IO request
selectdatabase_id,file_id,io_stall,io_pending_ms_ticks, scheduler_address
from sys.dm_io_virtual_file_stats(null,null)t1,
       sys.dm_io_pending_io_requestsas t2
where t1.file_handle=t2.io_handle
-- 如果有資料，說明iO有問題

解決：
通過磁碟索引嚮導，最佳化索引，減少磁碟掃描

找到最浪費IO效能的查詢
select top 5
(total_logical_reads/execution_count) asavg_logical_reads,
(total_logical_writes/execution_count)asavg_logical_writes,
(total_physical_reads/execution_count)asavg_physical_reads,
execution_count,
statement_start_offset,
sql_handle,
plan_handle,
SQLText.Text
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) asSQLText
order by(total_logical_reads+total_logical_writes)/execution_count desc