SQLServer性能監(jiān)視和故障診斷.ppt

1、第 14 章 SQL Server 性能監(jiān)視和故障診斷,監(jiān)視 SQL Server 使用 “SQL Server Profiler ” 使用“系統(tǒng)監(jiān)視器” 查看當(dāng)前活動(dòng),監(jiān)視 SQL Server ：,管理任務(wù)： 小王所在的學(xué)校建立一個(gè)完整的數(shù)據(jù)庫(kù)系統(tǒng)，該系統(tǒng)用于學(xué)校的辦公和教學(xué)管理。小王負(fù)責(zé)維護(hù)數(shù)據(jù)庫(kù)，他根據(jù)數(shù)據(jù)庫(kù)特點(diǎn)制定了日常的監(jiān)視計(jì)劃，并使用各種工具對(duì) SQL Server 運(yùn)行進(jìn)行監(jiān)視。,任務(wù)演示： 小王使用一臺(tái)計(jì)算機(jī)通過(guò)“系統(tǒng)監(jiān)視器”管理控制臺(tái)遠(yuǎn)程連接到 SQL 服務(wù)器。監(jiān)視默認(rèn)的磁盤(pán)活動(dòng)、處理器使用率等；還監(jiān)視 SQL Server 對(duì)象 General Statistics 和

2、Buffer Manager 下的計(jì)數(shù)器（用戶的連接情況和數(shù)據(jù)頁(yè)的使用情況）。 通過(guò)使用，發(fā)覺(jué)網(wǎng)站查詢教室安排時(shí)慢，檢查發(fā)現(xiàn)教室安排查詢使用了spClassroomQ 存儲(chǔ)過(guò)程。用 SQL Server Profiler 建立了該存儲(chǔ)過(guò)程跟蹤。,跟蹤后發(fā)現(xiàn)問(wèn)題的源頭，在SSMS中打開(kāi)活動(dòng)監(jiān)視器，查看數(shù)據(jù)庫(kù)的鎖的信息，并對(duì)數(shù)據(jù)庫(kù)對(duì)象相互鎖定的問(wèn)題進(jìn)行解決。 最后檢查該表屬性。通過(guò)維護(hù)計(jì)劃重建了更新頻繁的索引。問(wèn)題全部解決。,重要概念： 在日常工作中，必須不斷的對(duì)數(shù)據(jù)庫(kù)進(jìn)行維護(hù)。維護(hù)的依據(jù)就是對(duì)系統(tǒng)狀態(tài)的監(jiān)視情況。通過(guò)監(jiān)視及跟蹤，找到產(chǎn)生問(wèn)題的對(duì)象，對(duì)系統(tǒng)進(jìn)行維護(hù)。這就是數(shù)據(jù)庫(kù)生命周期的一個(gè)重要組

3、成部分。 SQL Server Profiler ：可以跟蹤 SQL Server 的各種對(duì)象，進(jìn)一步查找故障來(lái)源； 系統(tǒng)監(jiān)視器：可以實(shí)時(shí)監(jiān)控系統(tǒng)的運(yùn)行狀態(tài)并提供警報(bào)信息； 活動(dòng)監(jiān)視器：可以監(jiān)視當(dāng)前的 SQL Server 中的進(jìn)程和鎖的情況。,使用 “SQL Server Profiler ” ：,SQL Server Profiler 是一種事件探查器，它具備跟蹤服務(wù)器和數(shù)據(jù)庫(kù)活動(dòng)的功能。可用來(lái)捕捉表、文件或 T-SQL 腳本中的數(shù)據(jù)，以供稍后分析。,監(jiān)視服務(wù)器和數(shù)據(jù)庫(kù)活動(dòng)的圖形化界面工具 根據(jù)可重用模板創(chuàng)建跟蹤 在跟蹤運(yùn)行時(shí)，觀察跟蹤結(jié)果 將跟蹤結(jié)果存儲(chǔ)在文件表中，以供進(jìn)一步分析 按需要

