数据库查锁、查占用内存高的sql、查占用cpu，以及如何可视化监控数据库

这几天遇到项目卡顿的问题，迟迟没有找到原因，于是从数据库入手，今天给大家分享一下我的整个排查过程。

首先，针对sqlserver数据库我们可以怎么做呢？这里分享几句sql查询。

//查询死锁，主要看前两段sql，后两段是找到整个死锁阻塞情况，然后杀死锁。
//
select object_name(resource_associated_entity_id) as tableName, request_session_id as pid from sys.dm_tran_locks
where resource_type = 'OBJECT'


SELECT
es.session_id,
database_name = DB_NAME(er.database_id),
er.cpu_time,
er.reads,
er.writes,
er.logical_reads,
login_name,
er.status,
blocking_session_id,
wait_type,
individual_query = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2),
parent_query = qt.text,
program_name,
host_name,
nt_domain,
start_time
FROM
sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE
es.session_id > 50
AND es.session_Id NOT IN (@@SPID)
ORDER BY
1, 2

-- 发生阻塞时，透过以下命令，可看出是哪个进程 session id，阻塞了哪几个进程 session id，且期间经过了多少「毫秒 (ms)」。如下图 3 里 session id = 53 阻塞了 session id = 52 的进程。另透过 SQL Server Profiler 工具，也能看到相同的内容。
SELECT blocking_session_id, wait_duration_ms, session_id FROM sys.dm_os_waiting_tasks 


--透过以下两个命令，我们还能看到整个数据库的锁定和阻塞详细信息：

SELECT * FROM sys.dm_tran_locks

EXEC sp_lock

--kill 72

//查找数据库中内存占用高的sql语句。
//
SELECT s2.dbid,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1,
((CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY last_worker_time desc,s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

--查询当前高CPU活动
select top 10 s.session_id,
r.status,r.cpu_time,r.logical_reads,r.reads,r.writes,r.total_elapsed_time/(1000*60) 'elaps m',
SUBSTRING(st.text,(r.statement_start_offset/2)+1,((case r.statement_end_offset when -1 then datalength(st.text) else r.statement_end_offset end -r.statement_start_offset)/2)+1) as statement_text,
coalesce(quotename(db_name(st.dbid))+N'.'+quotename(object_schema_name(st.objectid,st.dbid))+N'.'+quotename(object_name(st.objectid,st.dbid)),'') as command_text,
r.command,s.login_name,s.host_name,s.program_name,s.last_request_end_time,s.login_time,r.open_transaction_count
from sys.dm_exec_sessions as s 
join sys.dm_exec_requests as r on r.session_id=s.session_id 
cross apply sys.dm_exec_sql_text(sql_handle) st
order by r.cpu_time desc 



----查询历史占用大量CPU情况
select top 10 st.text as batch_text,
SUBSTRING(st.text,(qs.statement_start_offset/2)+1,
((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end -qs.statement_start_offset)/2)+1) as statement_text,
(qs.total_worker_time/1000) / qs.execution_count as avg_cpu_time_ms,
(qs.total_elapsed_time/1000) / qs.execution_count as avg_elapsed_time_ms,
qs.total_logical_reads / qs.execution_count as avg_logical_reads_ms,
(qs.total_worker_time /1000) as cumulative_cpu_time_all_executions_ms,
(qs.total_elapsed_time/1000) as aumulative_elapsed_time_all_executions_ms
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
order by (qs.total_worker_time/qs.execution_count) desc

您可以通过上面的查询知道某个数据库的是否有死锁，有的话具体情况是什么，如何杀死？是否有占用内存极高的sql语句，那句sql的具体内容？cpu占用情况？

当然还有一种可视化的方式，这是sqlserver自带的一种，具体操作如下所示：

1、连接好数据库，右键选择活动和监视器，即出现如下界面，您可以在此页面看到哪些sql耗费了大量资源，哪些资源等待比较久，哪些进程时间比较久。

2、找到工具，右键选择第一个选项后会进入一个新的页面，让您连接登录数据库，等您连接成功后，又会弹出一个新的对话框，让您选择，无需改什么，直接点击运行即可，您可以在运行后的界面看到哪些sql语句执行时间很长，具体的sql语句写法，其实和前面的用sql查询的目的差不多，只是这个是可视化的。

最后，再给大家分享一下简单的Oracle数据库查锁的sql，希望对大家有帮助。

--1、查锁
select /*+rule+*/
 s.username,
 decode(l.type, 'TM', 'TABLE　LOCK', 'TX', 'ROW LOCK', null) lock_level,
 o.owner,
 o.object_name,
 o.object_type,
 s.SID,
 s.SERIAL#,
 s.TERMINAL,
 s.MACHINE,
 s.PROGRAM,
 s.OSUSER
  from v$session s, v$lock l, dba_objects o
 where l.SID = s.SID
   and l.ID1 = o.object_id(+)
   and s.USERNAME is not null;

--按计算机名查锁
select /*+rule+*/
s.username,
decode(l.type,'TM','TABLE　LOCK','TX','ROW LOCK',null) lock_level,
o.owner,o.object_name,o.object_type,
s.SID,s.SERIAL#,s.TERMINAL,s.MACHINE,s.PROGRAM,s.OSUSER
from v$session s,v$lock l,dba_objects o 
where l.SID=s.SID
and l.ID1=o.object_id(+)
and s.USERNAME is not null and s.MACHINE='你的计算机名';


--2、解锁
Alter system kill session '6479,14285'; --SID,SERIAL#