SQLServer 相关监控脚本和工具

SQL SERVER监控

-- 非常好非常全的巡检脚本
https://www.cnblogs.com/lyhabc/p/4538145.html
非常全的巡检脚本
https://blog.csdn.net/yangzhawen/article/details/7259941
https://blog.csdn.net/kk185800961/article/details/72084951

SQL Server定时自动抓取耗时SQL并归档数据发邮件脚本
https://www.cnblogs.com/lyhabc/p/4587811.html

-- SQL Server 告警邮件配置
在数据库邮件中心进行配置文件配置
在sqlserver代理 属性 警报系统 启用邮件配置文件
新建一个操作员
作业中选择通知选项,选择电子邮件通知该操作员,发送电子邮件

EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'SQLServer',
                    @recipients = 'dba@xx.com', -- varchar(max) --收件人
                    @subject = N'SQL Server 实例SQL语句抓取统计信息', -- nvarchar(255) 标题
                    @body_format = 'HTML', -- varchar(20) 正文格式可选值：text html
                    @body = @finalSQL

-- powershell
 SQL Server自动化运维系列——监控性能指标脚本（Power Shell）
 SQL Server自动化运维系列——监控跑批Job运行状态（Power Shell）
 SQL Server自动化运维系列——监控磁盘剩余空间及SQL Server错误日志（Power Shell）
 SQL Server自动化运维系列——监控跑批Job运行状态（Power Shell）
 SQL Server自动化运维系列——监控磁盘剩余空间及SQL Server错误日志（Power Shell）

https://www.cnblogs.com/zhijianliutang/category/668928.html

-- # 用powershell实现多台服务器批量执行SQL脚本：

-- 临时启用Ad Hoc Distributed Queries 
-- 支持openrowset,openquery,OPENDATASOURCE实现从一台中心机连到其他实例抓取数据
https://www.cnblogs.com/LC0507/p/11209327.html

-- 查看远程分布式权限：
select value from sys.configurations where name='Ad Hoc Distributed Queries';

-- Invoke-Sqlcmd 导出数据成csv格式
https://blog.51cto.com/ultrasql/2050176

-- 执行脚本授权
rem get-executionpolicy
rem set-executionpolicy remotesigned  

-- 执行Invoke-SQLCMD
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

-- SQLCMD    -E 远程连接使用身份信任 去除标题，宽度100，去除多余空格，以逗号分割
sqlcmd -E -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt
-h-1 -w 100 -W -s ','

-- windows批处理实现循环处理多个变量
for /f "delims=， tokens=2,5" %%i in (test.txt) do (echo %%i %%j
sqlcmd -S %%i\%%j
)

-- if判断：

rem or：NEQ EQU
SETLOCAL ENABLEDELAYEDEXPANSION

rem default delim is blank or tab
rem for /f "delims=,tokens=1,2,3*" %%i in (serverlist.txt) do (echo %%i 
for /f "tokens=1,2,3*" %%i in (serverlist.txt) do (
echo %%i, %%j, %%k 

set x=%%i
echo !x!
set x1=!x:~0,1!
echo !x1!
if "!x1!" NEQ "#" (
echo sqlcmd -E -S %%i\%%j,%%k  -i select.sql -o select.out
sqlcmd -E -S %%i\%%j,%%k  -i select.sql -o select.out
type select.out>>run.out
) else (
echo skipped %%i %%j %%k
)

)

-- SQL
sqlserver监控脚本_发现某个等待就发出邮件
http://blog.itpub.net/30126024/viewspace-2672303/

磁盘空间监控
https://www.cnblogs.com/seusoftware/p/3939501.html

错误日志监控

默认的错误日志文件为：C:\Program Files\Microsoft SQL Server\MSSQL<版本>.<实例ID>\MSSQL\Log\ERRORLOG
https://www.cnblogs.com/seusoftware/p/3926674.html

sql server死锁跟踪
https://www.cnblogs.com/gered/p/10811758.html

-- zabbix监控
Zabbix监控sqlserver
https://blog.51cto.com/13272050/2050601

http://www.cppcns.com/jiaoben/dosbat/122184.html
zabbix_command.conf如下添加：
UserParameter=NewMonitor[*],cmd /k c:\zabbix\NewMonitor.bat monitor1

-- Pssdiag/SQLdiag.exe 服务器性能报告分析工具
https://github.com/Microsoft/SqlNexus

-- sqlserver 跨平台管理工具
https://github.com/microsoft/azuredatastudio

-- toad for sqlserver
https://www.jb51.net/database/628060.html

# 巡检脚本：
http://blog.itpub.net/29371470/viewspace-1175013/
--1.查看数据库版本信息  
select @@version  
--2.查看所有数据库名称及大小  
exec sp_helpdb  
--3.查看数据库所在机器的操作系统参数  
exec master..xp_msver  
--4.查看数据库启动的参数  
exec sp_configure  
--5.查看数据库启动时间  
select convert(varchar(30),login_time,120)  
from master..sysprocesses where spid=1  
--6.查看数据库服务器名  
select 'Server Name:'+ltrim(@@servername)  
--7.查看数据库实例名  
select 'Instance:'+ltrim(@@servicename)   
--8.数据库的磁盘空间呢使用信息  
exec sp_spaceused  
--9.日志文件大小及使用情况  
dbcc sqlperf(logspace)  
--10.表的磁盘空间使用信息  
exec sp_spaceused 'tablename'  
--11.获取磁盘读写情况  
select   
@@total_read [读取磁盘次数],  
@@total_write [写入磁盘次数],  
@@total_errors [磁盘写入错误数],  
getdate() [当前时间]  
--12.获取I/O工作情况  
select @@io_busy [自上次启动的I/O操作毫秒数],  
@@timeticks [每个时钟周期对应的微秒数],  
@@io_busy*@@timeticks [I/O操作毫秒数],  
getdate() [当前时间]  
--13.查看CPU活动及工作情况  
select  
@@cpu_busy [自上次启动CPU的工作时间毫秒数],  
@@timeticks [每个时钟周期对应的微秒数],  
@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],  
@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],  
getdate() [当前时间]  
--14.检查锁与等待  
exec sp_lock  