4、啟動(dòng)、停止、暫停和修改跟蹤結(jié)果 重現(xiàn)跟蹤結(jié)果,通過(guò)使用 SQL Server Profiler 跟蹤 SQL Server 活動(dòng) 使用 SQL Server Profiler 先確定跟蹤對(duì)象，再選擇標(biāo)準(zhǔn)，可以監(jiān)視以下活動(dòng)： 執(zhí)行性能較差的查詢； 登錄嘗試、故障、連接或連接斷開(kāi)； 語(yǔ)句級(jí)別使用的CPU； 死鎖的問(wèn)題； Tempdb 數(shù)據(jù)庫(kù)的性能等。,“SQL Server Profiler” 跟蹤選項(xiàng) ：,使用 SQL Server Profiler 創(chuàng)建一個(gè)跟蹤時(shí)，有很多選項(xiàng)用于定義將記錄的活動(dòng)和將存儲(chǔ)的登錄跟蹤活動(dòng)。,指定跟蹤模板 跟蹤事件是通過(guò)指定要監(jiān)視的事件類和要記錄的個(gè)別數(shù)據(jù)值（列）來(lái)

5、確定的。通過(guò)選擇跟蹤基于的模板，添加或刪除個(gè)別的事件類或列，并應(yīng)用篩選器限制基于指定標(biāo)準(zhǔn)收集的數(shù)據(jù)來(lái)確定跟蹤。,預(yù)定義模板：容易配置指定活動(dòng)類型所需的事件。 用戶定義模板,保存跟蹤數(shù)據(jù) 若稍后分析或重現(xiàn)捕捉到的數(shù)據(jù)，可先將捕捉到的事件數(shù)據(jù)保存到一個(gè)文件或 SQL Server 表中。 保存到表，可以設(shè)置的選項(xiàng)：表的位置和名稱；表中最多可存儲(chǔ)的行數(shù)。 保存到文件，可以設(shè)置的選項(xiàng)：文件的位置和名稱；最大文件的大??；文件滿時(shí)的跟蹤行為；服務(wù)器或“SQL Server Profiler ”進(jìn)行的跟蹤處理。 通過(guò)保存跟蹤，可以： 使用一個(gè)跟蹤文件或跟蹤表來(lái)創(chuàng)建一個(gè)工作負(fù)載，它被用作數(shù)據(jù)庫(kù)引擎優(yōu)化顧問(wèn)的輸

6、入； 使用一個(gè)跟蹤文件捕捉事件，并將跟蹤文件發(fā)送到提供支持的程序中進(jìn)行分析；,指定跟蹤停止時(shí)間 選擇設(shè)置一個(gè)跟蹤的時(shí)間，允許自動(dòng)跟蹤并在達(dá)到指定日期和時(shí)間之前保持跟蹤運(yùn)行。,使用 SQL Server 中的查詢處理工具，訪問(wèn)數(shù)據(jù)或查看 SQL Server Profiler 中的數(shù)據(jù)。,跟蹤類別、事件和列：,跟蹤類別 在 SQL Server Profiler 中，每個(gè)類別都是一組相關(guān)的事件類。事件類由可以跟蹤的事件的類型組成，事件類包含所有可以被事件報(bào)告的數(shù)據(jù)列。 默認(rèn)的跟蹤類別有： 安全審核； 會(huì)話； 存儲(chǔ)過(guò)程； TSQL。,事件 事件是指 SQL Server 數(shù)據(jù)庫(kù)引擎實(shí)例中操作的一次

7、執(zhí)行。事件由事件屬性進(jìn)一步定義。這些屬性在數(shù)據(jù)列中列出。 注意： 若要查看所有事件，選中跟蹤屬性對(duì)話框中事件選擇選項(xiàng)卡上的所有事件復(fù)選框。,列 數(shù)據(jù)列包含事件的屬性。 SQL Server Profiler 使用跟蹤輸出的數(shù)據(jù)列，描述跟蹤運(yùn)行時(shí)捕捉到的事件。 可以通過(guò)使用列篩選器管理列來(lái)控制收集數(shù)據(jù)的類型。,使用“系統(tǒng)監(jiān)視器” ：,場(chǎng)景： 你是一名企業(yè)數(shù)據(jù)庫(kù)主管，你的團(tuán)隊(duì)要維護(hù)該企業(yè)的數(shù)據(jù)庫(kù)24小時(shí)不間斷正常運(yùn)行。 為了達(dá)到這個(gè)目的，需要對(duì) SQL Server 進(jìn)行全面監(jiān)控以保證服務(wù)器的所有子系統(tǒng)都處于協(xié)調(diào)工作和未超載的狀態(tài)之下。 你覺(jué)得使用 Windows 系統(tǒng)監(jiān)視器可以實(shí)現(xiàn)這個(gè)目標(biāo)；通過(guò)

