sql性能監控

『壹』 如何監視和查看sql server的性能

1、用SQL語句：select count(*) from master.dbo.sysprocesses 或者 sp_who 2、用管理界面： SQL2000：進入企業管理器，管理——當前活動——進程信息 SQL2005：進入manager studio ，展開對象管理器，管理——右鍵「活動監視器」——查看進程 SQL2008：進放manager studio 在菜單欄下面有一行圖標，點擊最後一個圖標「活動監視器」，進入活動監視器的界面後，點擊「進程」.

『貳』 sql server性能監控指令~~

1. 監控事例的等待

select event，sum(decode(wait_Time，0，0，1)) "Prev"，

sum(decode(wait_Time，0，1，0)) "Curr"，count(*) "Tot"

from v$session_Wait

group by event order by 4;

2. 回滾段的爭用情況

select name， waits， gets， waits/gets "Ratio"

from v$rollstat a， v$rollname b

where a.usn = b.usn;

3. 監控表空間的 I/O 比例

select df.tablespace_name name，df.file_name "file"，f.phyrds pyr，

f.phyblkrd pbr，f.phywrts pyw， f.phyblkwrt pbw

from v$filestat f， dba_data_files df

where f.file# = df.file_id

order by df.tablespace_name;

4. 監控文件系統的 I/O 比例

