mssql sqlserver 数据库常用维护脚本收集—dba必备脚本4(维护脚本4)


摘要:
下文主要讲述数据库常用的监控维护脚本


--查询当前数据库的数据库链接数
select * from master.dbo.sysprocesses where dbid=db_id()

--查询 最近的前10条等待
select top 10 * from sys.dm_os_wait_stats
order by wait_time_ms desc

----根据等待类型 查询相应的数据
SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like 'LOGMGR_QUEUE%' 

--查询当前DB服务器上CPU的压力 
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255

--查询物理读占用最多的前20条sql脚本
select  top  20 [sql脚本内容] = t.text,
[执行次数] = a.execution_count,
[平均执行时间] = isnull (a.total_elapsed_time/a.execution_count, 0 ),
[平均运行时间] = a.total_worker_time /a.execution_count,
[总运行时间] = a.total_worker_time,
[最大逻辑读数] = a.max_logical_reads,
[最大物理读数] = a.max_physical_reads,
[最大逻辑写数] = a.max_logical_writes,
[生成时间] = a.creation_time 
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text( a.sql_handle ) t ORDER BY
a.max_physical_reads DESC
 
--如果当前DB进程大于500时候,返回 当前DB进程中sql脚本
if (select COUNT(*) from master.dbo.sysprocesses) > 1
begin
select  t.text as [sql脚本],a.*  from master.sys.sysprocesses a 
 CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) as t 
end
 
--查看DB服务器当前运行的进程,并根据CPU倒序返回结果
select * from master.dbo.sysprocesses
order by cpu desc

--查询当前DB服务器进程信息
sp_who 

--查询是否由于连接没有释放引起CPU过高
select * from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -10, getdate())
and login_time < dateadd(minute, -10, getdate())

--生成 强行释放的sql脚本
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses
where spid> 50
and waittype = 0x0000
and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -60, getdate())
and login_time < dateadd(minute, -60, getdate())

--查询当前CPU资源消耗过高的sql脚本信息  
select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text
from master..sysprocesses order by cpu desc,physical_io desc

--查看当前缓存中重用次数少并且占用内存大的sql语句
select  top 200 usecounts, objtype, p.size_in_bytes,[sql].[text]
FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25 qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt
WHERE plan_generation_num >1
ORDER BY qs.plan_generation_num
SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time,
COUNT(*) AS number_of_statements
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间

-- 计算可运行状态下的工作进程数量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id
FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schedulers AS s
ON o.scheduler_address=s.scheduler_address
AND s.scheduler_id<255
WHERE o.state='RUNNABLE'
GROUP BY s.scheduler_id