SQL Server 维护脚本分享（13）数据库内部日常巡检

原创
JeesonHuang 2023-01-11 02:12:48 博主文章分类：SQLServer ©著作权
©著作权归作者所有：来自51CTO博客作者JeesonHuang的原创作品，请联系作者获取转载授权，否则将追究法律责任
--当前正在执行的语句
select session_id,transaction_id,wait_type,last_wait_type,wait_resource,start_time,status,command
,estimated_completion_time,cpu_time,logical_reads,text,open_transaction_count,open_resultset_count,percent_complete
from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) s
where session_id>50 and session_id<>@@spid
go


--堵塞语句
select spid,blocked,waittime,lastwaittype,waitresource,open_tran,status,p.dbid,cpu,physical_io,memusage,login_time,last_batch
,hostname,[program_name],hostprocess,cmd,nt_domain,nt_username,net_address,net_library,loginame,sql_handle,text
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s
where blocked >0 or spid in(select sp.blocked from master.dbo.sysprocesses sp where sp.blocked>0)
go


--是否有未提交事务
select spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran   
,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text   
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s   
where open_tran <> 0   
go  

--各数据库连接数  
SELECT @@ServerName AS server,NAME AS dbname,COUNT(STATUS) AS number_of_connections,GETDATE() AS timestamp  
FROM sys.databases sd  LEFT JOIN sys.sysprocesses sp ON sd.database_id = sp.dbid  
WHERE database_id NOT BETWEEN 1 AND 4  
GROUP BY NAME  
GO 


------------------------------------------------------------------------
--死锁跟踪，启用： dbcc traceon(1222,-1)
------------------------------------------------------------------------
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE ID=OBJECT_ID('TEMPDB..#deadlock'))
    Drop TABLE #deadlock
CREATE TABLE #deadlock(LogDate DATETIME,ProcessInfo VARCHAR(20),Text VARCHAR(2000))
go
INSERT INTO #deadlock EXEC xp_readerrorlog 0,1,N'deadlock victim',N'','2018-03-01','2018-12-30','DESC'
go
SELECT DISTINCT 'exec xp_readerrorlog 0,1,NULL,NULL,'''+CONVERT(VARCHAR(19),LogDate,120)+''','''+CONVERT(VARCHAR(19),DATEADD(S,1,LogDate),120)+''',''ASC'''
FROM #deadlock
go

------------------------------------------------------------------------
------------------------------------------------------------------------
--查看最近失败的SqlServer作业
select top 10 run_date,run_time,run_duration,step_name,message
from  msdb..sysjobhistory where run_status = 0 
order by run_date desc,run_time desc
go

--各DB最近备份情况
SELECT database_name 
,MAX(CASE WHEN type='D' THEN backup_finish_date ELSE NULL END) AS 完整备份时间 
,MAX(CASE WHEN type='I' THEN backup_finish_date ELSE NULL END) AS 差异备份时间 
,MAX(CASE WHEN type='L' THEN backup_finish_date ELSE NULL END) AS 日志备份时间 
FROM( 
    SELECT database_name,type,MAX(backup_finish_date) AS backup_finish_date  
    FROM msdb.dbo.backupset 
    GROUP BY database_name,type 
) T GROUP BY database_name 
go

--谁对对象进行了 DDL 操作 (exec sp_configure 'default trace enabled')
DECLARE @path NVARCHAR(1000)    
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +'\log.trc'    
FROM sys.traces WHERE  id = 1    
SELECT DatabaseID,NTDomainName,NTUserName,HostName, ClientProcessID,ApplicationName  
,LoginName,StartTime,DatabaseName,ObjectName,SessionLoginName
,(CASE WHEN EventClass=46 THEN 'Object:Created' WHEN EventClass=47 THEN 'Object:Deleted' WHEN EventClass=164 THEN 'Object:Altered' END)EventClass
FROM ::fn_trace_gettable(@path, 0)    
WHERE EventClass in(46,47,164) and DatabaseName<>'tempdb' and ObjectName is not null 
GO

--平均耗时最大的 SQL 语句
SELECT TOP 30 execution_count,total_worker_time,total_logical_reads,total_logical_writes
,total_worker_time/total_logical_reads as avgRead,s.text
FROM sys.dm_exec_query_stats qs  cross apply sys.dm_exec_sql_text(qs.sql_handle) s
where execution_count>=100 and total_logical_reads<>0
order by avgRead desc
go

