如何用DPA結合Zabbix分析定位SQL Server的tempdb數據庫暴增問題
本文轉載自微信公眾號「DBA閑思雜想錄」，作者瀟湘隱者。轉載本文請聯(lián)系DBA閑思雜想錄公眾號。
這篇文章分享一下如何通過使用Zabbix與DPA工具(SolarWinds的Database Performance Analyzer)結合來分析、定位SQL Server的tempdb數據庫大小暴增的問題。個人經驗，沒有完美的監(jiān)控工具，所謂尺有所長，寸有所短。監(jiān)控方案不要全部依賴一個工具，最好是多個監(jiān)控工具搭配與結合，長短互補。多方面、多層次監(jiān)控。
如下所示，Zabbix監(jiān)控發(fā)出了告警，磁盤空間告警和數據庫無法分配新的頁面告警，如下所示
- G:: Disk space is low (used > 90%)
- SQL Instance MSSQLSERVER: Could not allocate a new page
如下截圖所示，可以看到G盤(此盤單獨存放tempdb的數據文件)在短短一小時內暴增了100多G。
注意：其實是2點55左右開始的，在接近4點時結束，主要發(fā)生在3:00-4:00這個時間段，所以我們下面主要分析這個時間段內的數據。
Zabbix發(fā)出告警的同時，其實DPA也出現(xiàn)告警了，但是它提示這個時間段的性能出現(xiàn)嚴重下降，等待事件的累積時間彪增，指標從綠色變成了紅色。但是當時個人正在處理另外一個問題，沒有留意到告警信息，錯過了最佳時機，等到發(fā)現(xiàn)問題的時候，已經無法抓取問題SQL，但是DPA工具抓取、捕獲了相關SQL以及一些指標數據。
首先，我們分析這個時間段的各項指標數據，做下鉆分析，發(fā)現(xiàn)這個時間段內的磁盤讀寫延時非常高，如下截圖所示
加入Total I/O Wait Time等分析指標，如下所示，默認情況下，只顯示部分指標數據，如果你需要觀察其它一些指標數據，需要做切換。如下截圖所示
如下所示，Total I/O Wait Time也高得離譜。
其實通過環(huán)比、同比分析發(fā)現(xiàn)，我們就會發(fā)現(xiàn)其實正常情況下，數據庫的讀寫時延基本都在10毫秒以下。如下其中一張截圖所示：
按時間劃片，我們觀察到3點到4點間，等待時間最多的是下面兩個SQL(SQL Hash值為5444075766和3439056218的SQL語句)，如下截圖所示：
經過下鉆分析，我們排除掉Hash值為5444075766的SQL，著重分析SQL Hash值為3439056218的SQL語句，如下截圖所示，發(fā)現(xiàn)這個SQL語句出現(xiàn)了大量的等待事件(如下所示)，從這些等待事件分析，主要是Memory/CPU等待事件(后面分析會讓你會知道為什么Memory/CPU等待事件占了大頭)和PREEMPTIVE_OS_FILEOPS等待事件，基于經驗判斷，估計這個SQL是罪魁禍首的可能性非常大，因為大量PREEMPTIVE等待事件，以及PREEMPTIVE_OS_FILEOPS等待非常高，一般出現(xiàn)大量的PREEMPTIVE_OS_FILEOPS等待事件，很有可能出現(xiàn)大量的IO操作，在這里就極有可能是tempdb空間的分配和占用。當然這個不是絕對的。不能作為因果推理。
- PREEMPTIVE_OS_GETDISKFREESPACE
- PREEMPTIVE_OS_FILEOPS
- PREEMPTIVE_OS_WRITEFILEGATHER
- Memory/CPU
- PAGEIOLATCH_UP
PREEMPTIVE_OS_FILEOPS等待事件，它產生在當線程調用與文件系統(tǒng)相關的多個Windows 函數之一時，此等待類型是一種通用等待。然后我們從“Waits”這個維度下鉆分析，發(fā)現(xiàn)這個等待事件下，也主要是SQL Hash值為3439056218的SQL語句。其它語句產生這個等待事件微乎及微。這個現(xiàn)象也增加了我們初步判斷的正確性。
從其它維度(Files)下鉆分析，發(fā)現(xiàn)也是SQL Hash值為3439056218的SQL語句
然后仔細分析發(fā)現(xiàn)這個SQL，發(fā)現(xiàn)SQL里面使用CROSS JOIN關聯(lián)一個表和視圖，但是WHERE語句中兩者居然沒有關聯(lián)條件，也就是說這個SQL變成了一個笛卡爾積。再加上SQL中還有排序等操作，所以這個SQL要消耗大量的tempdb的空間。由于笛卡爾積，所以才出現(xiàn)Memory/CPU等待是大頭的現(xiàn)象。至此，分析結束。正向分析和反向印證了這個SQL確實是導致tempdb暴增的原因。
