SQL Server大負(fù)載的生產(chǎn)環(huán)境下的性能優(yōu)化：初識(shí)元數(shù)據(jù)優(yōu)化_圖文

1、SQL Server大負(fù)載的生產(chǎn)環(huán)境下的性能優(yōu)化：初識(shí)元數(shù)據(jù)優(yōu)化相信朋友對(duì)SQL Server性能調(diào)優(yōu)相關(guān)的知識(shí)或多或少都有一些了解。雖然說現(xiàn)在NOSQL相關(guān)的技術(shù)非常的火熱，但是RMDB(關(guān)系型數(shù)據(jù)庫與NOSQL是并存的，并且適用在各種的項(xiàng)目中。在一般的企業(yè)級(jí)開發(fā)中，主要還是RMDB占據(jù)主導(dǎo)地位。并且在互聯(lián)網(wǎng)項(xiàng)目中，也不是摒棄了RMDB，例如MySQL就在很多的互聯(lián)網(wǎng)應(yīng)用中發(fā)揮著作用。所以，對(duì)數(shù)據(jù)庫的調(diào)優(yōu)是個(gè)值得深入學(xué)習(xí)的課題。本系列文章，主要講述與SQL Server相關(guān)的調(diào)優(yōu)知識(shí)，希望能夠?yàn)榕笥褌儙硪恍椭?本篇提綱如下：傳統(tǒng)SQL Server調(diào)優(yōu)方式的比較什么是DMVDMV簡單示

2、例DMV可以解決哪些問題傳統(tǒng)SQL Server調(diào)優(yōu)方式的比較我們平時(shí)可以采用很多工具對(duì)SQL Server的性能進(jìn)行診斷分析與調(diào)優(yōu)：性能計(jì)數(shù)器，SQL Server Profiler和Database Engine Tuning Advisor(數(shù)據(jù)庫引擎優(yōu)化顧問，簡稱DTA。下面我們就來對(duì)每一個(gè)簡單的說明一下，并且講述各自的優(yōu)缺點(diǎn)。性能計(jì)數(shù)器對(duì)于使用Windows操作系統(tǒng)的開發(fā)朋友，對(duì)性能計(jì)數(shù)器已經(jīng)不陌生了，最簡單的方式就是運(yùn)行”perfmon”，就可以打開性能監(jiān)視器的窗口，然后添加對(duì)應(yīng)的計(jì)數(shù)器，進(jìn)行監(jiān)控。如下圖所示：性能計(jì)數(shù)器每隔一段時(shí)間就回去收集相關(guān)的數(shù)據(jù)，并且我們還可以把這些數(shù)據(jù)保存

3、起來，便于以后的分析。優(yōu)點(diǎn)：使用方便，并且數(shù)據(jù)收集的比較全面。因?yàn)檫@些操作系統(tǒng)是集成的，不需要額外的花費(fèi)，就可以直接使用。缺點(diǎn)：數(shù)據(jù)不夠準(zhǔn)確，分析數(shù)據(jù)成本很高，并且對(duì)系統(tǒng)的性能產(chǎn)生影響。因?yàn)樾阅苡?jì)數(shù)器在收集數(shù)據(jù)的時(shí)候，要定時(shí)的去抓取系統(tǒng)相關(guān)的數(shù)據(jù)，這樣，會(huì)對(duì)性能造成影響，如果抓取的時(shí)間間隔越短，那么對(duì)系統(tǒng)的影響就越大。另外，對(duì)于收集到的數(shù)據(jù)，也需要有經(jīng)驗(yàn)豐富的人去分析數(shù)據(jù)，并且如果要準(zhǔn)確的分析出結(jié)果，需要收集大量的數(shù)據(jù)，人力和時(shí)間的成本也高。SQL Server Profiler每次談到SQL Server調(diào)優(yōu)，勢必要講到SQL Server Profiler（為了后續(xù)的講述方便，我們將會(huì)把它