select substr(a.file#，1，2) "#"， substr(a.name，1，30) "Name"，

a.status， a.bytes， b.phyrds， b.phywrts

from v$datafile a， v$filestat b

where a.file# = b.file#;

5.在某個用戶下找所有的索引

select user_indexes.table_name， user_indexes.index_name，uniqueness， column_name

from user_ind_columns， user_indexes

where user_ind_columns.index_name = user_indexes.index_name

and user_ind_columns.table_name = user_indexes.table_name

order by user_indexes.table_type， user_indexes.table_name，

user_indexes.index_name， column_position;

6. 監控 SGA 的命中率

select a.value + b.value "logical_reads"， c.value "phys_reads"，

round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"

from v$sysstat a， v$sysstat b， v$sysstat c

where a.statistic# = 38 and b.statistic# = 39

and c.statistic# = 40;

7. 監控 SGA 中字典緩沖區的命中率

select parameter， gets，Getmisses ， getmisses/(gets+getmisses)*100 "miss ratio"，

(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"

from v$rowcache

where gets+getmisses <>0

group by parameter， gets， getmisses;

8. 監控 SGA 中共享緩存區的命中率，應該小於1%

select sum(pins) "Total Pins"， sum(reloads) "Total Reloads"，

sum(reloads)/sum(pins) *100 libcache

from v$librarycache;

select sum(pinhits-reloads)/sum(pins) "hit radio"，sum(reloads)/sum(pins) "reload percent"

from v$librarycache;

9. 顯示所有資料庫對象的類別和大小

select count(name) num_instances ，type ，sum(source_size) source_size ，

sum(parsed_size) parsed_size ，sum(code_size) code_size ，sum(error_size) error_size，

sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required

from dba_object_size

group by type order by 2;

10. 監控 SGA 中重做日誌緩存區的命中率，應該小於1%

SELECT name， gets， misses， immediate_gets， immediate_misses，

Decode(gets，0，0，misses/gets*100) ratio1，

Decode(immediate_gets+immediate_misses，0，0，

immediate_misses/(immediate_gets+immediate_misses)*100) ratio2

FROM v$latch WHERE name IN ('redo allocation'， 'redo ');

11.監控內存和硬碟的排序比率，最好使它小於 .10，增加 sort_area_size

SELECT name， value FROM v$sysstat WHERE name IN ('sorts (memory)'， 'sorts (disk)');

12. 監控當前資料庫誰在運行什麼SQL語句

SELECT osuser， username， sql_text from v$session a， v$sqltext b

where a.sql_address =b.address order by address， piece;

13. 監控字典緩沖區

SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;

SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

SELECT SUM(PINS) "EXECUTIONS"， SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;

後者除以前者，此比率小於1%，接近0%為好。

SELECT SUM(GETS) "DICTIONARY GETS"，SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"

FROM V$ROWCACHE

14. 找ORACLE字元集

select * from sys.props$ where name='NLS_CHARACTERSET';

15. 監控 MTS

select busy/(busy+idle) "shared servers busy" from v$dispatcher;

此值大於0.5時，參數需加大

select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';

select count(*) from v$dispatcher;

select servers_highwater from v$mts;

servers_highwater接近mts_max_servers時，參數需加大

『叄』 如何監控sqlserver 性能 死鎖

具體步驟如下：

1.首先使用下面的命令，將有關的跟蹤標志啟用。

SQL codeDBCC TRACEON (3605,1204,1222,-1)

說明：
3605
將DBCC的結果輸出到錯誤日誌。
1204 返回參與死鎖的鎖的資源和類型，以及受影響的當前命令。
1222
返回參與死鎖的鎖的資源和類型，以及使用了不符合任何 XSD 架構的 XML 格式的受影響的當前命令(比1204更進一步,SQL
2005及以上可用)。
-1 以全局方式打開指定的跟蹤標記。

以上跟蹤標志作用域都是全局，即在SQL
Server運行過程中，會一直發揮作用，直到SQL Server重啟。

如 果要確保SQL Server在重啟後自動開啟這些標志，可以在SQL
Server服務啟動選項中，使用 /T 啟動選項指定跟蹤標志在啟動期
間設置為開。(位於SQL Server配置管理器->SQL
Server服務->SQL Server->屬性->高級->啟動參數)

在運行上面的語句後，當SQL
Server中發生死鎖時，已經可以在錯誤日誌中看到了，但還不夠直觀(和其它信息混在一起)。（SSMS
-> SQL Server實例 ->
管理 -> SQL Server日誌）

2.建表，存放死鎖記錄

SQL codeUSE [Cole] --Cole是我的示例資料庫，你可以根據實際情況修改。 GO
CREATE TABLE DeadLockLog ( id int IDENTITY (1, 1) NOT NULL, LogDate DATETIME, ProcessInfo VARCHAR(10), ErrorText VARCHAR(MAX) )
GO

3.建立JOB

新建一個JOB（假設名稱為DeadLockJob）,在"步驟"中新建一步驟，隨便寫一個步驟名稱，資料庫為"Cole",在"命令"欄中輸入以下語句：

SQL code--新建臨時表 IF OBJECT_ID('tempdb.dbo.#ErrorLog') IS Not Null
DROP TABLE #ErrorLog
CREATE TABLE #ErrorLog (Id int IDENTITY (1, 1) NOT NULL, a DATETIME, b VARCHAR(10), c VARCHAR(MAX)) --將當前日誌記錄插入臨時表
INSERT INTO #ErrorLog EXEC master.dbo.sp_readerrorlog --將死鎖信息插入用戶表
insert DeadLockLog
select a, b, c from #ErrorLog where id >= (select MAX(id) from #ErrorLog WHERE c Like '%Deadlock encountered%')
DROP TABLE #ErrorLog

4.新建警報

在"新建警報"窗體的"常規"選項卡中,進行以下設置：

名稱：可根據實際自行命名，這里我用DeadLockAlert
類型：選擇"SQL
Server性能條件警報"
對象：SQLServer:Locks
計數器：Number of
Deadlocks/sec
實例：_Total
計數器滿足以下條件時觸發警報：高於
值：0
在"響應"選項卡中，選中"執行作業"，並選擇步驟3中我們新建的作業(即DeadlockJob)

到這里為止，我們已經完成了全部步驟，以後，你就可以隨時查詢DeadLockLog表，來顯示死鎖信息了。

『肆』 100ms的SQL把伺服器搞崩潰了

一個項目上線了兩個月，除了一些反饋的優化和小Bug之外，項目一切順利；前期是屬於推廣階段，可能使用人員沒那麼多，當然對於項目部署肯定提前想到並發量了，所以早就把集群安排上，而且還在測試環境搞了一下壓測，絕對是沒得問題的；但是，就在兩個月後的一天，系統突然跑的比烏龜還慢，投訴開始就陸續反饋過來了。

經過排查，原來是頻繁執行一條耗時100ms的SQL導致，100ms感覺不長，但就是把系統搞崩了，具體細節如下。

項目採用ABP進行開發，集成統一的認證中心(IDS4)，部分數據對接第三方系統，拆分後的這個項目架構相對簡單。

考慮並發量不高，就算是高峰期也不會超過1000，於是就搞了個單台的資料庫伺服器(MySQL)，測試環境中經過壓測，完全能抗住。

上線時，由於線上資源的關系，DB伺服器的配置沒有按測試環境的標准來分配，相關人員想著後續看情況進行補配。上線推的比較緊，簡單評估了配置風險，初步判斷沒啥大問題，於是就推上線了。

相關技術棧：ABP、IdentityServer4、Autofac、AutoMapper、Quartz.NET、EF Core、Redis、MySQL等，這都不重要，重要的是100ms的SQL把系統搞崩了。

由於系統相對不大，並沒有把分布式日誌、調度監控，性能監控集成上去。

上線期間，前期處於使用推廣階段，一切正常。兩個月後的一天，系統處於使用高峰時段，突然陸續收到反饋：系統有點卡！！！於是趕緊進行排查。

由於系統已經是集群部署的，慢這個問題首先懷疑是資料庫伺服器，於是讓DBA的同事排查了一下，沒有鎖，只是有大量事務等待提交(waiting for handler commit)，通過如下命令可查的：

看到都是插入審計日誌記錄導致，一看日誌記錄頻率，差不多一秒500條記錄。DBA同事說可能是記錄插入頻繁導致，此時CPU已經爆到100%了，為了快速解決問題，於是就趕緊關掉了一些不必要的日誌記錄。

這么一改，稍微降了一點，沒有事務提交的記錄，系統勉強可以撐著用，但是CPU還是在85%~97%波動；

看到這種情況，當然還是不放心，繼續排查。 中間有對伺服器的配置產生過懷疑，但非常肯定的是這不是主要原因，於是和DBA的同事繼續排查。

系統雖然可以正常使用，但時不時的也看看監控屏，CPU一直處於高水位狀態，還是有點慌的，因為一有問題，信息和電話都要爆。

突然DBA同事發現有一個單表查詢的SQL執行比較頻繁，於是單獨拿出來試了一下，查詢時間150ms左右，這個表的數據量不大，8萬左右，但沒有加任何索引，因為想著數據量不大，查詢時長還可接受，所以當時就沒有加相關索引。

定位到這條SQL後，想到的第一步就是增加索引，在測試環境上試了一把，執行效率直接飛速提高到1ms；效果如下：

所以和DBA同事達成一致意見，在生成環境上增加復合索引( 創建索引一定要注意欄位順序 )，在中午時候，系統使用頻率不太高，於是就在生成上快速加了索引，我去，CPU一下降到了20%以內，意不意外；就算在使用高峰期，也沒超過20%，通過zabbix工具監控看到CPU的效果：

問題算是解決了，總算鬆了一口氣。

這里有個問題： CPU都爆了為什麼沒有報警提醒，這塊DBA同事正在排查相關配置。這里發現CPU爆了，還是無意的遠程到伺服器，發現很卡，一看CPU才知道爆了。

系統雖小，問題不大，但其實暴露的問題還是挺多。

這次線上小事故暫時分享到這，因為項目不大，所以沒有做那麼多監控，但以下建議，小夥伴可以參考一下：

文章來自https://www.cnblogs.com/zoe-zyq/p/16207287.html

『伍』 sql server資料庫性能監控指令，請大蝦幫忙~~

連接到資料庫的會話數量，數據文件/日誌文件大小這兩個是可以直接從界面上設置，別的就不知道了，沒用過希望高手進來，頂上哈哈。