--15.检测死锁和阻塞
declare @spid int,@bl int,
@intTransactionCountOnEntry     int,
@intRowcount             int,
@intCountProperties         int,
@intCounter             int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 print @@ERROR
insert into #tmp_lock_who(spid,bl) select  0 ,blocked
from (select * from sysprocesses where  blocked>0 ) a
where not exists(select * from (select * from sysprocesses
where  blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where  blocked>0
IF @@ERROR<>0 print @@ERROR
-- 找到临时表的记录数
select     @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 print @@ERROR
if    @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select     @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被'
+ '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
/

--16.用户和进程信息  
exec sp_who  
exec sp_who2  

--17.活动用户和进程的信息  
exec sp_who 'active'  


--19.查看所有数据库用户登录信息  
exec sp_helplogins   

--20.查看所有数据库用户所属的角色信息  
exec sp_helpsrvrolemember  

--21.查看链接服务器  
exec sp_helplinkedsrvlogin  

--22.查看远端数据库用户登录信息  
exec sp_helpremotelogin  

--23.获取网络数据包统计信息  
select   
@@pack_received [输入数据包数量],  
@@pack_sent [输出数据包数量],  
@@packet_errors [错误包数量],  
getdate() [当前时间]  

--24.检查数据库中的所有对象的分配和机构完整性是否存在错误  
--dbcc checkdb  

--25.查询文件组和文件  
select   
    df.[name],df.physical_name,df.[size],df.growth,   
    f.[name][filegroup],f.is_default   
from sys.database_files df join sys.filegroups f   
on df.data_space_id = f.data_space_id   

--26.查看数据库中所有表的条数  
select  b.name as tablename ,    
        a.rowcnt as datacount    
from    sysindexes a ,    
        sysobjects b    
where   a.id = b.id    
        and a.indid < 2    
        and objectproperty(b.id, 'IsMSShipped') = 0   

--27.得到最耗时的前10条T-SQL语句  
;with maco as     
(       
    select top 10    
        plan_handle,    
        sum(total_worker_time) as total_worker_time ,    
        sum(execution_count) as execution_count ,    
        count(1) as sql_count    
    from sys.dm_exec_query_stats group by plan_handle    
    order by sum(total_worker_time) desc    
)    
select  t.text ,    
        a.total_worker_time ,    
        a.execution_count ,    
        a.sql_count    
from    maco a    
        cross apply sys.dm_exec_sql_text(plan_handle) t   

--28. 查看SQL Server的实际内存占用  
select * from sysperfinfo where counter_name like '%Memory%'  


--29.显示所有数据库的日志空间信息  
dbcc sqlperf(logspace)  

--30.收缩数据库  
 -- dbcc shrinkdatabase(databaseName)

监控指标：

数据库服务器的监控可大致分为两类：

(1) 状态监控：数据库服务器有没有在健康地运行？ (2) 性能监控：健康运行的同时，有没有性能问题？可不可以更快些？

一. 服务器

状态监控

(1) 服务器是否可访问？ (2) 数据库服务是否启用？ (3) 操作系统事件日志中的错误或告警 (4) 磁盘可用空间

性能监控

(1) IO压力 (2) 内存使用 (3) CPU使用 (4) 网络带宽占用

这1，2，3，4是按照容易出现瓶颈的顺序排列的，由于磁盘的读写速度限制，通常IO是最容易出现瓶颈的地方，我们所做的很多优化，也都是针对IO的，比如：索引优化，读写分离等等。

二. 数据库

状态监控

(1) 数据库可否打开 (数据库状态) (2) SQL Server/SQL Server Agent错误日志中的错误或告警 (3) 数据库/文件组可用空间 (4) SQL Agent 作业运行状态 (5) 数据库备份有没有成功 (6) 数据库还原测试的结果 (7) 数据库一致性检查的结果 (DBCC CHECKDB)

以下几条状态监控，通常需要和系统平均值/基线值比较才有意义，否则没有告警的标准。

(8) 连接数、请求数、事务数、线程数 (9) 数据库/文件/表的大小 (10) 表使用、行数

性能监控

(1) 有没有长时间运行的查询 (一般指没有被任何请求阻塞，效率很差的查询) (2) 有没有被阻塞的查询 (可能单独运行很快，但和别的请求一起，由于有锁等待，耗时很长) (3) 有没有死锁 (开发人员/用户口中说的”死锁” 通常是阻塞/等待，数据库死锁通常很少让用户感觉到等待，一般是请求被中断，因为被kill掉了) (4) 有没有等待 (一般指各种资源的等待，等待和阻塞的交集就是锁等待)

以下几条性能监控，通常在性能优化时作为参考，或者如：索引碎片整理/统计信息更新，直接设置为后台维护作业，并不直接告警。

(5) 有没有缺失的/未被使用的/效率不高的索引，以及索引碎片 (6) 有没有过期的统计信息 (7) 有没有数据库文件的争用 (比如：日志文件，tempdb争用) (8) 有没有消耗CPU较大、IO读写较多的查询 (通常IO消耗大的，也就是内存消耗大的查询)

(1). 如果有部署高可用的策略，会有镜像、复制、日志传送、集群状态的监控； (2). 某些业务数据有严格的一致性要求，业务数据的校验，最好也做在监控的告警里面； (3). 对于数据库/实例的选项、参数设置，链接服务器等对象的可用性，也可以放到监控的告警中来

每日巡检

●备份 - 检查您的备份，确保备份任务都正确执行。 ●夜间作业- 查看夜间或清晨的执行的作业是否成功 ●SQL Server错误日志 - 查看SQL Server错误日志中是否有意外的任何错误或安全问题（成功或失败的登录）。 ●Windows事件日志 - 至少检查应用程序事件日志，以确定是否写入了任何Windows或硬件相关的错误或警告。 ●SQL Server代理作业 - 查看失败的SQL Server代理作业。 ●HA或DR日志 - 检查高可用性和/或灾难恢复进程日志。根据您正在使用的解决方案（日志传送，集群，复制，数据库镜像，Moebius等）决定需要检查的内容。 ●性能日志 - 查看性能指标，以确定您的基线是否超过，或者在白天需要进行审查时有缓慢点。 ●安全日志 - 从第三方解决方案或SQL Server错误日志中查看安全日志，以确定您的策略中是否存在违规的操作 ●存储 - 检查的磁盘是否具有足够的存储空间，以便在短期内支持数据库，备份，批处理等。 ●Service Broker - - 检查传输和用户定义的队列，以确保在应用程序中正确处理数据。 ●改正措施 - 根据您发现的问题和/或错误采取改正措施。 ●学习新的东西 - 虽然这个审查和更正过程可能很耗时，但每天花费一些时间来学习新的东西，以提高您每天工作的技术知识。 ●处理业务相关的内容

每周或每月清单

备份验证（完整） - 把备份文件COPY到其他备用服务器，进行还原：验证备用服务器磁盘空间是否足够验证SQL Server版本是否兼容以还原数据库验证在还原过程中不会生成错误消息验证数据库是否准确恢复，验证应用程序能否正常运行备份验证（简单） - 定期验证备份。维护任务：自动执行RESTORE VERIFYONLY进程使用RESTORE VERIFYONLY语句验证备份 Windows，SQL Server或应用程序更新 - 从硬件，操作系统，DBMS或应用程序角度检查是否需要安装的补丁容量规划 - 执行容量规划，以确保您在一段特定的时间内拥有足够的存储空间，例如6,12或18个月。碎片- 检查数据库的碎片，以确定是否需要重建特定索引维护 - 执行其他数据库维护安全性 - 删除离职人员的登录和用户

```

在天堂 - 技术老男 博主简介

数据库技术点滴累积，个人运维经验分享

在天堂 - 技术老男 ›

SqlServer ›
SQLServer 相关监控脚本和工具

SQL SERVER监控

监控指标：

您还没有登录，请您注册 登录后发表评论。

SQL SERVER监控

监控指标：

您还没有登录，请您 注册 登录后发表评论。

您还没有登录，请您注册登录后发表评论。