建站
咨詢
售后
建站咨詢
新聞中心
使用場(chǎng)景
創(chuàng)新互聯(lián)建站是專業(yè)的葉集網(wǎng)站建設(shè)公司，葉集接單;提供網(wǎng)站建設(shè)、成都網(wǎng)站制作,網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行葉集網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!
記得某次給一家公司調(diào)優(yōu)的時(shí)候，負(fù)責(zé)人發(fā)給我一堆業(yè)務(wù)的T-SQL腳本，我面對(duì)海量腳本還是從容，雖然不了解內(nèi)部復(fù)雜的業(yè)務(wù)，但是我們得專注問題的關(guān)鍵 “慢”，我們根據(jù)查詢的“慢”把他們篩選出來，一一調(diào)式優(yōu)化，不就迅速解決問題嗎？三天后，負(fù)責(zé)人含淚握著我的手，哥們辛苦了，查詢響應(yīng)得到了質(zhì)的改善。
跟蹤提供者
SQL Server 為我們兩者提供跟蹤的方式：一種是一個(gè)物理文件（可保存在本機(jī)或者UNC網(wǎng)絡(luò)路徑），一種是行集。對(duì)于后者大家應(yīng)該比較熟悉
這個(gè)工具在 SSMS 的 工具 –> SQL Profile
詳細(xì)的我暫時(shí)不介紹，先說說兩者的區(qū)別和類同點(diǎn) DIFFAndSame(行集,文件提供者)。
兩者都是用類似Buffer來保存當(dāng)前的事件數(shù)據(jù)，很明顯是為了減少IO的壓力，這樣可以不阻塞和盡量不遺漏 事件數(shù)據(jù)，當(dāng)Buffer 到達(dá)一定量時(shí)候可能才會(huì)Flush到磁盤或者發(fā)送到網(wǎng)絡(luò)的終端（客戶端）顯示監(jiān)控行集。
物理文件保存監(jiān)控結(jié)果的方式的重要保證是不能遺漏任何事件，一旦IO降速的時(shí)候，可能會(huì)影響到整個(gè)T-SQL的執(zhí)行情況。
- SELECT * FROM sys.dm_os_wait_stats
- WHERE wait_type IN ('SQLTRACE_LOCK','IO_COMPLETION');
我使用這個(gè)語句來監(jiān)控TRACE 和IO 完成對(duì)我當(dāng)前機(jī)器的影響，我的某個(gè)客戶的IO情況：
- wait_type
- waiting_tasks_count
- wait_time_ms
- max_wait_time_ms
- signal_wait_time_ms
- IO_COMPLETION
- 66030898
- 24377499
- 3634
- 418960
- SQLTRACE_LOCK
- 12007
- 175943
- 1001
- 1281
因?yàn)槲疫M(jìn)行了大量的過濾，因此這個(gè)值還是能夠接受的，影響不是特別大。
行結(jié)果集的方式，其實(shí)也是我們最熟悉的，就是使用SQL Server Profile監(jiān)控GUI 直接展現(xiàn)給我們看到的。但是，我是非常不建議使用的，首先如果Buffer滿了，它有一定的延遲，可能會(huì)拋棄事件已清空緩存區(qū)繼續(xù)接受事件，而事件沒有發(fā)送到Client，也沒有寫到物理文件，自然就丟失了。比如，SQL Server Profile 在DB服務(wù)器進(jìn)行監(jiān)控，因?yàn)楦哓?fù)載的機(jī)器再用來展示，很有可能就會(huì)丟失事件，另外物理文件方式，其實(shí)是接受一個(gè)足夠大的Buffer，進(jìn)行的大塊寫操作，性能是優(yōu)于行集的。
（行集）
保密性原則
SQL Server的安全特性會(huì)自動(dòng)過濾 包含隱私的數(shù)據(jù)，比如密碼。我在我的SSMS中執(zhí)行了如下的語句：
- EXEC sp_password 'pp','pp1','sa';
這是修改sa帳號(hào)密碼的系統(tǒng)sp，我打開了SQL Server Profile –> 選擇了T-SQL 監(jiān)控模版
然后執(zhí)行上面的存儲(chǔ)過程，監(jiān)控結(jié)果：
監(jiān)控結(jié)果：--*sp_password----------------------------
SQL Server Profile
使用SQL Server Profile GUI工具還是很多優(yōu)勢(shì)，首先是減少了我們監(jiān)控的復(fù)雜性，可以款速的建立監(jiān)控，在跟蹤屬性中，可以可以選擇MSSQL為我們提供的模版，包括常用的T-SQL、T-SQL Duration、T-SQL Locks模版分別監(jiān)控當(dāng)前DB運(yùn)行的所有查詢，所有查詢的耗時(shí)、所有的鎖定狀態(tài)。
在跟蹤屬性 –> 選擇事件選擇 我們可以選擇自己需要的事件，所有的事件在MSDN 都有定義->單擊列篩選器 可以自定義過濾，排序噪點(diǎn)干擾因素
（我隨便選擇了一個(gè)耗時(shí) = 500 微妙的過濾條件）
其他的模版大家可以自己看看MSDN 手冊(cè)，自己嘗試一下：SQL Server 2008 R2 本機(jī) MSDN
服務(wù)器端跟蹤和物理方式收集
SQL Server Profile 只是對(duì)一些存儲(chǔ)過程的封裝，我更傾向于，自己定義常用的腳本，將監(jiān)控結(jié)果保存在本機(jī)，用來大量的分析和存檔。
當(dāng)然涉及4個(gè)存儲(chǔ)過程，雖然設(shè)置過濾的腳本非常麻煩，但是SQL Server Profile 可以利用 文件->導(dǎo)出 可以導(dǎo)出監(jiān)控腳本意味著，我們不需要編寫復(fù)雜的T-SQL 腳本，不過還是建議大家熟悉這幾個(gè)存儲(chǔ)過程：
sp_trace_create 定義跟蹤 ，創(chuàng)建的跟蹤會(huì)在sys.traces查詢的到。
s_trace_setevent 設(shè)置監(jiān)控事件
sp_trace_setfilter 設(shè)置過濾
sp_trace_setstatus 設(shè)置跟蹤的狀態(tài) 常用的是 sp_trace_setstatus @traceid,0 停止功能 、sp_trace_setstatus @traceid,2 移除跟蹤，這將導(dǎo)致sys.traces最終查詢不到該跟蹤
其實(shí)整個(gè)跟蹤還是比較簡(jiǎn)單的。我這里有一個(gè)常用的腳本：
用來 監(jiān)控超過指定秒數(shù) 和 數(shù)據(jù)庫 的 批處理和存儲(chǔ)過程 語句（超過5MB的文件，會(huì)執(zhí)行ROLLOVER，根據(jù)文件名在后面添加類似_1,_2.trc的跟蹤結(jié)果）：
- CREATE PROC [dbo].[sp_trace_sql_durtion]
- @DatabaseName nvarchar(128),
- @Seconds bigint,
- @FilePath nvarchar(260)
- AS
- BEGIN
- DECLARE @rc int,@TraceID int,@MaxFileSize bigint;
- SET @MaxFileSize = 5;
- EXEC sp_trace_create @TraceID OUTPUT,2,@FilePath,@MaxFileSize,NULL;
- IF @rc != 0
- RETURN;
- DECLARE @On bit;
- SET @On = 1;
- EXEC sp_trace_setevent @TraceID,10,35,@On;
- EXEC sp_trace_setevent @TraceID,10,1,@On;
- EXEC sp_trace_setevent @TraceID,10,13,@On;
- EXEC sp_trace_setevent @TraceID,41,35,@On;
- EXEC sp_trace_setevent @TraceID,41,1,@On;
- EXEC sp_trace_setevent @TraceID,41,13,@On;
- SET @Seconds = @Seconds * 1000000;
- EXEC sp_trace_setfilter @TraceID,13,0,4,@Seconds;
- IF @DatabaseName IS NOT NULL
- EXEC sp_trace_setfilter @TraceID,35,0,0,@DatabaseName
- EXEC sp_trace_setstatus @TraceID,1
- SELECT TraceID = @TraceID;
- END
參數(shù)非常的明了，數(shù)據(jù)庫名稱、執(zhí)行事件超過多少秒、保存的路徑。
當(dāng)我們運(yùn)行這個(gè)腳本一段事件以后，可以快速的發(fā)現(xiàn)大量耗時(shí)的T-SQL，我們可以通過
- SELECT * FROM fn_trace_gettable(N'監(jiān)控文件路徑',1);
來查看行方式的結(jié)果。
同樣的富有創(chuàng)造力的讀者可以自己創(chuàng)建監(jiān)控鎖定，監(jiān)控死鎖等方式保存文件，但是我的建議是盡可能的減少噪音，也就是說我們要達(dá)到什么目地就在《Microsfot SQL Server 2005 技術(shù)內(nèi)幕： T-SQL 程序設(shè)計(jì)》 中有一個(gè)正則，用來將類似的語句全部組合成，只有參數(shù)形式替換具體值的SQL CLR，但是我認(rèn)為那個(gè)正則還有bug，等我空了給大家寫一個(gè)，自己也能使用的更完善。
監(jiān)控異常
在上個(gè)系列中，講述了具體的SQL Event抓去的異常，可以及時(shí)通知，但是具體的異常信息，并不是特別詳細(xì)。因此我們可以選擇事件中的Error來添加有關(guān)T-SQL批處理和SP的所有異常，用于分析，這個(gè)跟蹤非常有利于我們監(jiān)控一些異常情況?。。∥覄?chuàng)建了一個(gè)跟蹤的腳本，和上面的跟蹤事件的腳本一樣，超過5MB RollOver。我們要定期的執(zhí)行這個(gè)跟蹤，雖然不建議長(zhǎng)期開啟，但是定期監(jiān)控處理異常是有利我們系統(tǒng)更加長(zhǎng)時(shí)間運(yùn)作的。
- CREATE PROC [dbo].[sp_trace_sql_exception]
- @FilePath nvarchar(260)
- AS
- DECLARE @rc int,@TraceID int,@Maxfilesize bigint
- SET @maxfilesize = 5
- EXEC @rc = sp_trace_create @TraceID output, 2, @FilePath, @Maxfilesize, NULL
- IF (@rc != 0)
- RETURN;
- DECLARE @on bit
- SET @on = 1
- EXEC sp_trace_setevent @TraceID, 33, 1, @on
- EXEC sp_trace_setevent @TraceID, 33, 14, @on
- EXEC sp_trace_setevent @TraceID, 33, 51, @on
- EXEC sp_trace_setevent @TraceID, 33, 12, @on
- EXEC sp_trace_setevent @TraceID, 11, 2, @on
- EXEC sp_trace_setevent @TraceID, 11, 14, @on
- EXEC sp_trace_setevent @TraceID, 11, 51, @on
- EXEC sp_trace_setevent @TraceID, 11, 12, @on
- EXEC sp_trace_setevent @TraceID, 13, 1, @on
- EXEC sp_trace_setevent @TraceID, 13, 14, @on
- EXEC sp_trace_setevent @TraceID, 13, 51, @on
- EXEC sp_trace_setevent @TraceID, 13, 12, @on
- DECLARE @intfilter int,@bigintfilter bigint;
- EXEC sp_trace_setstatus @TraceID, 1
- SELECT TraceID=@TraceID
- GOTO finish
- ERROR:
- SELECT ErrorCode=@rc
- FINISH:
定期執(zhí)行吧，同志們，找異常。
默認(rèn)跟蹤和黑盒跟蹤
在sys.traces中的TraceID = 1的跟蹤是SQL Server 默認(rèn)跟蹤，這個(gè)跟蹤比較輕量級(jí)，一般監(jiān)控服務(wù)器的啟用停止，對(duì)象的創(chuàng)建和刪除，日志和數(shù)據(jù)文件自動(dòng)增長(zhǎng)以及其他數(shù)據(jù)庫的變化。（監(jiān)控那些沒事刪錯(cuò)了表的人，是最好的，當(dāng)然前提不要都使用一個(gè)帳號(hào)?。?/p>
可以通過
- EXEC sp_configure 'default trace enabled',0;
- RECONFIGURE WITH OVERRIDE;
來關(guān)閉默認(rèn)跟蹤。
黑盒跟蹤，就是可以幫助我們?cè)\斷數(shù)據(jù)庫沒事自個(gè)奔了的異常，在MSDN 搜索sp_create_trace的時(shí)候應(yīng)該也發(fā)現(xiàn)了
的選項(xiàng)，那么我們也能創(chuàng)建一個(gè)類似的存儲(chǔ)過程來快速的創(chuàng)建黑盒跟蹤，幫助我們?cè)\斷一些異常！
- CREATE PROCEDURE sp_trace_blackbox
- @FilePath nvarchar(260)
- AS
- BEGIN
- DECLARE @TraceID int,@MaxFileSize bigint
- SET @MaxFileSize = 25;
- EXEC sp_trace_create @TraceID OUTPUT,8,@FilePath,@MaxFileSize
- EXEC sp_trace_setstatus @TraceID,1;
- END
我這里提供@FilePath = NULL參數(shù)，這個(gè)默認(rèn)就保存在SQL Server的數(shù)據(jù)文件夾中。
結(jié)尾
這里詳細(xì)的描述了SQL Server Trace 的各種功能特性，有興趣的朋友可以深入到MSDN研究監(jiān)控，我這是也只是一筆帶過，也參考了MSDN 和《Microsoft SQL Server 2005調(diào)優(yōu)》那本書，下面的監(jiān)控可能和大家講述 DDL觸發(fā)器監(jiān)控，C2審核以及SQL Server的事件通知（涉及的Service Broker我會(huì)開一個(gè)系列和大家詳細(xì)說說Service Broker），最后的結(jié)束可能就是說說2008的數(shù)據(jù)收集監(jiān)控
本文標(biāo)題：SQLServer監(jiān)控系列之調(diào)優(yōu)排錯(cuò)
分享URL：http://www.5511xx.com/article/codidid.html
