DBA 需要知道N种对数据库性能的监控SQL语句

迈不过友情╰ 2022-06-14 05:25 109阅读 0赞

--DBA 需要知道N种对数据库性能的监控SQL语句
    
    -- IO问题的SQL内部分析
    下面的DMV查询可以来检查当前所有的等待累积值。
    Select  wait_type, 
            waiting_tasks_count, 
            wait_time_ms
    from     sys.dm_os_wait_stats  
    where    wait_type like 'PAGEIOLATCH%'  
    order by wait_type
    
    --可以通过运行下面的查询得到每个文件的信息，了解哪个文件经常要做读(num_of_reads/ num_of_bytes_read)，
    --哪个经常要做写(num_of_writes/ num_of_bytes_written)，哪个文件的读写经常要等待(io_stall_read_ms/ io_stall_write_ms/ io_stall)。
    select db.name as database_name, f.fileid as file_id,
    f.filename as file_name,
    i.num_of_reads, i.num_of_bytes_read, i.io_stall_read_ms, 
    i.num_of_writes, i.num_of_bytes_written, i.io_stall_write_ms, 
    i.io_stall, i.size_on_disk_bytes
    from sys.databases db inner join
    sys.sysaltfiles f on db.database_id = f.dbid
    inner join sys.dm_io_virtual_file_stats(NULL, NULL) i 
    on i.database_id = f.dbid and i.file_id = f.fileid
    
    --SQLOS的任务调度算法
    --SQL 2005和SQL 2008有个动态管理视图sys.dm_os_schedulers，可以反映当前每个scheduler的状态。
    SELECT
        scheduler_id,
        cpu_id,
        parent_node_id,
        current_tasks_count,
        runnable_tasks_count,
        current_workers_count,
        active_workers_count,
        work_queue_count
      FROM sys.dm_os_schedulers;
    
    -- SQL CPU 100%问题
    --使用DMV来分析SQL Server启动以来累计使用CPU资源最多的语句。例如下面的语句就可以列出前50名。
    select 
        highest_cpu_queries.*,q.dbid, 
        q.objectid, q.number, q.encrypted, q.[text]
    from 
        (select top 50 qs.*
        from sys.dm_exec_query_stats qs
        order by qs.total_worker_time desc) as highest_cpu_queries
        cross apply sys.dm_exec_sql_text(plan_handle) as q
    order by highest_cpu_queries.total_worker_time desc
    go
    
    --我们也可以找到最经常做重编译的存储过程。
    select top 25 sql_text.text, sql_handle, plan_generation_num,  execution_count,
        dbid,  objectid 
    from sys.dm_exec_query_stats a
        cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
    where plan_generation_num >1
    order by plan_generation_num desc
    go
    -- 返回最经常运行的100条语句
    SELECT TOP 100
            cp.cacheobjtype
            ,cp.usecounts
            ,cp.size_in_bytes  
            ,qs.statement_start_offset
            ,qs.statement_end_offset
            ,qt.dbid
            ,qt.objectid
            ,SUBSTRING(qt.text,qs.statement_start_offset/2, 
                (case when qs.statement_end_offset = -1 
                then len(convert(nvarchar(max), qt.text)) * 2 
                else qs.statement_end_offset end -qs.statement_start_offset)/2) 
            as statement
    FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
    inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
    where cp.plan_handle=qs.plan_handle
    and cp.usecounts>4
    ORDER BY [dbid],[Usecounts] DESC
    
    -- 返回最经常被修改的100个索引
    -- 通过它们的Database_id, object_id, index_id和partition_number
    -- 可以找到它们是哪个数据库上的哪个索引
    SELECT top 100 * 
    FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
    order by leaf_insert_count+leaf_delete_count+leaf_update_count desc
    GO
    
    
    -- 返回做IO数目最多的50条语句以及它们的执行计划
    select top 50 
        (total_logical_reads/execution_count) as avg_logical_reads,
        (total_logical_writes/execution_count) as avg_logical_writes,
        (total_physical_reads/execution_count) as avg_phys_reads,
         Execution_count, 
        statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset,
    substring(sql_text.text, (statement_start_offset/2), 
    case 
    when (statement_end_offset -statement_start_offset)/2 <=0 then 64000
    else (statement_end_offset -statement_start_offset)/2
    end) as exec_statement,  
    sql_text.text,
    plan_text.*
    from sys.dm_exec_query_stats  
    cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
    cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
    order by 
     (total_logical_reads + total_logical_writes) /Execution_count Desc
    go
    
    -- CPU
    -- 计算signal wait占整wait时间的百分比
    select convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms)) 
    from Sys.dm_os_wait_stats
    
    --性能计数对象SQLServer:SQL Statistics下面有几个计数器，可以计算出大致的执行计划重用率。计算方法是：
    --Initial Compilations = SQL Compilations/sec – SQL Re-Compilations/sec
    --执行计划重用率= (Batch requests/sec – Initial Compilations/sec) / Batch requests/sec
    
    
    -- 计算'Cxpacket'占整wait时间的百分比
    declare @Cxpacket bigint
    declare @Sumwaits bigint
    select @Cxpacket = wait_time_ms
    from Sys.dm_os_wait_stats
    where wait_type = 'Cxpacket'
    select @Sumwaits = sum(wait_time_ms)
    from Sys.dm_os_wait_stats
    select convert(numeric(5,4),@Cxpacket/@Sumwaits)
    
    -- 阻塞：
    -- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率
    declare @dbid int
    select @dbid = db_id()
    Select dbid=database_id, objectname=object_name(s.object_id)
    , indexname=i.name, i.index_id    --, partition_number
    , row_lock_count, row_lock_wait_count
    , [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
    , row_lock_wait_in_ms
    , [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
    from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s,     sys.indexes i
    where objectproperty(s.object_id,'IsUserTable') = 1
    and i.object_id = s.object_id
    and i.index_id = s.index_id
    order by row_lock_wait_count desc
    
    -- 返回当前数据库所有碎片率大于25%的索引
    -- 运行本语句会扫描很多数据页面
    -- 避免在系统负载比较高时运行
    declare @dbid int
    select @dbid = db_id()
    SELECT * FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL)
    where avg_fragmentation_in_percent>25
    order by avg_fragmentation_in_percent desc
    GO
    
    -- 当前数据库可能缺少的索引
    select d.*
            , s.avg_total_user_cost
            , s.avg_user_impact
            , s.last_user_seek
            ,s.unique_compiles
    from sys.dm_db_missing_index_group_stats s
            ,sys.dm_db_missing_index_groups g
            ,sys.dm_db_missing_index_details d
    where s.group_handle = g.index_group_handle
    and d.index_handle = g.index_handle
    order by s.avg_user_impact desc
    go
    ---查找未使用过的索引
    SELECT TOP 1000
    o.name AS 表名
    , i.name AS 索引名
    , i.index_id AS 索引id
    , dm_ius.user_seeks AS 搜索次数
    , dm_ius.user_scans AS 扫描次数
    , dm_ius.user_lookups AS 查找次数
    , dm_ius.user_updates AS 更新次数
    , p.TableRows as 表行数
    , 'DROP INDEX ' + QUOTENAME(i.name)
    + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS '删除语句'
    FROM sys.dm_db_index_usage_stats dm_ius
    INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
    INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
    FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
    ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
    WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
    AND dm_ius.database_id = DB_ID()
    AND i.type_desc = 'nonclustered'
    AND i.is_primary_key = 0
    AND i.is_unique_constraint = 0
    and o.name='t_goods'   --根据实际修改表名
    ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
    
    /*
    user_updates很大，而发现user_seeks和user_scans很少或者就是0，那就说明该索引一直在更新，
    但是从来不被使用，仅仅创建和修改，没有为查询提供任何帮助，就可以考虑删除了
    */
    --得到按照执行时间排序的前10 的存储过程的执行信息：
    
    SELECT TOP 10 a.object_id, a.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    
    a.cached_time, a.last_execution_time, a.total_elapsed_time, a.total_elapsed_time/a.execution_count AS [avg_elapsed_time],
    
    a.execution_count,
    
    a.total_physical_reads/a.execution_count avg_physical_reads,
    
    a.total_logical_writes,
    
    a.total_logical_writes/ a.execution_count  avg_logical_reads,
    
    a.last_elapsed_time,
    
    a.total_elapsed_time / a.execution_count   avg_elapsed_time,
    
    b.text,c.query_plan 
    
    FROM sys.dm_exec_procedure_stats AS a
    
    CROSS APPLY sys.dm_exec_sql_text(a.sql_handle)  b
    
    CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c
    
    ORDER BY [total_worker_time] DESC;
    
    --100个io读取开销最大的语句
    SELECT TOP 100 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
     
      ((CASE qs.statement_end_offset
    
      WHEN -1 THEN DATALENGTH(qt.text)
    
      ELSE qs.statement_end_offset
    
      END - qs.statement_start_offset)/2)+1), 
    
      qs.execution_count, 
    
      qs.total_logical_reads, qs.last_logical_reads,
    
      qs.min_logical_reads, qs.max_logical_reads,
    
      qs.total_elapsed_time, qs.last_elapsed_time,
    
      qs.min_elapsed_time, qs.max_elapsed_time,
    
      qs.last_execution_time,
    
      qp.query_plan
     
    FROM sys.dm_exec_query_stats qs
     
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
     
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
     
    WHERE qt.encrypted=0
     
    ORDER BY qs.total_logical_reads DESC