SQLServer性能数据解析

磁盘相关

           ins_dir = MSSQL_DIR + "/ms" + str(port)data_dir = ins_dir + '/data'log_dir = ins_dir + '/log' backup_dir = ins_dir + '/backup' ins_size = func_os.get_dir_size( ins_dir)          data_size = func_os.get_dir_size(data_dir)log_size = func_os.get_dir_size(log_dir) backup_size = func_os.get_dir_size( backup_dir)real_ins_size = ins_size - backup_size          other_size = real_ins_size - data_size - log_size
总空间：real_ins_size = 实例目录空间 - 备份空间（落地备）数据空间：data_size其他文件空间：other_size = 总空间 - 数据空间 - 日志空间 = tepmdb空间大小（日志+数据）+ 其它（errorlog+agentlog+fulltextlog+系统库+JOBS）BinLog空间：log_size

SQL Server性能相关



动态管理视图和函数

   与session执行有关sys.dm_exec_connectionssys.dm_exec_sessions与系统有关sys.dm_os_performance_counters与IO相关sys.dm_io_virtual_file_stats

目录视图

   数据库和文件目录视图sys.master_filessys.databases

sessions

   命令 select count(*) from sys.dm_exec_sessions where login_name not in ( 'root', 'aurora', 'sa', 'eagleye') and login_name not like 's\_%' escape '\' and login_name not like %mssqld' and login_name not like '%Administrator' and status not in ( 'Preconnect', 'Dormant‘）解释sys.dm_exec_sessions：是服务器范围的视图，显示了有关所有活动用户连接和内部任务的信息。login_name 当前执行的会话所使用的 SQL Server 登录名。status    会话的状态。 可能的值：Running - 当前正在运行一个或多个请求Sleeping - 当前没有运行任何请求Dormant – 会话因连接池而被重置，并且现在处于登录前状态。Preconnect - 会话在资源调控器分类器中。分类资源调控器支持对传入会话的分类。分类基于函数中包含的一组用户编写的条件。函数逻辑的结果使资源调控器可以将会话归入现有工作负荷组类。会话传入链接——分类（分类器函数）——路由到工作负荷组——工作负荷组使用相关联的资源池——资源池提供同时限制应用程序资源

bufferhit

命令 SELECT a.cntr_value * 1.0 / b.cntr_value * 100.0 AS BufferCacheHitRatio FROM sys.dm_os_performance_counters a JOIN ( SELECT cntr_value, OBJECT_NAME FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base' AND OBJECT_NAME = 'MSSQL$MS%d:Buffer Manager') b ON a.OBJECT_NAME = b.OBJECT_NAME WHERE a.counter_name = 'Buffer cache hit ratio' AND a.OBJECT_NAME = 'MSSQL$MS%d:Buffer Manager'解释sys.dm_os_performance_counters       为服务器维护的每个性能计数器返回一行。counter_name    计数器的名称OBJECT_NAME          计数器的所属类别Buffer Manager 对象提供了计数器，用于监视 SQL Server 如何使用：内存存储数据页、内部数据结构和过程缓存。计数器监视 SQL Server 读取和写入数据库页时的物理 I/O。Buffer Manager中的Buffer cache hit ratio base和Buffer cache hit ratioBuffer cache hit ratio 官方解释在缓冲区高速缓存中找到而不需要从磁盘中读取的页的百分比。经验正常情况应该在 99%以上

checkpoint

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager' AND counter_name = 'Checkpoint pages/sec'解释由要求刷新所有脏页的检查点或其他操作每秒刷新到磁盘的页数。经验 1. 显示执行 checkpoint；会发生checkpoint 2. alter database 添加或删除数据库文件；会发生checkpoint 3. 停止服务；会发生checkpoint 4. engine定期生成checkpoint，跟 recovery interval、恢复模式相关 5. 备份；会发生checkpoint 6. 在数据库中执行了最小日志记录操作，例如，在使用大容量日志恢复模式的数据库中执行大容量复制操作；会发生checkpoint 7 执行了需要关闭数据库的活动。例如，AUTO_CLOSE 设置为 ON 并且关闭了数据库的最后一个用户连接，或者执行了需要重新启动数据库的数据库选项更改；会发生checkpoint 8. 08R2 checkpoint是实例级别的配置，但进行的过程（实现方式）是数据库级别的，这个msdn没有详细说明但可以做实验证明，大概方法可以构造两个数据库和相应数据，不同的DB session手动执行checkpoint观察sys.dm_os_buffer_descriptors 9. 脏页的多少影响checkpoint开销大小 10. 08R2镜像端不支持checkpoint

pagelife

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager    ' AND counter_name = 'Page life expectancy'解释表示页面在缓存中的预期 "寿命"，单位为秒。微软建议最少 300秒。如果在一个实例中经常低于 300秒，意味着数据保留的时间少于 5分钟就被移出内存。经验 1. 正常PLE应该是大于 300且为一条持续平稳上升的直线 2. PLE经常性的波动可能是内存压力的信号，具体再结合lazy_write，page_writes/page_reads等一起观察判定

sqlcompilations

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:SQL Statistics' AND counter_name = 'SQL Compilations/sec'解释每秒的 SQL 编译数。 表示编译代码路径被进入的次数。 包括 SQL Server 中语句级重新编译导致的编译。 当 SQL Server 用户活动稳定后，该值将达到稳定状态。经验编译截断主要是CPU开销，一般可以和Batch Requests/sec对照来看

logins

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:General Statistics' AND counter_name = 'Logins/sec'解释 General Statistics 对象提供计数器，用于监视服务器范围内的常规活动Logins/sec   每秒启动的登录数。 这不包括已入池连接。

transactions

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE 'MSSQL$MS%d:database%%' AND instance_name = '_Total' AND counter_name LIKE 'Transactions/sec%%'解释 Database 对象提供了计数器，来监视大容量复制操作、备份和还原吞吐量以及事务日志活动。_Total     有的计数器的instance_name中有值为_Total的实例，它是对该计数器所有实例的聚合值( SUM)。Transactions/sec       每秒为数据库启动的事务数经验理解SQLServer的Transactions和Batch Requests，一个Batch Requests可能有多个Transactions，Batch Requests更能说明系统负载

locktimeout

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Locks' AND counter_name = 'Lock Timeouts/sec' AND instance_name = '_Total'解释Locks 对象提供了有关各种资源类型的 SQL Server 锁的信息 Lock Timeouts/sec    每秒超时的锁请求数，包括对 NOWAIT 锁的请求。

deadlock

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Locks' AND counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'解释 Number of Deadlocks/sec     每秒导致死锁的锁请求数。经验经常死锁可能会导致严重的问题，借助Profiler可以解，但在RDS场景下最好的处理方式是打开Flag 1222 通过日志分析

lockwaits

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Locks' AND counter_name = 'Lock Waits/sec' AND instance_name = '_Total'解释 Lock Waits/sec   每秒要求调用者等待的锁请求数。

fullscans

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Access Methods' AND counter_name = 'Full Scans/sec'解释 Access Methods       对象提供用于监视如何访问数据库中的逻辑数据的计数器。 Full Scans/sec    每秒不受限制的完全扫描数。这些扫描可以是基表扫描，也可以是全文索引扫描。

pagesplits

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Access Methods ' AND counter_name = 'Page Splits/sec'解释Page Splits/sec   每秒由于索引页溢出而发生的页拆分数。经验页拆分会搬移数据，性能开销，和fillfactor参数有联系； 12相对于 08有优化，碰到大条记录拆分一次放不下可能会尝试生成一个新页存放，优化出现连续拆分的情况

lazy_writes

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager' AND counter_name = 'Lazy writes/sec'解释Lazy writes/sec   每秒被缓冲区管理器的惰性编写器写入的缓冲区数。惰性编写器    一个系统进程，用于成批刷新脏的老化的缓冲区（包含更改的缓冲区，必须将这些更改写回磁盘，才能将缓冲区重用于其他页），并使它们可用于用户进程。当 SQL Server感觉到内存压力的时候，会将最久没有使用的数据页面和执行计划从缓冲池中清理掉，做这个动作的就是Lazy Writer。经验Lazy writes有单独一个系统进程，刷脏页的过程和checkpoint类似同样会有性能影响，对比checkpoint可以理解为两者的行为类似但目的不同，Lazy writes是保证内存有可用页，一般有内存压力的时候会频繁出现，Checkpoint聚合写、优化IO、维护数据一致性，可以结合PLE一起观察内存问题

pagereads

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager ' AND counter_name = 'Page reads/sec'理解Page reads/sec  每秒发出的物理数据库页读取数。经验大量物理读写和IOPS升高是一致的；频繁持续的物理读写过高先找找是否有需要优化改写的 SQL或者考虑增加内存，当然内存是有成本的最好的方式还是通过rewrite queries/ add intelligent indexes处理；

pagewrite

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:Buffer Manager' AND counter_name = 'Page writes/sec'理解Page writes/sec  每秒执行的物理数据库页写入数。经验大量物理读写和IOPS升高是一致的；频繁持续的物理读写过高先找找是否有需要优化改写的 SQL或者考虑增加内存，当然内存是有成本的最好的方式还是通过rewrite queries/ add intelligent indexes处理；

qps

命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%d:SQL Statistics' AND counter_name = 'Batch Requests/sec'理解 SQL Statistics      对象提供计数器来监视编译和发送到 SQL Server 实例的请求类型。通过监视查询编译和重新编译的次数以及 SQL Server 实例收到的批数，可了解 SQL Server 处理用户查询的速度，以及查询优化器处理查询的效率。Batch Requests/sec  每秒收到的 Transact- SQL 命令批数。经验这个Batch Requests跟MySQL的QPS不同，SQLServer一个Batch Requests可能包含多个Transactions

traffic_kb

命令 SELECT round( SUM(net_packet_size * 1.0 * num_reads / 1024), 0) AS read_kb, round( SUM(net_packet_size * 1.0 * num_writes / 1024), 0) AS write_kb FROM sys.dm_exec_connections WHERE session_id > 50理解dm_exec_connections     返回与 SQL Server 实例建立的连接有关的信息以及每个连接的详细信息。net_packet_size  用于信息和数据的网络包的大小。可为 Null 值。num_reads   此连接中已发生的读包次数。可为 Null 值。num_writes  此连接中已发生的写数据包次数。可为 Null 值。session_id    标识与此连接关联的会话。可为 Null 值。

db_io

命令 SELECT SUM(fs.num_of_reads + fs.num_of_writes) AS [mssql_db_log_io] FROM sys.dm_io_virtual_file_stats( NULL, NULL) AS fs INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.file_id = f.file_id INNER JOIN sys.databases AS d ON d.database_id = f.database_id AND d.name IN (%s) GROUP BY d.name ORDER BY d.name解释sys.dm_io_virtual_file_stats     返回数据和日志文件的 I/O 统计信息。num_of_reads     对文件发出的读取次数。num_of_writes    在该文件中写入的次数。sys.master_files  存储在 master 数据库中的每个数据库文件都在表中占用一行。database_id 应用此文件的数据库的 ID。 master database_id 始终为 1。file_id    数据库内文件的 ID。主 file_id 始终为 1sys.databases     实例中的每个数据库都对应一行 name     数据库名称

activesession

命令 select COUNT(*) from sys.dm_exec_sessionswhere login_name not in ( 'root', 'aurora', 'sa', 'eagleye') and login_name not like 's\_%' escape '\' and login_name not like '%mssqld' and login_name not like '%Administrator' and status not in ( 'Preconnect', 'Dormant', 'Sleeping') and session_id > 50解释比之前的session监控多了Sleeping和session_id> 50的过滤条件Sleeping      当前没有运行任何请求经验activesession过高一般业务都会出现卡慢，但这个值只是一个结果表现，具体导致原因还需要其它进一步排查

connectionreset

   命令 SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'MSSQL$MS%s:General Statistics' AND counter_name = 'Connection Resets/sec'解释 Connection Resets/sec    从连接池启动的登录总次数。