4、簡稱為Profiler）。使用Profiler能夠捕獲一段時(shí)間內(nèi)SQL執(zhí)行的每個(gè)查詢的記錄。當(dāng)數(shù)據(jù)庫服務(wù)器上有大量很少運(yùn)行的查詢時(shí)，或者有特別的用戶查詢運(yùn)行的時(shí)候，這個(gè)工具很有用。使用Profiler還能捕獲到指定時(shí)間段內(nèi)的工作負(fù)荷，然后可以在恢復(fù)的數(shù)據(jù)庫系統(tǒng)中進(jìn)行重現(xiàn)。下面就是一個(gè)使用Profiler的界面：在使用Profiler的時(shí)候，在哪里啟動(dòng)Profiler以及將跟蹤的數(shù)據(jù)保存在何處，是一個(gè)特別需要考慮的問題。下面，我們就用來對(duì)比地看看各種不同的情況。啟動(dòng)Profiler的位置跟蹤文件說明在正在監(jiān)視的數(shù)據(jù)庫服務(wù)器上啟動(dòng)Profiler的跟蹤將跟蹤的數(shù)據(jù)文件保存到服務(wù)器的文件系統(tǒng)或共享目

5、錄中優(yōu)點(diǎn)：這種方式適用于非生產(chǎn)環(huán)境和低負(fù)載服務(wù)器上的開發(fā)和對(duì)數(shù)據(jù)庫應(yīng)用進(jìn)行性能診斷和跟蹤。缺點(diǎn)：加大了服務(wù)器的I/O讀寫操作在本機(jī)開啟Profiler，連接遠(yuǎn)程的數(shù)據(jù)庫服務(wù)器將跟蹤的數(shù)據(jù)文件保存在本地優(yōu)點(diǎn)：減小了數(shù)據(jù)庫服務(wù)器的I/O操作。缺點(diǎn)：加大了網(wǎng)絡(luò)傳輸?shù)膲毫?，占用?shù)據(jù)庫服務(wù)器的網(wǎng)絡(luò)資源和CPU資源。因?yàn)樾枰獙⒋罅康母檾?shù)據(jù)傳送到本機(jī)，這樣占用了網(wǎng)絡(luò)資源；而數(shù)據(jù)在傳輸?shù)臅r(shí)候，需要CPU將之序列化，加大了CPU的操作。數(shù)據(jù)庫引擎優(yōu)化顧問（DTA）DTA一般需要和Sql Server Profiler結(jié)合在一起使用。DTA主要是對(duì)Profiler中收集到的數(shù)據(jù)進(jìn)行純數(shù)據(jù)的綜合分析，所以它分析結(jié)

6、果的準(zhǔn)確性非常依賴于Profiler收集的數(shù)據(jù)量的多少。Profiler收集的數(shù)據(jù)越多，那么DTA分析的就越準(zhǔn)確，但是這樣也對(duì)數(shù)據(jù)庫服務(wù)器的壓力越大，反之。所以，一般不建議在大負(fù)載或者生產(chǎn)環(huán)境下的數(shù)據(jù)庫服務(wù)器上面采用。如果有需要在大負(fù)載，或者生產(chǎn)環(huán)境下對(duì)數(shù)據(jù)庫的性能進(jìn)行分析與調(diào)優(yōu)，那如何處理？這就是我們本次系列文章要討論的話題。什么是DMV使用過SQL Server的朋友，對(duì)DMV（Dynamic Management Views，動(dòng)態(tài)管理視圖）或多或少都有一些了解或者耳聞。其實(shí)DMV就是SQL Server內(nèi)核的元數(shù)據(jù)，通過對(duì)內(nèi)部的元數(shù)據(jù)的分析，我們快速而準(zhǔn)確獲取很多與SQL Server內(nèi)

7、部相關(guān)的信息，從而進(jìn)行性能分析。當(dāng)查詢在SQL Server中運(yùn)行時(shí)，SQL Server會(huì)自動(dòng)的將此次活動(dòng)的相關(guān)信息記錄下來，并且保存在內(nèi)存之中，這些活動(dòng)信息，就稱之為：DMV。不同類型的DMV信息，有不同的用途，例如，可以相關(guān)的DMV來對(duì)性能進(jìn)行診斷，從而提升性能，或?qū)?shù)據(jù)庫的運(yùn)行進(jìn)行監(jiān)控，或解決故障等。DMV是以SQL Server實(shí)例為級(jí)別進(jìn)行保存的。也就說，如果在服務(wù)器上面，安裝了一個(gè)SQL Server，那么此時(shí)這個(gè)SQL Server就是一個(gè)實(shí)例，那么這個(gè)實(shí)例里面的所有的數(shù)據(jù)庫的DMV都是保存在相同的內(nèi)存中。當(dāng)然，我們在使用的時(shí)候，可以根據(jù)需要只提取更低級(jí)別的DMV，例如提取某個(gè)

