extend event是否滿足可靠性要求
extend event是否滿足吞吐量要求
extend event對sql server本身語句查詢性能影響到底有多大
這篇文章就是圍繞這幾個問題的量化分析來展開的。
首先，需要說明一下測試環境，我的所有測試資料量化結果都是基于我的測試環境的而得出來的。如果使用者測試環境的配置不同，可能會得到不同的測試量化資料。我的測試環境介紹如下。
主機： mac os x 10.11.6系統上vm主機
cpu：i7-4770 2.2ghz 4 cores 4 logical processor
memory: 5gb
storage: ssd
sql server：sql server 2008r2
測試工具：sqltest 1.0.45.0
sql server幾個關鍵的配置：max degree of parallelism和max server memory (mb)均采用預設值。
測試環境詳情截圖如下：
使用create event session on server語句建立基于執行個體級别的extended event。語句如下：
extended event session對象建立完畢後，需要啟動這個session對象，方法如下：
在選擇使用extend event實作審計日志功能的解決方案之前，該技術方案可行性和吞吐量直接關系到産品的穩定性和功能延續性，這些特性對于審計日志功能都非常重要，我們需要經過嚴格的可靠性和吞吐量測試，以確定extend event技術方案滿足這兩方面的要求的同時，又不會對sql server本身性能和吞吐量造成大的影響（假設條語句性能和吞吐量影響超過5%定義為大的影響）。
可靠性測試的方法是，我們使用sqltest工具開4個并發線程執行查詢語句，持續運作10分鐘時間，同時，使用profiler抓取sql:stmtcompleted事件(功能和extend event事件sql_statement_completed功能相同)，來校驗extend event抓取的記錄數，如果兩者的記錄數相同說明extend event滿足可靠性要求。在測試的短短10分鐘左右時間内，檢視profiler抓取到的記錄數為3189637，總共310多萬條記錄，參見如下截圖：
而，extend event總共生成了341個審計日志檔案，每個日志檔案最大大小為10mb（這裡調整了最多的檔案數量為500，以滿足測試産生的資料要求），總共大小為近3.18gb。
使用系統提供的函數sys.fn_xe_file_target_read_file讀取extend event生成的審計日檔案記錄總數，展示也是3189637條，這個記錄總數和sql profiler抓取到的記錄數是恰好吻合。
從測試的結果來看，extend event實作審計日志功能可靠性有保證，在10分鐘310多萬條語句執行的壓力下，依然可以工作良好。
可靠性測試是保證extend event在抓取審計日志時的穩定性和功能健壯性，簡單講就是“不丢資料”，而吞吐量的測試是要回答“extend event到底在多大的查詢吞吐量時，依然能夠工作良好？”。就可靠性測試的我們來簡單推算一下：10分鐘的測試，共執行3189637條查詢，生成了3.18gb的審計日志檔案，以此來推算每秒，每分鐘，每小時，每天可以抓取到的查詢記錄數和産生的日志檔案大小，如下圖計算所示：
平均每秒抓取5316條審計日志和記錄5.43mb日志檔案
平均每分鐘抓取318963條審計日志和記錄325.6mb日志檔案
平均每小時抓取19137822條審計日志和記錄19.08gb日志檔案
平均每天抓取459307728條審計日志和記錄457.92gb日志檔案
從這個數量級别來看，extend event實作審計日志功能平均每天吞吐量可以達到4億5千萬條審計日志記錄；生成457.92gb審計日志檔案，完全可以滿足我們的業務要求吞吐量了。
為了測試extend event對使用者sql server執行個體的性能影響，我們的思路是在停止和啟用extend event session的場景下，統計一千條相同查詢（簡稱千查詢）在不同數量并發線程情況下時間消耗和機關時間内（以1分鐘為機關）的疊代次數，最終以得到的測試資料做為标準。
測試之前，對測試資料的定性分析邏輯是：
機關時間内疊代千查詢的次數越多，性能越優
千查詢消耗時間越少，性能越優
停止和啟用extend event session情況下，以上兩個名額越接近，差異越小，說明extend event對sql server性能影響越小，是以也就越好
建立測試對象表：建立測試表tab72，并初始化5萬條資料。
千查詢測試語句：就是針對某個查詢語句循環1000次。
測試方法：使用sqltest，分别測試在1、2、4、8、16、32個并發線程情況下，機關時間内（1分鐘）千查詢的平均疊代次數和時間消耗。
使用sqltest，在開啟不同數量的并發查詢線程情況下，擷取到的千查詢平均時間消耗資料統計如下：
千查詢平均耗時統計資料表格
其中：
at_pk_xe：啟用extend event session場景下，使用主鍵千查詢的平均耗時。
at_pk_nonxe：停用extend event session場景下，使用主鍵千查詢的平均耗時。
at_pk_range_xe：啟用extend event session場景下，使用主鍵範圍查找，千查詢的平均耗時。
at_pk_range_nonxe：停用extend event session場景下，使用主鍵範圍查找，千查詢的平均耗時。
at_nonxe_xe_gap：使用主鍵千查詢，在啟用和停用extend event session兩種場景的平均耗時差異。
at_range_nonxe_xe_gap：使用主鍵範圍千查詢，在啟用和停用extend event session兩種場景的平均耗時差異。
将“千查詢平均耗時統計資料表格”資料，做成echart圖，我直覺的來看看平均時間消耗差異。
使用單主鍵查找的千查詢平均時間消耗圖
從這個圖，我們可以做如下總結：
線條at_nonxe_xe_gap表示停止和啟用extend event session兩個場景，千查詢平均時間消耗差異，總體差異不大；但差異會随着線程數量的不斷增加，而拉大。
在并發線程為4的時候（這個數字和我的測試機cpu cores個數驚人的相等），千查詢平均時間消耗差異最小，僅為29毫秒，千查詢平均耗時影響為29*100/270 = 10.74%，即單語句查詢的平均耗時影響為0.01074%。
使用主鍵範圍查找的千查詢平均時間消耗圖
線條at_range_nonxe_xe_gap表示停止和啟用extend event session兩個場景，千查詢平均時間消耗差異，總體差異不大（除開2個線程情況下）；但差異會随着線程數量的不斷增加，而拉大。
同樣，在并發線程為4的時候，千查詢平均時間消耗差異最小，僅為58毫秒，千查詢平均耗時影響為58*100/1712=3.39%，即單語句查詢平均耗時影響為0.00339%。
根據以上對千查詢平均耗時統計資料和做圖，總結如下：
無論是基于主鍵的單值查詢語句，還是主鍵的範圍查詢語句，禁用和啟用extend event session，對于千查詢的平均耗時差異不大，在并發線程為4的時候，差異最小；千查詢平均耗時差異為29毫秒和58毫秒，性能影響為10.74%和3.39%；單語句查詢平均耗影響分别為0.01074%和0.00339%。
這一小節從另外一個角度來看extend event對sql server性能的影響，讓我們來看看在機關時間内（1分鐘内）千查詢疊代次數。千查詢疊代次數統計表格
其中，表格每行資料表示千查詢疊代次數，第一列與千查詢平均時間消耗表達含義類似，這裡不再累述。
使用單主鍵千查詢在機關時間内的疊代次數統計資料，做圖如下：
從圖表直覺反映，我們可以發現如下規律：
ai_nonxe_xe_gap線條表示千查詢疊代次數差異，随着并發線程數增加，差異被拉大。
禁用和啟用extend event session場景下，當并發線程數為4的時候，千查詢疊代次數差異最小，這個規律和單主鍵千查詢平均時間消耗規律相似。啟用extend event session，對疊代次數影響是85*100/897=9.47%，換算成單個查詢語句的疊代次數影響為0.00947%。
使用主鍵範圍查找的千查詢疊代次數做圖如下：
同樣，我們可以直覺的發現以下規律：
疊代次數随着線程數量的增加而增加，在16個并發線程時達到頂峰，疊代次數開始下降。
禁用和啟用extend event session場景下，千查詢疊代次數差異在并發4個線程時（忽略并發線程為2的情況），最小值為8，這個規律和千查詢平均時間消耗規律十分類似。啟動extend event session後，對千查詢的疊代次數影響為8*100/142=5.63%，換算成單個查詢語句的疊代次數影響為0.00563%。
根據以上對千查詢疊代次數資料和做圖，總結如下：
無論是基于主鍵的單值查詢語句，還是主鍵的範圍查詢語句，禁用和啟用extend event session，千查詢的疊代次數差異并不大，在并發線程為4的時候，差異達到最小值；千查詢疊代次數差異為85和8次，啟用extend event session後，對千查詢在主鍵查找和主鍵範圍查找場景下，疊代次數影響為9.47%和5.63%；單查詢平均疊代次數影響分别為0.00947%和0.00563%。
在啟用了extend event session抓取審計日志以後，對使用者sql server執行個體性能影響的量化分析總結如下：
單主鍵查找千查詢，平均耗時影響為10.74%；換算為單主鍵單語句查詢，性能影響為0.01074%。
單主鍵查找千查詢，機關時間内（1分鐘）疊代次數影響（吞吐量）為9.47%；換算為單主鍵單語句查詢，性能影響為0.00947%。
主鍵範圍查找千查詢，平均耗時影響為3.39%；換算成單主鍵單語句查詢，性能影響為0.00339%。
主鍵範圍查找千查詢，機關時間内（1分鐘）疊代次數影響（吞吐量）為5.63%；換算成單主鍵單語句查詢，性能影響為0.00563%。
将以上文字描述的數字解決做成一個直覺的圖形，我們發現在開啟extend event實作審計日志功能時，對于單條語句查詢性能的影響最大約為0.01%；而對于單語句查詢吞吐量的影響不超過0.01%。
從這個量化分析的總結來看，extend event對使用者sql server性能影響是，千查詢語句的性能影響在3% ~ 10%之間；單條語句查詢性能和吞吐量損失均在0.01%小幅波動，這個影響相對于profiler已經非常小了。是以，方案可行，并且影響在可控的範圍内。
<a href="https://sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events">measuring “observer overhead” of sql trace vs. extended events</a>
<a href="https://channel9.msdn.com/series/sql-workshops/extended-event-query-post-execution-showplan-in-sql-server">sqltest測試方法參考連結</a>