SQLServer性能数据解析-阿里云开发者社区

开发者社区> 数据库> 正文

SQLServer性能数据解析

简介: # 磁盘相关 ins_dir = MSSQL_DIR + "/ms" + str(port) data_dir = ins_dir + '/data' log_dir = ins_dir + '/log' backup_dir = ins_dir + '/backup

磁盘相关


          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_connections
          sys.dm_exec_sessions
   与系统有关
          sys.dm_os_performance_counters
   与IO相关
          sys.dm_io_virtual_file_stats

目录视图

   数据库和文件目录视图
          sys.master_files
          sys.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 ratio
          Buffer 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 始终为 1
          sys.databases     实例中的每个数据库都对应一行
          name     数据库名称
          

activesession

命令
          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', '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    从连接池启动的登录总次数。                                    
          

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章