1. 首頁 > 生活 > >

sqlserver日志查看刪除記錄 sqlserver日志查看視圖變動

小知識



文章插圖
sqlserver日志查看刪除記錄 sqlserver日志查看視圖變動

文章插圖
在《深入解析SQL Server并行執行原理及實踐（上）》談完并行執行的原理，咱們再來談談優化，到底并行執行能給我們帶來哪些好處，我們又應該注意什么呢，下面展開 。
Amdahl’s Law
再談并行優化前我想有必要談談阿姆達爾定律，可惜老爺子去年已經駕鶴先去了 。
其中 P：可以并行的百分比
N：算法并行計算使用的“CPU”
這里我們舉個簡單的例子，我們來做一份大餐，如圖1-1所示：
圖1-1
土豆泥，荷蘭豆，雞排還有整體組合各需十分鐘 。在這里前三個食材是可以共同執行的，也就是說4個步驟中3步可并行 P=3/4，其中3個食材可同時加工N=3，則根據公式S(N)=1/(1-0 。75)+0 。75/3 =2這個操作整體相比完全串行可提升200%即做這個大餐的時間由40分鐘，縮短到20分鐘 。
這里實際引申出一個事實，整體的操作時間和整體事務中串行的比例多少有很大關聯，這個引申到我們的并行調優中，并行可以改進查詢，但受串行部分影響也是很大的，同時也告之我們沒有必要過度串行優化，還是做大餐，即便并行可做的越來越多，但改進的效果卻越發不明顯，過度無謂的優化我們是應該避免的，如圖1-2：
圖1-2
SQL Server中禁止并行的一些操作
上面我們了解了并行的操作性，但在SQL Server并不是所有操作都可以并行的，有些操作符會導致整個執行計劃無法并行，而有些會使得某些分支(branch)無法并行，下面羅列出相關的操作符對并行的影響，這些感興趣的朋友可以自行測試 。
執行計劃禁止并行
T-SQL scalar functions
更新表變量數據
訪問系統表
動態游標
【sqlserver日志查看刪除記錄 sqlserver日志查看視圖變動】Branche不能并型
TOP（global）
2012以前窗口函數(Row_Number)
Multi -Statement Function
Backward scan
CTE遞歸
并行執行的優點
說了一些限制，我們再來簡單說下優點，要不開此文章的意義何在 。實際上在上半部分文章中大家可能已經感受到它的優點了，這里再簡單總結下 。實際上在并行中，計算工作是均勻的分配在參與并行的threads中，所有的threads同時工作，無先后之分 。某些操作中threads自身的工作完成后還會協助threads工作，雖然會有短暫的CXPACKET等待(數據分布，預估等問題)但基本可以解決或是緩解 。
執行時分支(Branchs)間有時可以是無順序的，更好地增加了并行 。
針對CPU-Bound的操作，SQL Server可以說時隨著CPU(并行度)的增加，性能也基本是線性增加的 。
并行相關的設置
SQL Server中有并行相關的一些設置，主要有兩個：并行閾值及最大并行度 。
并行閾值：上篇已經提到過，查詢子樹大小觸發并行的條件，此值的設定仁者見仁智者見智了，一般設定為實例執行計劃編譯的平均查詢子樹大小上下幅度不超過20%
最大并行度：查詢中的操作符可同時采用的線程數 。這個值便隨著NUMA的誕生應尤為注意，早在SQL 2005研發階段，NUMA開始出現，而SQL 2005也提供了支持，但程度有限，隨著SQLOS的進一步演化在SQL2008時對其支持已經很不錯了，這里有大家都知道的NUMA架構下的foreign memory問題，實際上SQL Server在采用并行時會試圖將并行的線程都集中在某個NUMA節點下，所以我們在配置初始參數時并行度最好控制在某個NUMA節點的核數內，而且最好是偶數，這里面涉及到很多SQLOS的知識，限于篇幅就不深入了 。
使用并行應注意的問題
強制使用并行
Trace flag 8649 在SQL Server中可以不觸發并行而手動指定并行，注意這個標記是無官方文檔記錄，勿輕易使用 。使用時只需在查詢最好加上query hint：Option(querytraceon 8649)即可 。
數據分布不均，預估，碎片等問題
導致CXPACKET等待以及過多無謂IO，應對方式創建臨時對象，更新統計信息，整理碎片等 。
nested loop Join導致的隨機IO，及nested loop join預讀問題等
冷數據中使用并行nested join可能導致實例的IO穩定性受影響，面對具體場景應酌情使用 。應對方式可以關閉nested loop預讀，而nested loop預讀時SQL Server也會試圖將隨機IO轉化為連續IO，如具體應用合理應接受并行nested loop join 。
線程饑餓問題(worker thread starvation)
前面我們說過，線程的授予是按照分支(branches)及并行度授予的，如果并行度高，此時復雜的查詢下分支又很多，這個時候可能針對某個查詢分配過多線程，加之這類查詢又高并發，則這時出現線程饑餓的幾率就大大增加了 。具體生產中，這個應引起我們的注意 。這里給大家舉個簡單的實例，感興趣的同學可以自己測試下 。這個查詢有5個分支，分支所申請的線程就是5*16共80個！如圖5-1：
圖5-1
并行死鎖
并行執行提升查詢響應時間，提高用戶體驗已經被我們所熟知了，但正如我一直強調的，任何事物均有利弊，我們要做的重點是權衡 。并行死鎖在并行執行中也會偶爾出現，官方給出的解釋是SQL Server的“BUG”，你只需將查詢的MAXDOP調整為1，死鎖就會自動消失，但有時我們還應追溯其本質 。這里用一個實例為大家說明下并行死鎖的原因，以便我們更好的利用并行 。
生成測試數據
接下來我們執行如下語句，取30000下最大偶數，此時我將執行并行數maxdop隨意調整為奇數，3，5，7我的執行都可以迅速返回結果 。
但當我將并行數調整為偶數時，執行時間居然長達數秒…打開trace profiler跟蹤dead lock chain我們發現，當并行數為偶數時出現了死鎖 。
注：我們用Trace profiler捕捉死鎖
如圖6-1，6-2，6-3：
圖6-1
圖6-2
圖6-3
有的同學可能覺得蹊蹺，發生了什么我們具體分析下并行死鎖的相應執行計劃 。
分析：
訪問基表數據時用的是聚集索引掃描，但掃描方式是backward，而SQL server中只有forward scan可以并行掃描，backward只能串行掃描因此在做exchange向各個threads分發數據時（distribute streams）采用roundrobin輪詢分發數據，這勢必造成奇偶數據按threads分開流向下一個過濾操作符在Filter時將奇數的數據過濾，而相應的threads也就沒有了數據所以在最后exchange匯總數據時(gather streams)有的threads沒有數據，因而造成死鎖 。
（注：thread 0為主線程，不參與并行分支工作）
分析如圖6-4：
圖6-4
而反觀并行采用奇數并行數，這時當分發數據時就不會造成某個thread所持有的數據只是奇數或是偶數，也就不會造成后來的情形，死鎖也就不會出現 。如圖6-4感興趣的同學可以做實驗調整并行數并閱讀相應的執行計劃 。
圖6-5
至此我們應該可以明白了 。
萬事皆有因果，一個簡單的BUG可以做為回應，但深究可能窺其本質，并且很有意思 。技術人需有這種精神 。
優化實踐
最后咱們再通過一個簡單的實例說下優化實踐 。
日常的OLTP環境中，有時會涉及到一些統計方面的SQL語句，這些語句可能消耗巨大，進而影響整體運行環境，這里我為大家介紹如何利用SQL Server中的“類MapReduce”方式，在特定的統計情形中不犧牲響應速度的情形下減少資源消耗 。
我們可能經常會利用開窗函數對巨大的數據集進行分組統計排序 。比如下面的例子：
腳本環境
當我們針對bigProduct表的productid分組，并按照bigTransactionHistory的actualcost及quantity分別排序取結果集語句如下：
執行此語句并輸出實際執行計劃如圖7-1：
圖7-1
可以看出我的這條語句由于對大量結果集進行排序，致使消耗了365MB的內存，并且由于分別對actualcost，quantity排序使得在進行第二個排序時內存不足并溢出，排序的操作只能在tempdb中進行 。
Sort由于是典型的計算密集型運算符，此查詢在我的機器上執行時間為5s 。
大量的內存被個別查詢長時間獨占，使得Buffer Pool的穩定性下降，進而可能影響整體吞吐 。
關于SQL Server的Sort限于篇幅這里就不細說了 。
在介紹“類MapReduce”之前，我想先接著上面Sort溢出的現象給大家簡單介紹下通過Query hints 來影響優化器的資源分配 。
通過查詢可以看出由于我加了Query Hint，改變了優化器的資源評估標準，使得優化器認為productid本身需要資源從1001 and 3001分配變為了1001 and 5001分配，內存申請由365MB變為了685MB，接近一倍的增長，避免了溢出 。并且執行時間也由5S變為了2S，提升了用戶體驗 。
如圖7-2：
圖7-2
可以看到溢出與不溢出在查詢消耗時間上差別很大，但這樣就是好了嗎?其實未必，畢竟即便在非溢出的情形中將近700MB的內存近2s內被這個查詢占用，這在高并發的OLTP環境中是傷全局的 。那更理想的解決方式呢?
在并行執行計劃中是多個線程(CPU核)協同工作，這里面的Sort面對大量數據結果集時即便多核同時進行，在復雜的預算面前也是有些力不從心 。在分布式的思想中，講究分而治之，我們只要將大的結果集化為多個小的部分并多核同時進行排序，這樣就達到了分而治之的效果 。也就是前面說的“MapReduce” 。
幸好，在SQL Server實現并行運算的運算符”nestloop”與之相似 。上篇中并行nested loop join的原理中已經提到 。
并行Nest loop Join實現方式
在并行循環嵌套中，外表數據Scan，seek多線程(threads)同時進行(Map)，而內表的在每個thread上串行執行(Reduce) 。
優點：
可以減少執行過程中各線程數據流的數據交換顯著的減少內存需求
上述查詢我用如下的方式實現：
執行中輸出實際執行計劃可以看出，此計劃中消耗的內存15MB，和上述的執行計劃相比有指數級的下降，同時執行時間為不到2s，保證執行時間的同時明顯降低了資源消耗，從而避免了實例級的影響 。
已經很美好了：)
如圖7-3：
圖7-3
到這里其實我們已經達到了我們想要的效果，但還可以更好嗎?我們還需要多了解些 。
上面我講到了并行nest loops的優點，少資源占用，少數據交換 。但就像在我以前的文章中說的那樣：”任何術都是有缺陷的”，前文已經提到了并行中很可能造成數據的傾斜，如上圖7-3中藍線中標注的外表seek，實際是只在一個thread中完成的 。優化器為我們加了數據交換，使得外部的數據在多個threads下分布均衡與內表匹配提升效率，但優化器可不會每次都如此“好心”(智能) 。
其實在并行seek，scan中由于實現方式在05到08的過程變化很大(前文提到過)，使得操作更需注意，這里我們直接上新的方案 。
通過查詢時輸出執行計劃 如圖7-4所示 。
我們可以看到通過將外表數據放入臨時表中，使得內存消耗進一步降低，而數據較為平均的分布到多個threads中，你可能看到其中不少threads是沒有數據的，其實有時需要我們根據查詢管控并行度的 。而在執行時間上有可能得到進一步的改善！
圖7-4
至此，還有沒有更好的方案呢，當然有，優化器就是讓我們玩的！
我們可以通過臨時表緩解數據分布不均的問題，但臨時表創建導入也是成本啊，我們也可以用其他方式誘導優化器讓數據分布徹底均勻，還記得前面說的Round Robin嗎？這里我用特定的寫法引導他的數據exchange.徹底平均分配，
如圖7-5：
圖7-5
至此這個優化更合理的解決了面臨的問題！
我們的并行原理及實踐也到此為止吧 。
說點題外話，不少朋友認為SQL Server是小兒科，沒內容，技術含量不高，而且在國內的互聯網公司中又顯得格格不入 。這種想法真心Too Naive 。這里我可以告訴大家，SQL Server，乃至關系型數據庫的水很深 。如果你是相關的從業者，全身心地投入進來吧，其實很好玩 。