8、它還可設(shè)置警報(bào)，在系統(tǒng)超載時(shí)，通知管理員團(tuán)隊(duì)。,系統(tǒng)監(jiān)視器簡(jiǎn)介 它是一個(gè)MMC（Microsoft Management Console）管理單元，可使用該管理單元查看系統(tǒng)性能度量指標(biāo)。 你可以啟動(dòng)控制面板中管理工具程序組下的性能應(yīng)用程序，來(lái)查看系統(tǒng)監(jiān)視器。 對(duì)象：是計(jì)算機(jī)系統(tǒng)的主要組件或子系統(tǒng)。對(duì)象可以是硬件、軟件或應(yīng)用程序。 計(jì)數(shù)器：收集對(duì)象各方面的數(shù)據(jù)。計(jì)數(shù)器內(nèi)置于操作系統(tǒng)中，并持續(xù)讀取性能數(shù)據(jù)。若一個(gè)對(duì)象類型有多個(gè)實(shí)例，計(jì)數(shù)器會(huì)跟蹤每個(gè)實(shí)例或所有實(shí)例的統(tǒng)計(jì)信息。 實(shí)例：同一個(gè)對(duì)象類型可以有多個(gè)實(shí)例。查看系統(tǒng)監(jiān)視器性能信息時(shí)，可以查看某一實(shí)例值或該對(duì)象所有實(shí)例的綜合值。,在系統(tǒng)監(jiān)視器中，

9、可指定要顯示的計(jì)數(shù)器，并可以直線圖、直方圖或報(bào)告的形式顯現(xiàn)。 當(dāng)性能數(shù)據(jù)保存為 HTML 文件時(shí)，可在瀏覽器中查看并打印直線圖、直方圖或報(bào)告。報(bào)告還可導(dǎo)入電子表格中，進(jìn)行更詳細(xì)分析。,性能日志和警報(bào) 在系統(tǒng)監(jiān)視器中，使用該管理單元將性能信息捕捉到日志文件中，稍后查看。 計(jì)數(shù)器日志將記錄關(guān)于硬件資源和基于性能的系統(tǒng)服務(wù)的歷史信息；跟蹤日志收集事件跟蹤信息，根據(jù)跟蹤信息測(cè)量與事件相關(guān)的性能統(tǒng)計(jì)。 日志文件將提供用于排除故障和進(jìn)行歸納的信息。在為當(dāng)前活動(dòng)的圖表、警報(bào)和報(bào)告提供即時(shí)反饋時(shí)，還可長(zhǎng)時(shí)間跟蹤計(jì)數(shù)器。 不可能時(shí)時(shí)監(jiān)視某個(gè)特定計(jì)數(shù)器，又希望再超出或低于指定值得到通知，可配置一個(gè)在計(jì)數(shù)器達(dá)到特定

10、極限值時(shí)就被激活的警報(bào)。,SQL Server-specific 對(duì)象允許你監(jiān)視 SQL Server 的每個(gè)實(shí)例 SQL Server-specific 對(duì)象包括:,SQL Server 性能對(duì)象 SQL Server 為系統(tǒng)監(jiān)視器提供了大量的對(duì)象和計(jì)數(shù)器。對(duì)象可以是任何一個(gè) SQL Server 資源。每個(gè)對(duì)象包含一個(gè)或多個(gè)計(jì)數(shù)器，這些計(jì)數(shù)器可確定要監(jiān)視的對(duì)象的多個(gè)方面。,監(jiān)視SQL Server 的注意事項(xiàng) 通過(guò)監(jiān)視 SQL Server 實(shí)例，需要對(duì)系統(tǒng)主要方面進(jìn)行分析。通過(guò)監(jiān)視，可找到并消除性能瓶頸，進(jìn)一步確定數(shù)據(jù)庫(kù)、T-SQL 查詢或客戶端應(yīng)用程序的設(shè)計(jì)問(wèn)題。,監(jiān)視器的關(guān)鍵區(qū)域 磁