--平均罗辑读最大的 SQL 语句
SELECT TOP 30 execution_count,total_worker_time,total_logical_reads,total_logical_writes
,total_worker_time/total_logical_reads as avgRead,s.text
FROM sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) s
where execution_count<=10 and total_logical_reads<>0
order by avgRead desc
go

--系统主要等待类型  
SELECT TOP 10   
wait_type,waiting_tasks_count ,wait_time_ms,signal_wait_time_ms   
,wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms   
,100.0 * wait_time_ms / SUM (wait_time_ms ) OVER( )AS percent_total_waits   
,100.0 * signal_wait_time_ms / SUM (signal_wait_time_ms) OVER( )AS percent_total_signal_waits   
,100.0 * ( wait_time_ms - signal_wait_time_ms )/SUM (wait_time_ms ) OVER( ) AS percent_total_resource_waits   
FROM sys .dm_os_wait_stats  
WHERE wait_time_ms > 0  
go

--当前锁请求脚本
select req_spid  
,case req_status when 1 then '已授予' when 2 then '正在转换' when 3 then '正在等待' end as req_status  
,case rsc_type when 1 then 'NULL 资源（未使用）' when 2 then '数据库' when 3 then '文件'  
    when 4 then '索引' when 5 then '表' when 6 then '页' when 7 then '键'   
    when 8 then '扩展盘区' when 9 then 'RID（行 ID)' when 10 then '应用程序' else '' end rsc_type  
,coalesce(OBJECT_NAME(rsc_objid),db_name(rsc_dbid)) as [object]  
,case req_mode when 1 then 'NULL' when 1 then 'Sch-S' when 2 then 'Sch-M' when 3 then 'S'   
    when 4 then 'U' when 5 then 'X' when 6 then 'IS' when 7 then 'IU' when 8 then 'IX' when 9 then 'SIU'   
    when 10 then 'SIX' when 11 then 'UIX' when 12 then 'BU' when 13 then 'RangeS_S' when 14 then 'RangeS_U'   
    when 15 then 'RangeI_N' when 16 then 'RangeI_S' when 17 then 'RangeI_U' when 18 then 'RangeI_X'   
    when 19 then 'RangeX_S' when 20 then 'RangeX_U' when 21 then 'RangeX_X' else '' end req_mode  
,rsc_indid as index_id,rsc_text,req_refcnt  
,case req_ownertype when 1 then '事务' when 2 then '游标' when 3 then '会话' when 4 then 'ExSession' else'' end req_ownertype  
from sys.syslockinfo WHERE rsc_type<>2 
GO

------------------------------------------------------------------------
------------------------------------------------------------------------
EXEC xp_enumerrorlogs 1 --查看 sqlserver 错误日志大小  
EXEC xp_enumerrorlogs 2 --查看 代理日志大小 
go
exec msdb.dbo.sp_cycle_errorlog         --  "Sql Server 日志"切换  
exec msdb.dbo.sp_cycle_agent_errorlog   --  "代理错误日志"切换  
go

--各数据库日志大小及使用百分比
dbcc sqlperf(logspace)  
go

--当前DB虚拟日志数量  
DBCC loginfo  
go

--数据库活动游标   
DBCC activecursors 
go

--查看操作系统逻辑磁盘可用空间
EXEC master.dbo.xp_fixeddrives
go

--  数据库大小
select name,sum(size)*8/1024 from sys.database_files where type=0 group by name order by name
go
exec master.dbo.proc_getdbspaceused
go

--数据库表大小及行数（部分不算太准确，但可作为参考）  
SELECT OBJECT_NAME(id) as tab,rows,(reserved*8)/1024 as size_MB
FROM SYS.sysindexes WHERE indid IN(0,1) and id in(select object_id from sys.tables )
order by size_MB desc
go

--数据库文件默认设置情况
select DB_NAME(database_id) as dbName,file_id,(size*8/1024)  as [size(mb)]  
,case when is_percent_growth = 1 then '10%' else CONVERT(varchar(10),growth*8/1024)+'M' end as growth  
,type_desc,physical_name   
from sys.master_files   
where state = 0
go

--各数据库 buffer pool 的分配情况 
SELECT   
CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS Database_name  
,count(*) AS cached_pages_count  
,count(*)*8/1024 AS cached_space_in_mb  
,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb  
FROM sys.dm_os_buffer_descriptors(nolock)  
GROUP BY db_name(database_id) ,database_id  
ORDER BY cached_pages_count DESC; 
GO

