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

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

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

4、都消耗到哪里了？差不多是診斷單個查詢性能問題的直通車I/O 瓶頸分析磁盤 I/O 瓶頸分析監視和確定 I/O 瓶頸資源監視器和性能監視器sys.dm_os_wait_stats是什么導致高 I/O 負載?不好的查詢計劃讀/寫了太多的數據緩慢的磁盤子系統一個正常的磁盤平均傳輸時間應低于10ms資源監視器文件級的 I/O 吞吐量及磁盤響應時間性能監視器實時性能監視系統報告當前顯示的計數器I/O 相關的性能計數器（一）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 相關的性能計數器（二）SQL Server Buffer Manager 對象: (內存壓力可能導致 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等待類型數據文件的IO PLETIONASYNC_ PLETIONASYNC_DISKPOOL_LOCKPAGEIOLATCH_日志文件的IOWRITELOGLOGBUFFERLOGMGR_RESERVE_APPEND接下來做什么?進一步找出哪些查詢造成了大量的 I/O 負載，優化這些查詢。索引水平分區和垂直分區觀察是否有內存壓力考慮使用數據壓縮來減少 I/O 負載考慮使用資源調控器來為優先級高的負荷分配 I/O 資源 (SQL 2014)升級存儲硬件評估數據壓縮壓縮表和索引可以減少數據庫大小壓縮比和結構/數據有關，

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

8、TW（Windows 事件跟蹤）挖到更深的信息CPU 性能計數器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 資源調控器新功能你觀察其它

9、性能計數器了嗎?SQL Server:SQL StatisticsBatch requests/secSQL Compilations/secSQL pilations/secSQL Server:Cursor Manager by TypeSQL Server:Cursor Manager Total多個游標的并發處理也會導致 CPU 問題接下來做什么?高 CPU 并不一定意味這是個問題找出哪些負載和查詢造成了大量的 CPU 使用，優化這些查詢。升級硬件為負載分配 CPU 資源資源調控器 為優先級高的負荷分配 CPU/內存 資源（通常基于用戶名、主機名、應用程序名稱等判斷）混合的工作負荷:

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

11、y 對象:Available K/M bytesCommit LimitPaging File %Usage (Peak)Page reads/secProcess 對象:Private bytesVirtual bytesWorking set內存性能計數器（二）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有人在等內存這些緩存頁待了多久沒用過了？常見的內存等待類型CMEMTHREADRESOURCE_SEMAPHORE使用 DBCC 和新的 DMV DBCC MEMORYSTATUSsys.dm_os_sys_memorysystem_memory_stat

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

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

15、rver MAX_SERVER_MEMORY 應設置為64GB 1024x4MB 4GB (操作系統和其它應用程序) = 56GB使用鎖定內存頁的權限 在“本地安全策略”中配置TempDB 競爭Tempdb 競爭Tempdb 存儲空間Tempdb 的分配和釋放誰動了我的 tempdb 空間？用戶對象全局臨時表 (#)本地臨時表 (#)表變量內部對象工作表 (游標, 假脫機,)工作文件 (哈希聯接)排序結果(使用了sort_in_tempdb選項創建/重建索引, Group By,)版本存儲行版本MARS （多個活動結果集）在線索引操作觸發器快照隔離級別他們使用了多少 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 用戶和內部對象sys.dm_db_session_space_usagesys.dm_db_task_space_usage預分配 tempdb 空間分配/釋放 壓力頻繁的分配/釋放對PFS 和 SGAM 頁造成壓力sys.dm_os_waiting_t

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