8、數(shù)據(jù)庫的DMV，某個(gè)表的DMV，甚至是某個(gè)查詢的DMV。因?yàn)镈MV信息是保存在內(nèi)存中的，我們不需要額外的操作，只需要將這些信息取出來，按照我們的要求進(jìn)行運(yùn)算，統(tǒng)計(jì)，分析就夠了，獲取信息的數(shù)據(jù)非常快，并且不會(huì)對(duì)服務(wù)器產(chǎn)生壓力。另外，因?yàn)镈MV是SQL Server本身保存的，并且已經(jīng)做了統(tǒng)計(jì)的信息，所以，數(shù)據(jù)更加的接近于數(shù)據(jù)庫本身的狀態(tài)。SQL Server運(yùn)行的時(shí)候越長，DMV中保存的信息就越多（當(dāng)然，DMV非常小，不會(huì)對(duì)內(nèi)存造成壓力），利用DMV分析就越準(zhǔn)確。這一點(diǎn)和之前的Profiler和DTA是完全不一樣的。唯一的一個(gè)問題就是：每次SQL Server服務(wù)重啟，這些保存在內(nèi)存中的DMV信

9、息就沒有了，又是從頭開始，慢慢的保存。當(dāng)然，對(duì)于這個(gè)問題，我們有很多的解決方案，例如，我們可以定期的將DMV的信息導(dǎo)出，保存在磁盤上。DMV包含了的信息有很多：索引相關(guān)的，查詢執(zhí)行相關(guān)的，還有SQL Server OS相關(guān)的，Common Language Runtime（CLR）相關(guān)的，事務(wù)相關(guān)，安全相關(guān)的，資源管理相關(guān)的，數(shù)據(jù)備份相關(guān)的，I/O相關(guān)，全文查找相關(guān)，數(shù)據(jù)庫鏡像相關(guān)的，等等信息。所以，我們完全可以使用已經(jīng)保存在DMV中的信息來進(jìn)行我們的分析。因?yàn)镾QL Server內(nèi)部的DMV很多，我們本次系類的文章注重在性能分析與調(diào)優(yōu)上，所以，我們主要關(guān)注以下幾類DMV：索引相關(guān)，執(zhí)行相關(guān)，

10、SQL Server OS相關(guān)，CLR相關(guān)，事務(wù)相關(guān)，I/O相關(guān)，數(shù)據(jù)庫相關(guān)。下面，我們分析一個(gè)查詢的運(yùn)行，看看在這個(gè)過程中SQL Server都記錄了哪些信息（或者說，DMV中保存了什么信息）：查詢的執(zhí)行計(jì)劃（即描述了一個(gè)查詢是如何被執(zhí)行的）什么索引被使用什么索引本來應(yīng)該被用到，但是又沒有使用。（因?yàn)榇藭r(shí)存在缺失索引的性能問題）I/O的狀態(tài)（包含邏輯I/O操作和物理的I/O操作）查詢執(zhí)行消耗的時(shí)間查詢等待其他資源消耗的時(shí)間查詢在等待什么資源通過分析這些信息，不僅僅可以使得我們更好的理解查詢的是如何工作的，并且還可以讓我們思考如何更加合理，高效的使用資源，提高性能。一般而言，在使用DMV的時(shí)候

11、，我們很多時(shí)候也需要將其與DMF（Dynamic Management Functions）一起使用。我們可以簡單的將DMF理解為SQL Server內(nèi)部的一系列函數(shù)。例如，通過分析sys.dm_exec_query_stats，可以知道查詢的相關(guān)信息，如果將sys.dm_exec_query_stats里面的sql_handle傳給sys.dm_exec_sql_text，那么，我們就可以知道查詢的語句的內(nèi)容。DMV簡單示例為了使得大家對(duì)DMV有更加深入的了解，我們首先來看看一個(gè)使用DMV來找出那些查詢運(yùn)行的最慢。（朋友們可能對(duì)這里提到的相關(guān)的DMV和DMF不太熟悉，沒關(guān)系，后續(xù)文章會(huì)介紹）

