您的位置:1010cc时时彩经典版 > 1010cc安卓版 > 1010cc时时彩经典版:数据库优化,性能分析报表

1010cc时时彩经典版:数据库优化,性能分析报表

发布时间:2019-10-06 21:50编辑:1010cc安卓版浏览(85)

     一.概念

       SOS_SCHEDULER_YIELD等待类型是一个任务自愿丢掉当前的能源占用,让给别的义务使用。   那些等待类型与CPU有平昔关联,与内部存款和储蓄器与也是有直接关联,与CPU有关系是因为在sql server里是通过任务调节SCHEDULE奥迪Q5来涉及CPU。 通过SCHEDULE奥迪Q7下的Worker线程来拍卖SQL职分。为何跟内部存款和储蓄器有关系啊,是因为获取的财富须要内存来承载。 
      Yelding的爆发:是指SCHEDULE奔驰G级上运维的Worker都以非抢占式的, 在 SCHEDULE大切诺基上Worker由于财富等待,让出当前Worker给另外Worker就叫Yielding。 关于SCHEDULEEnclave_YIELD爆发的准绳查看  sqlserver 职责调解与CPU。SOS_SCHEDULER_YIELD 等待的状态可以精通到:

      (1)CPU有压力

      (2) SQL Server CPU scheduler 使用合适管理就能功用高。

    1.1 从实例等第来查阅等待数

    select wait_type,
    waiting_tasks_count,
    wait_time_ms ,
    max_wait_time_ms,
    signal_wait_time_ms
    from sys.dm_os_wait_stats
    where wait_type like 'SOS_SCHEDULER_YIELD%' 
    order by wait_type
    

      查询如下图所示: 

    1010cc时时彩经典版 1

      那几个等待类型排行第二,从呼吁的次数来说有693670伍拾伍次,也正是说该线程用完了4ms的日子片,主动扬弃cpu。假如未有大气的runnable队列可能大量的signal wait,注解不自然是cpu难点。因为那四个目的是cpu压力的二个反映。要求检查施行安插中是或不是存在一大波扫描操作。

    1.2 通过dmv scheaduler的描述查看cpu压力

    SELECT scheduler_id, current_tasks_count, runnable_tasks_count, work_queue_count, pending_disk_io_count
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255
    

      如下图所示:

    1010cc时时彩经典版 2

      假设您放在心上到runnable_tasks_count计数有两位数,持续不短日子(一段时间内),你就能够分晓CPU压力。两位数字经常被以为是一件坏事 不可能应对近些日子负荷。其他能够经过质量监视器%Processor Time 来查阅CPU的气象。

    1.3 通过案例实时查看sql语句级的能源等待

    SELECT * FROM sys.dm_exec_requests  WHERE wait_type LIKE 'SOS_SCHEDULER_YIELD%'
    

      -- 或探究财富等待的
      SELECT session_id ,status ,blocking_session_id
      ,wait_type ,wait_time ,wait_resource
      ,transaction_id
      FROM sys.dm_exec_requests
      WHERE status = N'suspended';

      如下图所示 运维sys.dm_exec_requests 表,由于字段多截取了三断。会话202的sql 语句上叁次等待类型是SOS_SCHEDULER_YIELD。之所以会冒出YIELD,是因为SCHEDULE君越下的Worker已经发起了task 命令,但鉴于财富等待 如锁恐怕磁盘输入/输出等,Worker又是非抢占式,所以让出了当下的Worker。

    1010cc时时彩经典版 3

    1010cc时时彩经典版 4

    1010cc时时彩经典版 5

    1.4 减少sos_scheduler_yield 等待

      正如下边所钻探的,这种等待类型与CPU压力有关。扩充更多CPU是粗略的缓慢解决方案,然则达成这么些建设方案并不轻便。当以此等待类型异常高时,你能够设想其余的作业。这里经过从缓存中找到与CPU相关的最昂贵的SQL语句。

    --查询编写翻译以来 cpu耗费时间总的数量最多的前50条(Total_woker_time) 第一种查询
    select
    'total_worker_time(ms)'=(total_worker_time/1000),
    q.[text], --DB_NAME(dbid),OBJECT_NAME(objectid),
    execution_count,
    'max_worker_time(ms)'=(max_worker_time/1000),
    'last_worker_time(ms)'=(last_worker_time/1000),
    'min_worker_time(ms)'=(min_worker_time/1000),
    'max_elapsed_time(ms)'=(max_elapsed_time/1000),
    'min_elapsed_time(ms)'=(min_elapsed_time/1000),
    'last_elapsed_time(ms)'=(last_elapsed_time/1000),
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,
    max_physical_reads,
    total_logical_reads,
    last_logical_reads,
    max_logical_reads,
    creation_time,
    last_execution_time
    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(highest_cpu_queries.plan_handle) as q
    order by highest_cpu_queries.total_worker_time DESC

     

    试应用环境:SQL2008 R2、SQL2012、SQL2014
    
    [sql] view plaincopy
    --语句1:获取前20逻辑读取次数或逻辑写入次数或CPU 时间  
    SELECT TOP 20 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.total_logical_writes, qs.last_logical_writes,  
    qs.total_worker_time,  
    qs.last_worker_time,  
    qs.total_elapsed_time/1000000 total_elapsed_time_in_S,  
    qs.last_elapsed_time/1000000 last_elapsed_time_in_S,  
    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  
    ORDER BY qs.total_logical_reads DESC -- 逻辑读取次数  
     --ORDER BY qs.total_logical_writes DESC -- 逻辑写入次数  
     --ORDER BY qs.total_worker_time DESC -- CPU 时间  
    
    
    --语句2:获取前20执行的 SP 命令的总工作时间 (CPU 压力)  
        SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime',   
        qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
        qs.execution_count AS 'Execution Count',   
        ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',  
        ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',   
        qs.max_logical_reads, qs.max_logical_writes,   
        DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'  
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        WHERE qt.dbid = db_id() -- 当前数据库  
        ORDER BY qs.total_worker_time DESC  
    
    --语句3: 获取前20 执行的 SP 命令逻辑写入/分钟  
        SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites',  
        qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min',    
        qs.execution_count AS 'Execution Count',   
        qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',   
        qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
        qs.total_worker_time AS 'TotalWorkerTime',  
        qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',  
        qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,   
        DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',  
        qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid  
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        WHERE qt.dbid = db_id() -- 当前数据库  
        ORDER BY qs.total_logical_writes DESC  
    
    --语句4: 获取前20执行的 SP 命令的逻辑读取(内存压力)   
        SELECT TOP 20 qt.text AS 'SP Name', total_logical_reads,   
        qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads',  
        qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',   
        qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
        qs.total_worker_time AS 'TotalWorkerTime',  
        qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',  
        qs.total_logical_writes,  
        qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,   
        DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid   
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        WHERE qt.dbid = db_id() -- 当前数据库  
        ORDER BY total_logical_reads DESC  
    
    --语句5: 获取前20执行的 SP 命令由物理读取 (读取 I/O 压力)  
        SELECT TOP 20 qt.text AS 'SP Name', qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads',  
        qs.execution_count AS 'Execution Count',  
        qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',    
        qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
        qs.total_worker_time AS 'TotalWorkerTime',  
        qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',  
        qs.max_logical_reads, qs.max_logical_writes,    
        DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid   
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        WHERE qt.dbid = db_id() -- 当前数据库  
        ORDER BY qs.total_physical_reads DESC  
    
    --语句6: 获取前20执行的 SP 命令执行计数  
        SELECT TOP 20 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',    
        qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second',  
        qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',  
        qs.total_worker_time AS 'TotalWorkerTime',  
        qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',  
        qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads,   
        DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'  
        FROM sys.dm_exec_query_stats AS qs  
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt  
        WHERE qt.dbid = db_id() -- Filter by current database  
        ORDER BY qs.execution_count DESC  
    
    查看10秒时间内存储过程执行次数和CPU时间
    [sql] view plaincopy在CODE上查看代码片派生到我的代码片
    SELECT DB_NAME(st.dbid) DBName  
          ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName  
          ,OBJECT_NAME(st.objectid,dbid) StoredProcedure  
          ,max(cp.usecounts) Execution_count  
          ,sum(qs.total_worker_time) total_cpu_time  
          ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time  
     into #temp  
     FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle  
          CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st  
     where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'  
     group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)   
     order by sum(qs.total_worker_time) desc  
    
    WAITFOR DELAY '00:00:10'   
    
    SELECT DB_NAME(st.dbid) DBName  
          ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName  
          ,OBJECT_NAME(st.objectid,dbid) StoredProcedure  
          ,max(cp.usecounts) Execution_count  
          ,sum(qs.total_worker_time) total_cpu_time  
          ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0)  avg_cpu_time  
     into #temp2  
     FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle  
          CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st  
     where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'  
     group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid)   
     order by sum(qs.total_worker_time) desc  
    
    SELECT a.DBNAMe, a.SchemaName, a.StoredProcedure,  
    b.Execution_count - a.Execution_count as ExecCnt,  
    b.total_cpu_time - a.total_cpu_time as CPU   
    FROM #temp a inner join #temp2 b on a.DBName = b.DBname and a.SchemaName = b.SchemaName and a.StoredProcedure = b.StoredProcedure  
    ORDER BY 5 desc  
    
    drop table #temp  
    drop table #temp2   
    

     

    SELECT  creation_time  N'语句编写翻译时间'
            ,last_execution_time  N'上次奉行时间'
            ,total_physical_reads N'物理读取总次数'
            ,total_logical_reads/execution_count N'每一趟逻辑读次数'
            ,total_logical_reads  N'逻辑读取总次数'
            ,total_logical_writes N'逻辑写入总次数'
            , execution_count  N'试行次数'
            , total_worker_time/1000 N'所用的CPU总时间ms'
            , total_elapsed_time/1000  N'总开支时间ms'
            , (total_elapsed_time / execution_count)/一千  N'平均时间ms'
            ,SUBSTRING(st.text, (qs.statement_start_offset/2) 1,
             ((CASE statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END
                - qs.statement_start_offset)/2) 1) N'试行语句'
    FROM dm_exec_query_stats AS qs
    CROSS APPLY dm_exec_sql_text(qs.sql_handle) st
    where SUBSTRING(st.text, (qs.statement_start_offset/2) 1,
             ((CASE statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END
                - qs.statement_start_offset)/2) 1) not like '

    本文由1010cc时时彩经典版发布于1010cc安卓版,转载请注明出处:1010cc时时彩经典版:数据库优化,性能分析报表

    关键词:

上一篇:mysql参数调优,讲的挺周全

下一篇:没有了