sql數(shù)據(jù)庫培訓(xùn)19瓶頸分析

1、資源瓶頸分析瓶頸分析按照方法來 別瞎猜!理解時間花在哪兒了總計時間 = 運行 + 等待關(guān)鍵就是等待 等待是什么類型?對產(chǎn)品要有個構(gòu)思模型 去推斷性能查找主要等待類型 記住這些問題代碼在哪里消耗了時間?為什么 CPU 沒有用起來?能并發(fā)運行多少件事情?我怎么來測量所有這些東西?瓶頸分析典型問題典型問題太長的等待 PAGEIOLATCH_太長的總等待 ASYNC_NETWORK_IO 或 OLEDB太長的等待 WRITELOG太長的等待 LCK_ 在Profiler中顯示了大量的死鎖不必要的大麻煩.大量的客戶 (現(xiàn)在還是這樣，唉！) 認為阻塞 = 死鎖LATCH 和 PAGELATCH 等待 =

2、值得關(guān)注的事平均等待也是值得關(guān)注的雖然最長等待時間通常會指出問題所在SQL Server 錯誤日志警告討厭的等待要熟悉常見的等待類型并發(fā)有自己的等待詳見下頁一些等待表示問題在數(shù)據(jù)庫引擎之外OLEDB 就是一個PREEMPTY_OS_LOADLIBRARY但是大部分 PREEMPTIVE 類型的等待都是無害的大部分引擎內(nèi)部的等待可以在 sys. dm_os_latch_stats 中診斷通常和引擎瓶頸相關(guān). 如果沒有很大的工作負載，很難看到LATCH的等待通常最主要的等待是 BUFFER并發(fā)等待LATCH_表示你需要去查看 dm_os_latch_stats通常這里值得關(guān)注一般和高并發(fā)有關(guān)PAG

3、EIOLATCH_ - 熱點等待詳見下文（ I/O 瓶頸分析）SOS_SCHEDULER_YIELD通常指示 CPU 被加載了超過負荷的工作也可能指示了數(shù)據(jù)倉庫的一種特殊情況CXPACKET傳言: 并行度出現(xiàn)問題事實: 忽略這個等待! 較大的CXPACKET等待很常見，通常并不說明出現(xiàn)問題。挖得更深 - XEvents擴展事件(XEvents) 是 SQL Server 2008 的一個新工具它能讓你訪問到單個會話的更細節(jié)的信息SQL 2008需要寫代碼使用，SQL 2012提供了界面 (小心SSMS吃掉你的內(nèi)存！)CodePlex 里面有個項目可以幫助上手: 它能回答這樣的問題: 查詢的時間

4、都消耗到哪里了？差不多是診斷單個查詢性能問題的直通車I/O 瓶頸分析磁盤 I/O 瓶頸分析監(jiān)視和確定 I/O 瓶頸資源監(jiān)視器和性能監(jiān)視器sys.dm_os_wait_stats是什么導(dǎo)致高 I/O 負載?不好的查詢計劃讀/寫了太多的數(shù)據(jù)緩慢的磁盤子系統(tǒng)一個正常的磁盤平均傳輸時間應(yīng)低于10ms資源監(jiān)視器文件級的 I/O 吞吐量及磁盤響應(yīng)時間性能監(jiān)視器實時性能監(jiān)視系統(tǒng)報告當前顯示的計數(shù)器I/O 相關(guān)的性能計數(shù)器（一）PhysicalDisk 對象:Avg. Disk Queue LengthAvg. Disk Sec/ReadAvg. Disk Sec/Write%Disk TimeDisk Tr

5、ansfers/SecAvg. Disk Bytes/ReadAvg. Disk Bytes/WriteDisk Read Bytes/SecDisk Write Bytes/SecI/O 相關(guān)的性能計數(shù)器（二）SQL Server Buffer Manager 對象: (內(nèi)存壓力可能導(dǎo)致 I/O 壓力)Buffer Cache hit ratioPage Life ExpectancyCheckpoint pages/secLazywrites/secPage reads/secPage writes/secSQL Server:Wait StatisticsLog buffer waits