11、盤(pán)系統(tǒng)： SQL Server 使用 Windows 操作系統(tǒng)輸入/輸出（I/O）調(diào)用來(lái)實(shí)現(xiàn)對(duì)磁盤(pán)子系統(tǒng)的讀寫(xiě)操作。 SQL Server 管理執(zhí)行磁盤(pán)I/O的時(shí)機(jī)和方式； Windows 操作系統(tǒng)將執(zhí)行底層 I/O 操作。在系統(tǒng)中，磁盤(pán) I/O 頻繁將導(dǎo)致瓶頸發(fā)生。,內(nèi)存： 對(duì) SQL Server 進(jìn)行周期性的監(jiān)視，可確保內(nèi)存的使用情況不出現(xiàn)異常。首先要確定不存在消耗過(guò)多內(nèi)存的進(jìn)程，同時(shí)也不存在內(nèi)存不夠的情況。 CPU： 對(duì) SQL Server 進(jìn)行周期性的監(jiān)視，可確定CPU 使用率是否在正常范圍內(nèi)。持續(xù)的高CPU 使用率可能表示需要升級(jí)CPU 或添加更多的處理器。,查看當(dāng)前活動(dòng) ：,提

12、問(wèn)： 作為DBA，希望獲得 SQL Server 的實(shí)時(shí)狀態(tài)。有什么好的辦法可以查看 SQL Server 中當(dāng)前的所有進(jìn)程以及進(jìn)程的內(nèi)容呢？ 可通過(guò)使用SSMS 的活動(dòng)監(jiān)視器組件或動(dòng)態(tài)管理視圖來(lái)監(jiān)視SQL Server 中當(dāng)前活動(dòng)，以獲取有關(guān)到數(shù)據(jù)庫(kù)引擎的用戶連接及其所保存的鎖的信息。,監(jiān)視當(dāng)前活動(dòng)可幫助排除數(shù)據(jù)庫(kù)鎖定的問(wèn)題，終止死鎖或其他沒(méi)響應(yīng)的進(jìn)程。,活動(dòng)監(jiān)視器 活動(dòng)監(jiān)視器是SSMS 中的圖形化工具，顯示有關(guān)當(dāng)前用戶進(jìn)程和鎖的信息。（在管理文件夾中可看到） 在活動(dòng)監(jiān)視器的選擇頁(yè)上可查看到三個(gè)信息：進(jìn)程信息、按進(jìn)程分類的鎖和按對(duì)象分類的鎖。,進(jìn)程信息： 在進(jìn)程信息頁(yè)面上?；顒?dòng)監(jiān)視器將顯示一

13、個(gè)列表，它列出了系統(tǒng)中所有的連接進(jìn)程。 數(shù)據(jù)庫(kù)管理員可根據(jù)該頁(yè)面來(lái)確定哪些進(jìn)程正處于活動(dòng)狀態(tài)，若需要還可終止某一進(jìn)程。,按進(jìn)程分類的鎖： 在按進(jìn)程分類的鎖頁(yè)面上，活動(dòng)監(jiān)視器顯示所選進(jìn)程保存的所有鎖和鎖定請(qǐng)求。,數(shù)據(jù)庫(kù)管理員可根據(jù)該頁(yè)面確定哪些進(jìn)程由于過(guò)多的鎖定和死鎖而產(chǎn)生了相互阻止。,按對(duì)象分類的鎖： 在按對(duì)象分類的鎖頁(yè)面上，活動(dòng)監(jiān)視器顯示出所選數(shù)據(jù)庫(kù)對(duì)象上保存的所有鎖和鎖請(qǐng)求。 數(shù)據(jù)庫(kù)管理員可使用該頁(yè)面排除與特定表或其他數(shù)據(jù)庫(kù)對(duì)象產(chǎn)生沖突的鎖定問(wèn)題。,動(dòng)態(tài)管理對(duì)象 可以使用動(dòng)態(tài)管理視圖和動(dòng)態(tài)管理函數(shù)去查詢 SQL Server 2005中的動(dòng)態(tài)元數(shù)據(jù)。這些元數(shù)據(jù)提供了有關(guān) SQL Server 當(dāng)前狀態(tài)的信息。,進(jìn)程信息： 動(dòng)態(tài)管理視圖和目錄視圖都位于SSMS 的對(duì)象資源管理器中System View 文件夾下。,動(dòng)態(tài)管理視圖與靜態(tài)視圖一樣