--当前内存脏页数量及大小 
SELECT db_name(database_id) AS 'Database'  
    ,count(page_id) AS 'Dirty Pages'  
    ,count(page_id)*8/1024 AS 'Dirty Pages(MB)'  
FROM sys.dm_os_buffer_descriptors(nolock)  
WHERE is_modified =1  
GROUP BY db_name(database_id)  
ORDER BY 'Dirty Pages' DESC 
GO

--缓存类型数量大小  
select cacheobjtype as [Cached Type]  
,COUNT(*) [Number of Plans]   
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
from sys.dm_exec_cached_plans   
group by cacheobjtype   
order by [Plan Cache Size(MB)] desc  
GO

--缓存对象数量大小  
select objtype as [Cached Object Type]  
,COUNT(*) as [Number of Plans]   
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
from sys.dm_exec_cached_plans   
group by objtype   
order by [Plan Cache Size(MB)] desc  
GO

------------------------------------------------------------------------
--  前N行则表示最近的N分钟内CPU使用情况  
------------------------------------------------------------------------
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) 
FROM sys.dm_os_sys_info WITH (NOLOCK));    
SELECT TOP(60)   
 DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]    
,SQLProcessUtilization AS [SQL Server Process CPU Utilization]  
,SystemIdle AS [System Idle Process]  
,(100 - SystemIdle - SQLProcessUtilization) AS [Other Process CPU Utilization]  
FROM (  
    SELECT record.value('(./Record/@id)[1]', 'int') AS record_id  
    ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS[SystemIdle]  
    ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization], [timestamp]    
    FROM (  
        SELECT [timestamp], CONVERT(xml, record) AS [record]    
        FROM sys.dm_os_ring_buffers WITH (NOLOCK)    
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'    
        AND record LIKE N'%<SystemHealth>%'  
    ) AS x    
) AS y    
ORDER BY record_id DESC OPTION (RECOMPILE); 
GO


-------------------------------------------------------------------------------------------
--复制相关
-------------------------------------------------------------------------------------------
--事务复制：未分发命令数(分发服务器执行)
SELECT  'EXEC distribution.sys.sp_replmonitorsubscriptionpendingcmds @publisher = N'''   
        + a.publisher + ''', @publisher_db = N''' + a.publisher_db   
        + ''', @publication = N''' + a.publication + ''', @subscriber = N'''   
        + c.name + ''', @subscriber_db = N''' + b.subscriber_db   
        + ''', @subscription_type =' + CAST(b.subscription_type AS VARCHAR)   
FROM    distribution.dbo.MSreplication_monitordata a ( NOLOCK )   
INNER JOIN (   
    SELECT   publication_id ,subscriber_id ,subscriber_db ,subscription_type   
    FROM     distribution.dbo.MSsubscriptions (NOLOCK)   
    GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type   
) b ON a.publication_id = b.publication_id   
INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id   
WHERE   a.agent_type = 1
go

--查看前10个等待分发命令最多的事务数 及 查看命令
use distribution 
go 
SELECT top 10  A.xact_seqno,A.entry_time,COUNT(*) AS cmds 
FROM distribution.dbo.MSrepl_transactions A(NOLOCK)  
INNER JOIN distribution.dbo.MSrepl_commands B(NOLOCK)  
ON A.xact_seqno=B.xact_seqno 
GROUP BY A.xact_seqno,A.entry_time 
ORDER BY cmds DESC 
go

--查看出现错误的事务序列号(历史记录) (分发服务器执行)
SELECT  'EXEC distribution.dbo.sp_helpsubscriptionerrors N''' 
+ a.publisher + ''', N''' + a.publisher_db    + ''', N''' + a.publication + ''', N'''   + c.name + ''',N''' + b.subscriber_db    + ''''
FROM    distribution.dbo.MSreplication_monitordata a ( NOLOCK )   
INNER JOIN (   
    SELECT   publication_id ,subscriber_id ,subscriber_db ,subscription_type   
    FROM     distribution.dbo.MSsubscriptions (NOLOCK)   
    GROUP BY publication_id ,subscriber_id ,subscriber_db ,subscription_type   
) b ON a.publication_id = b.publication_id   
INNER JOIN sys.servers c ( NOLOCK ) ON b.subscriber_id = c.server_id   
WHERE   a.agent_type = 1
GO