6、Log write waitsPage IO latch waits常見的IO等待類型數(shù)據(jù)文件的IO PLETIONASYNC_ PLETIONASYNC_DISKPOOL_LOCKPAGEIOLATCH_日志文件的IOWRITELOGLOGBUFFERLOGMGR_RESERVE_APPEND接下來做什么?進一步找出哪些查詢造成了大量的 I/O 負載，優(yōu)化這些查詢。索引水平分區(qū)和垂直分區(qū)觀察是否有內(nèi)存壓力考慮使用數(shù)據(jù)壓縮來減少 I/O 負載考慮使用資源調(diào)控器來為優(yōu)先級高的負荷分配 I/O 資源 (SQL 2014)升級存儲硬件評估數(shù)據(jù)壓縮壓縮表和索引可以減少數(shù)據(jù)庫大小壓縮比和結(jié)構(gòu)/數(shù)據(jù)有關(guān)，

7、一般是原大小的40-60%減少查詢的 I/O 開銷(但增加 CPU 開銷)評估壓縮后的大小 pression_savingssys.dm_db_index _operational_stats列存儲索引的壓縮比一般是原大小的10%或更小（可更新的列存儲聚集索引 SQL 2014）CPU 瓶頸分析CPU 瓶頸分析持續(xù)的 CPU 使用率 70-80%性能監(jiān)視器/任務(wù)管理器DMV 和擴展事件什么導(dǎo)致了高 CPU 使用率?重編譯游標不好的查詢計劃其它工具資源監(jiān)視器/任務(wù)管理器實時監(jiān)測性能監(jiān)視器實時或記錄性能數(shù)據(jù)系統(tǒng)數(shù)據(jù)收集器集報告格式化的性能監(jiān)視器數(shù)據(jù)報告Windows 性能工具 (XPerf)使用E

8、TW（Windows 事件跟蹤）挖到更深的信息CPU 性能計數(shù)器Processor 對象:%Privileged Time%Processor Time%User TimeProcess 對象(SQL Server):%Privileged Time%Processor Time%User TimeSQL Server:Workload Group StatsCPU usage %Max request cpu timeSQL Server:Resource Pool StatsCPU usage %CPU usage target %SQL Server 2008 資源調(diào)控器新功能你觀察其它

9、性能計數(shù)器了嗎?SQL Server:SQL StatisticsBatch requests/secSQL Compilations/secSQL pilations/secSQL Server:Cursor Manager by TypeSQL Server:Cursor Manager Total多個游標的并發(fā)處理也會導(dǎo)致 CPU 問題接下來做什么?高 CPU 并不一定意味這是個問題找出哪些負載和查詢造成了大量的 CPU 使用，優(yōu)化這些查詢。升級硬件為負載分配 CPU 資源資源調(diào)控器 為優(yōu)先級高的負荷分配 CPU/內(nèi)存 資源（通?；谟脩裘?、主機名、應(yīng)用程序名稱等判斷）混合的工作負荷:

10、OLTP, 報表, 維護任務(wù)軟件 NUMA節(jié)點配置 (軟件 NUMA 節(jié)點和 CPU 掩碼)網(wǎng)絡(luò)配置 (軟件 NUMA 節(jié)點和 TCP 端口)內(nèi)存瓶頸分析內(nèi)存瓶頸監(jiān)視和確定內(nèi)存壓力性能監(jiān)視器/任務(wù)管理器DMV 和擴展事件什么吃光了我的內(nèi)存?內(nèi)部或外部的內(nèi)存壓力不好的查詢計劃 (哈希, 排序,)32位-64位AWE 32位 使用 AWE 來緩存數(shù)據(jù)/索引頁環(huán)境虛擬尋址空間最大物理內(nèi)存32位操作系統(tǒng)上的32位SQL2 GB64 GB64位操作系統(tǒng)上的32位SQL(Wow)4 GB64 GB64位操作系統(tǒng)上的64位SQL8 TB2 TB內(nèi)存性能計數(shù)器（一）性能監(jiān)視器是確定內(nèi)存壓力最好的工具Memor

11、y 對象:Available K/M bytesCommit LimitPaging File %Usage (Peak)Page reads/secProcess 對象:Private bytesVirtual bytesWorking set內(nèi)存性能計數(shù)器（二）SQL Server Memory Manager object:Connection MemoryLock MemoryOptimizer MemoryMemory Grants Pending SQL Server Buffer Manager object:Buffer cache hit ratioCheckpoint pa