12、在SQL Server的查詢分析器中，運(yùn)行一下SQL 語句：此時(shí)，運(yùn)行的結(jié)果如下：在這個(gè)查詢中，我們主要是通過將sys.dm_exec_query_stats這個(gè)DMV與sys.dm_exec_sql_text和sys.dm_exec_query_plan這兩個(gè)DMF結(jié)合，通過分析查詢所消耗的時(shí)間，然后按照從高到低進(jìn)行排序，選出前20個(gè)進(jìn)行展示。從這個(gè)示例中，我們可以知道幾點(diǎn)：查詢DMV時(shí)，應(yīng)該盡可能的將對(duì)數(shù)據(jù)庫的影響降到最小。所以，我們在查詢的最上面，加上了：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED。因?yàn)槊看卧谶\(yùn)行查詢的時(shí)候，或多或少的

13、會(huì)對(duì)數(shù)據(jù)庫產(chǎn)生不同程度的鎖定，并且鎖定的級(jí)別各不一樣。通過上面的設(shè)置，就告訴SQL Server，接下來的執(zhí)行的查詢將鎖定的級(jí)別定為：Read Uncommitted。從而將影響減小到最小。每次進(jìn)行性能問題診斷的時(shí)候，首先要解決最嚴(yán)重的性能問題。所以，我們此處只是找出前20個(gè)運(yùn)行最慢的查詢語句。雖然原生的DMV信息提供了很多的信息，但是很多時(shí)候需要對(duì)DMV進(jìn)行復(fù)雜的統(tǒng)計(jì)分析。（這個(gè)成本比分析SQL Server Profiler收集到的數(shù)據(jù)小，也更加簡單，準(zhǔn)確。）DMV可以解決哪些問題看完了上面的簡單的例子之后，相信朋友們對(duì)DMV有了一個(gè)感性的認(rèn)識(shí)，下面，我們就來看看，利用DMV，我們可以解決

14、哪些問題。故障診斷診斷就是要識(shí)別出問題的所在。有很多的方式和工具可以幫助我們達(dá)到這個(gè)目的，但是，有了DMV，可能效率會(huì)更快：沒有什么比分析SQL Server內(nèi)部的元數(shù)據(jù)來的更快。很多時(shí)候，對(duì)問題的診斷也是性能調(diào)優(yōu)的第一步，搞清楚了問題，才好對(duì)癥下藥。利用DMV可以診斷出以下問題：最慢的查詢語句，常見的等待與阻塞，沒有用的索引，大量的I/O操作，利用率最低的執(zhí)行計(jì)劃。正如之前所說，我們可以在不同的級(jí)別上面分析問題，例如從整個(gè)服務(wù)器級(jí)別，數(shù)據(jù)庫級(jí)別，甚至是某個(gè)查詢。我們可以通過在獲取DMV信息時(shí)，設(shè)置獲取信息的條件來辦到。例如，在上一小節(jié)的示例中，就是獲取整個(gè)SQL Server中找出最慢的前20個(gè)查詢，如果需要，我們完全可以將條件縮小到某個(gè)數(shù)據(jù)庫。很多時(shí)候，在識(shí)別問題的時(shí)候，不是那么容易，僅僅通過一個(gè)DMV就搞定了的，需要和DMF結(jié)合。甚至要和其他的DMV一起結(jié)合分析（在后續(xù)文章中，我們會(huì)理解的更加深刻）。診斷出了問題，是一個(gè)方面，解決問題也尤為重要。性能調(diào)優(yōu)性能調(diào)優(yōu)主要是利用相關(guān)的技巧技術(shù)之前診斷中出現(xiàn)的問題，從而提升性能。我們后續(xù)會(huì)詳細(xì)講述，這里就不再贅述了。狀態(tài)監(jiān)控很多的DMV（特別是那些以 sys.dm_exec_開頭的）都反映了數(shù)據(jù)庫服務(wù)器執(zhí)行的狀態(tài)。通過查看這些DMV，我們可以清楚的知道數(shù)據(jù)庫服務(wù)器的現(xiàn)在的狀態(tài)和歷史的狀態(tài)（當(dāng)然，如何SQL Server服務(wù)被