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

zhk2369

　　--当前正在执行的语句
　　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>　　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 >　　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_reads0
　　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 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（行>　　,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_type2
　　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>
　　FROM SYS.sysindexes WHERE indid IN(0,1) and>
　　order by>　　go
　　--数据库文件默认设置情况
　　select DB_NAME(database_id) as dbName,file_id,(size8/1024)  as [size(mb)]  
　　,case when is_percent_growth = 1 then '10%' else CONVERT(varchar(10),growth8/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>　　from sys.dm_exec_cached_plans
　　group by cacheobjtype

　　order by [Plan Cache>　　GO
　　--缓存对象数量大小
　　select objtype as [Cached Object Type]
　　,COUNT(*) as [Number of Plans]

　　,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache>　　from sys.dm_exec_cached_plans
　　group by objtype

　　order by [Plan Cache>　　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>　　,(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'%%'
　　) 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