12、ges/secFree list stalls/secFree pagesLazy writes/secPage life expectancyStolen pagesTarget pagesTotal pagesSQL Server Plan Cache objectSQL Server Resource Pool Stats object有人在等內(nèi)存這些緩存頁待了多久沒用過了？常見的內(nèi)存等待類型CMEMTHREADRESOURCE_SEMAPHORE使用 DBCC 和新的 DMV DBCC MEMORYSTATUSsys.dm_os_sys_memorysystem_memory_stat

13、e_desc 很好的說明了內(nèi)存狀態(tài)sys.dm_os_process_memory process_physical_memory_low = 1 表示物理內(nèi)存不足memory_utilization_percentage 低于 100% 表示外部內(nèi)存壓力sys.dm_os_memory_clerks使用環(huán)形緩存區(qū)sys.dm_os_ring_buffers RING_BUFFER_SCHEDULER_MONITORRING_BUFFER_RESOURCE_MONITORRING_BUFFER_OOMRING_BUFFER_MEMORY_BROKER RING_BUFFER_BUFFER_PO

14、OL接下來做什么？檢查服務(wù)器內(nèi)存配置 sp_configure外部內(nèi)存壓力 檢查什么程序在競爭內(nèi)存內(nèi)部內(nèi)存壓力 檢查哪個 SQL 組件消耗了內(nèi)存負荷/查詢調(diào)優(yōu) (排序, 哈希聯(lián)接)資源調(diào)控器升級硬件服務(wù)器內(nèi)存配置配置服務(wù)器內(nèi)存的關(guān)鍵: 避免分頁文件（虛擬內(nèi)存）的使用盡量讓所有應(yīng)用程序使用的內(nèi)存量小于服務(wù)器物理內(nèi)存SQL Server MAX_SERVER_MEMORY=服務(wù)器物理內(nèi)存減 最大工作線程數(shù) 乘以 線程堆棧大小32位: 512K; X64: 2MB; IA64: 4MB再減 操作系統(tǒng)和其它應(yīng)用程序所需內(nèi)存大概 2-4GB例如: IA64 64GB 內(nèi)存，1024工作線程數(shù)SQL Se

15、rver MAX_SERVER_MEMORY 應(yīng)設(shè)置為64GB 1024x4MB 4GB (操作系統(tǒng)和其它應(yīng)用程序) = 56GB使用鎖定內(nèi)存頁的權(quán)限 在“本地安全策略”中配置TempDB 競爭Tempdb 競爭Tempdb 存儲空間Tempdb 的分配和釋放誰動了我的 tempdb 空間？用戶對象全局臨時表 (#)本地臨時表 (#)表變量內(nèi)部對象工作表 (游標, 假脫機,)工作文件 (哈希聯(lián)接)排序結(jié)果(使用了sort_in_tempdb選項創(chuàng)建/重建索引, Group By,)版本存儲行版本MARS （多個活動結(jié)果集）在線索引操作觸發(fā)器快照隔離級別他們使用了多少 tempdb 空間？sys

16、.dm_db_file_space_usage版本存儲sys.dm_tran_active_snapshot_database_transactionsSQL Server:Transactions - Version Generation Rate SQL Server:Transactions - Version Cleanup Rate 用戶和內(nèi)部對象sys.dm_db_session_space_usagesys.dm_db_task_space_usage預(yù)分配 tempdb 空間分配/釋放 壓力頻繁的分配/釋放對PFS 和 SGAM 頁造成壓力sys.dm_os_waiting_t

17、asks 會看到 Latch 等待PFS 頁的位置在 2:1:1, 每個PFS 會記錄 8088 頁的分配狀態(tài)信息SGAM 頁在 2:1:3, 每個SGAM會包含 511,232 頁的分配映射信息 跟蹤分配/釋放的性能計數(shù)器SQL Server:Access Methods workfiles(tables)/secSQL Server:General Stats Temp Tables Creation RateSQL Server:General Stats Temp Tables For destruct建議（并發(fā)非常頻繁的系統(tǒng)）增加 tempdb 文件數(shù)目(4-8個)使用 Sqlservr.exe 的T1118 參數(shù)啟用1118跟蹤標記如果不是 PFS/SGAM 頁的競爭檢查應(yīng)用程序以減少臨時表/表變量的使用檢查查詢計劃以減少排序，假脫機等。建立適當?shù)乃饕龝袔椭Ｔ黾优R時對象的緩存機會不要在創(chuàng)建臨時表后