日度归档:2018年5月10日

mssql sqlserver数据库监控方法分享


摘要:
sqlserver 已经面世30年了,下文讲述我们常用的监控计算机性能的方法分享



磁盘IO性能监控

Physical Disk: % Disk Time:
该计数器监视磁盘忙于读/写活动的时间。如果物理磁盘:%磁盘时间计数器接近或超过90%,则表示太多系统请求正在等待磁盘访问(通过物理磁盘:当前磁盘队列长度计数器检查此问题)。
待处理I / O请求的数量应保持不超过物理磁盘的主轴数量的1.5至2倍。
Physical Disk: Average Disk Queue Length:等待I / O操作的次数(再次,超过磁盘主轴数量的1.5倍或2倍)
SQL Server Buffer Manager – 页面读取/秒和页面写入/秒。如果此计数器超出您的基准线,则可能表示需要更多硬件电源

CPU利用率

Processor: % Processor time:如果在80-90%太高。多处理器系统为每个CPU都有一个单独的实例。
Processor: % Privileged time:表示在Windows内核命令(SQL Server I / O请求)上花费的时间。如果这个和物理磁盘计数器都很高,那么可能需要更快的磁盘或者更低的负载。
Processor: % user time:CPU花费在用户进程上的时间百分比(SQL Server)
Processor: Queue Length:等待处理器时间的线程数量。高数字可能表示需要更快或更多的处理器。

内存使用情况

Memory: Available MBs:表示有多少内存可用于新进程
Memory: Pages/sec:该计数器表示虚拟内存访问的次数。经验法则说它应该低于20.较高的数字可能意味着过度的分页。使用内存:页面错误/秒可以进一步指示SQL Server或其他进程是否导致它。

监视SQL Server

SQL Server使用对象和计数器,每个对象包含一个或多个计数器。例如,SQL Server锁对象具有称为“每秒死锁数”或“每秒锁定超时数”的计数器。
访问方法 – 完整扫描/秒:更高的数字(> 1或2)可能意味着您没有使用索引,而是使用表扫描。
缓冲区管理器 – 缓冲区缓存命中率:这是数据缓存提供服务的请求的百分比。当正确使用缓存时,这应该超过90%。计数器可以通过增加更多的RAM来改进。
内存管理器 – 目标服务器内存(KB):表示SQL Server“需要”多少内存。如果这与SQL Server:内存管理器 – 总服务器内存(KB)计数器相同,那么您知道SQL Server具有它所需的全部内存。
内存管理器 – 总服务器内存(KB):SQL Server实际使用的内存。如果这与SQL Server:内存管理器 – 目标服务器内存(KB)相同,则SQL Server具有它想要的所有内存。如果更小,那么SQL Server可以从更多的内存中受益。
锁定 – 平均等待时间:该计数器显示获取锁定所需的平均时间。这个值需要尽可能低。如果异常高,您可能需要查找阻止其他进程的进程。您可能还需要检查用户的T-SQL语句,并检查是否有其他I / O瓶颈。

Windows监视工具

性能监视器
性能监视器(或者,在一些较旧的Windows版本中,系统监视器)是一个方便的实时图形监视工具。它可以监控多种不同的度量标准来源,并提供数据导出,远程监控等功能。
———————
Windows事件日志
Windows应用程序事件日志是一组全面的通知​​和日志,属于重要的系统范围事件,包括SQL Server。

SQL Server Profiler

微软最近宣布,数据库引擎的SQL Server Profiler将在未来的版本中被弃用。
不过,它仍然是一个有用的工具
它带有MS SQL Server,非常擅长识别在底层发生的事情,
例如发现SQL语句在内部如何处理,执行哪些脚本以完成T-SQL命令以及其他功能。
您可以将由SQL Server Profiler收集的信息存储在文件或数据库表中,以便稍后导出并将其用于其他应用程序中的分析。


SSMS活动监视器

活动监视器实用程序是一个漂亮的工具,它提供了SQL Server Management Studio中SQL Server系统性能的快速概览。
您可以查看与进程和锁有关的信息,以及处理器时间,等待统计,数据库I / O,批量请求,资源等待,数据文件I / O和有关查询的信息。

SQL Server错误日志

就像Windows应用程序事件日志一样,某些系统和用户定义的事件记录在SQL Server错误日志中。此日志中的信息有助于进行故障排除并确保备份和恢复,批处理命令和其他脚本等进程按预期工作。
SQL Server错误日志采用文本文件的形式,可以使用任何文本编辑器查看。默认情况下,这些文件位于Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\文件ErrorLog和ErrorLog.n。


系统存储过程

通过系统存储过程,我们可以监控系统的压力和死锁情况
存储过程 描述
sp_who(Transact-SQL) 报告有关当前SQL Server用户和进程的快照信息,包括当前正在执行的语句以及语句是否被阻止。
sp_lock(Transact-SQL) 报告有关锁的快照信息,包括对象ID,索引ID,锁的类型以及锁适用的类型或资源。
sp_spaceused(Transact-SQL) 显示表(或整个数据库)使用的当前磁盘空间量的估计值。
sp_monitor(Transact-SQL) 显示统计信息,包括CPU使用率,I / O使用率以及自sp_monitor上次执行以来的空闲时间量。


第三方工具

SQL Server附带一套丰富的监视和配置工具
。第三方工具提供了额外的功能和灵活性,提供简单而全面的解决方案来监控挑战,并为此付出更多努力。
您可能想要查看Redgate SQL Monitor,Idera SQL Diagnostic Manager,SQL Server的 Netwrix Auditor或Apex SQL Monitor,
它们提供了深入的SQL Server度量标准和实用程序,可以很好地处理大型环境并提供免费试用版。

mssql sql代理被禁用时,如何查看当前sql server下的所有作业呢?


摘要:
当我们禁用sql server代理服务时,我们是无法通过ssms界面查看当前sqlserver下的所有作业时,此时我们只能通过以下t-sql的方式获取sqlserver下所有作业信息


   
--方法1:获取数据库下作业信息
SELECT * FROM msdb.dbo.sysjobs
 
--方法2:获取数据库下作业信息 及作业的运行信息
SELECT *  FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobschedules sjs ON sj.job_id = sjs.job_id
 

mssql sqlserver中估算数据压缩后所需的硬盘空间


摘要:
今天面试时,项目leader问,现在有一个mssql数据库,你能估算出数据压缩后所需的数据空间吗
实验环境:sqlserver 2008 企业版


SQL Server中内置存储过程sp_estimate_data_compression_savings。
我们可以通过输入相应的参数到此存储过程中获取压缩后的空间
我们可以将三个参数中的任意一个传递给此存储过程 – PAGE,ROW或NONE。

   对testInfo表执行row压缩
EXEC SYS.sp_estimate_data_compression_savings
       @SCHEMA_NAME='dbo',
       @OBJECT_NAME='testInfo',
       @INDEX_ID=NULL,
       @PARTITION_NUMBER=NULL,
       @DATA_COMPRESSION='ROW'
GO
 ----- 对testInfo表执行page压缩
EXEC sp_estimate_data_compression_savings 'Sales', 'InvoiceLines', NULL, NULL, 'PAGE' ;
EXEC SYS.sp_estimate_data_compression_savings
       @SCHEMA_NAME='dbo',
       @OBJECT_NAME='testInfo',
       @INDEX_ID=NULL,
       @PARTITION_NUMBER=NULL,
       @DATA_COMPRESSION='PAGE'
GO