【澳门金沙vip】查看Job执行的历史记录

在Disk-Base数据库中,由于临时表和表变量的数据存储在tempdb中,如果系统频繁地创建和更新临时表和表变量,大量的IO操作集中在tempdb中,tempdb很可能成为系统性能的瓶颈。在SQL
Server
2016的内存(Memory-Optimized)数据库中,如果考虑使用内存优化结构来存储临时表,表变量,表值参数的数据,那么将完全消除IO操作的负载消耗,发挥大内存的优势,大幅提高数据库的性能。

SQL Server将Job的信息存放在msdb中,Schema是dbo,表名以“sysjob”开头。

在SQL Server
2016中,能够直接创建内存优化的表类型,表变量和表值参数的数据只存储在内存中;不能直接在内存中创建临时表,但是,SQL
Server提供一个变通方法(Workaround),通过行级安全RLS(Row-Level-Security)控制,指定只有当前Session才能访问特定的数据,将内存优化表转换为Session级别的临时表,间接实现临时表的局部性和自动清空特性。

一,基础表

一,内存优化表类型(Memory-Optimized Table Type)

1, 查看Job和Step,Step_ID 是从1 开始的。

内存优化表类型定义的表变量,表值参数能够大幅提高效率(efficiency),有4个显著的特点:

select j.job_id,j.name,j.enabled,j.description,
    j.start_step_id,j.date_created,j.date_modified
from msdb.dbo.sysjobs j with(nolock)
where name =N'xxx'
  • 数据仅存储在内存中,在读写数据时,不会产生任何的IO消耗,消除了tempdb的竞争和利用率;
  • 必须有一个索引,Hash 或 Nonclustered
    都行;每一个内存优化表必须创建一个索引;
  • 只需要指定启用内存优化:MEMORY_OPTIMIZED = ON,只持久化Schema;
  • 必须先创建表类型,后创建表值变量;

2, 查看 特定job 的所有 Step的执行记录,Step_id=0
记录job的整体执行情况;run_time 和 run_duration
是int类型,格式是hhmmss。

1,创建内存优化表类型

select jh.instance_id,jh.job_id,jh.step_id,jh.step_name,jh.sql_message_id,jh.sql_severity,
    jh.message,
    case jh.run_status
        when 0 then 'failed'
        when 1 then 'Succeeded'
        when 2 then 'Retry'
        when 3 then 'Canceled'
    end as run_status,
    jh.run_date,jh.run_time,jh.run_duration
from msdb.dbo.sysjobhistory jh with(nolock)
where job_id=N'07A53839-E012-4C80-9227-15594165B013'
order by instance_id desc
CREATE TYPE dbo.TypeTable  
AS TABLE  
(  
Column1  INT NOT NULL,  
Column2  VARCHAR(10) NOT NULL,
INDEX idxName NONCLUSTERED(Column1)
)  
WITH(MEMORY_OPTIMIZED = ON); 

3,Job History的查询

2,创建内存优化表变量

use msdb
go

--查看job 最后一次执行的情况
DECLARE @Job_ID uniqueidentifier;

select @Job_ID=j.job_id
from msdb.dbo.sysjobs j with(nolock)
where j.name=N'job name'

;with cte as
(
select jh.job_id,
    jh.run_date,
    jh.run_time,
    jh.run_status,
    ROW_NUMBER() over(PARTITION by jh.job_id order by jh.run_date desc,jh.run_time desc) as rid
from msdb.dbo.sysjobhistory jh with(NOLOCK)
where jh.step_id=0
    and jh.job_id=@Job_ID
)
select j.name as JobName,
    jh.step_id,
    jh.step_name,
    case jh.run_status 
        when 0 then 'Failed' 
        when 1 then 'Successed' 
        when 2 then 'Retry' 
        when 3 then 'Canceled' 
        else N'' 
    end as StepStatus,
    jh.message,
    cast(STUFF(STUFF(str(jh.run_date,8),7,0,N'-'),5,0,N'-') + N' ' + 
         STUFF(STUFF(REPLACE(STR(jh.run_time,6,0),N' ',N'0'),5,0,N':'),3,0,N':') 
         AS DATETIME) as [StartTime],
    stuff(stuff(replace(str(jh.run_duration,6),N' ',N'0'),5,0,N':'),3,0,N':')  as Duration
from  msdb.dbo.sysjobs j with(nolock)
inner join msdb.dbo.sysjobhistory jh with(nolock)
    on jh.job_id=j.job_id
inner join cte as c
    on c.job_id=jh.job_id and jh.run_date>=c.run_date and jh.run_time>=c.run_time and jh.step_id>0
where c.rid=1
order by jh.step_id asc
declare @Table dbo.TypeTable 

4,通过msdb.dbo.sysjobsteps 查看指定Job中每个step 最后执行的状态

二,创建“临时内存优化表”

select js.job_id,js.step_id,js.step_name,
    js.subsystem,js.command,
    js.last_run_outcome,--Last Run Result
    js.last_run_duration,
    js.last_run_date,
    js.last_run_time,
    js.last_run_retries
from msdb.dbo.sysjobsteps js with(nolock)
where js.job_id=N'DF0C68ED-7C76-4571-A72D-CD6161EFFC04'

在Disk-Base数据库中,局部临时表#temp的作用域是session,创建在tempdb中,一旦session生命周期结束,系统自动回收其存储空间。在SQL
Server
2016中,不能直接在tempdb中创建内存优化表。要使用临时内存优化表,有一个变通的方法,在DB中创建内存优化表,通过Row-Level-Security控制Session能够访问的数据行,间接实现Session级别的临时表。

5,查看每个Job最后一次执行的状态和该job最后一个Step的执行信息。

Step1,创建内存优化表,只持久化Table Schema

use msdb
GO

;with cte_job as
(
select jh.server,
    j.name,
    j.enabled ,
    jh.job_id,
    jh.run_status,
    jh.run_date,
    jh.run_time,
    jh.run_duration,
    ROW_NUMBER() OVER(PARTITION by jh.job_id order by jh.run_date desc,jh.run_time desc ) as rid
from msdb.dbo.sysjobs  j with(nolock)
inner join msdb.dbo.sysjobhistory jh with(nolock)
    on j.job_id=jh.job_id
where j.category_id=0
    and jh.step_id=0
)
SELECT j.name,
    j.enabled,
    case j.run_status 
        when 0 then 'Failed' 
        when 1 then 'Successed' 
        when 2 then 'Retry' 
        when 3 then 'Canceled' 
        else '' 
    end as [Status],
    cast(STUFF(STUFF(str(j.run_date,8),7,0,N'-'),5,0,N'-') + N' ' + 
         STUFF(STUFF(REPLACE(STR(j.run_time,6,0),N' ',N'0'),5,0,N':'),3,0,N':') 
         AS DATETIME) as [StartTime],
    stuff(stuff(replace(str(j.run_duration,6),N' ',N'0'),5,0,N':'),3,0,N':')  as Duration,
    jh.step_id,
    jh.step_name ,
    case jh.run_status 
        when 0 then 'Failed' 
        when 1 then 'Successed' 
        when 2 then 'Retry' 
        when 3 then 'Canceled' 
        else N'' 
    end as StepStatus,
    jh.message as StepMessage
from cte_job j
outer apply
(
select top 1 jh.step_id,jh.step_name,jh.run_status,jh.run_date,jh.run_time,jh.run_duration,jh.message
from msdb.dbo.sysjobhistory jh with(nolock)
where j.job_id=jh.job_id and jh.step_id>0 and jh.run_date>=j.run_date and jh.run_time>=j.run_time
order by jh.step_id desc
) as jh
where j.Rid=1 --Last Execution
    and j.run_status in(0,2)  --0 = Failed, 2=retry
order by j.name
CREATE TABLE dbo.SessionTempTable  
(  
    Column1 INT NOT NULL,  
    Column2 NVARCHAR(4000) NULL,  
    SpidFilter SMALLINT NOT NULL DEFAULT (@@spid),  
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    --INDEX ix_SpidFilter HASH (SpidFilter) WITH (BUCKET_COUNT = 64),  
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK ( SpidFilter = @@spid ),  
)  
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);  
go 

二,查看Running jobs

Step2,创建RLS,控制用户只能访问当前Session的数据

Agent在运行时,会创建一个Session,并将current
SessionID存储在msdb.dbo.syssessions
中。Agent在执行每一个job时,都会将SessionID 和Job_ID 写入
msdb.dbo.sysjobactivity 中,因此 msdb.dbo.sysjobactivity 记录当前Agent
正在运行的每一个Job的信息(Job开始执行的时间,执行成功的最后一个StepID….),如果要查看Agent当前执行的Job,那么msdb.dbo.sysjobactivity的SessionID必须是当前Agent使用的SessionID。

推荐为Predicate function 和 Security
Policy创建单独的Schema,然后在该Schema下创建Predicate function 和
Security Policy,对于Predicate function必须使用
NATIVE_COMPLIATION选项创建。

1,基础表

create schema rls
authorization dbo;

CREATE FUNCTION rls.fn_SpidFilter
(@SpidFilter smallint)  
RETURNS TABLE  
WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
RETURN  
    SELECT 1 AS fn_SpidFilter  
    WHERE @SpidFilter = @@spid;  
go

CREATE SECURITY POLICY rls.soSessionC_SpidFilter_Policy  
ADD FILTER PREDICATE rls.fn_SpidFilter(SpidFilter)  
ON dbo.SessionTempTable  
WITH (STATE = ON);  
go 

msdb.dbo.syssessions 

Step3,使用内存优化临时表

Each time SQL Server Agent starts, it
creates a new session. SQL Server Agent uses sessions to preserve the
status of jobs when the SQL Server Agent service is restarted or stopped
unexpectedly. Each row of the syssessions table contains information
about one session. Use the sysjobactivity table to view the job
state at the end of each session. Every time the agent is started a new
session_id is added to the syssessions table.

  • 表名替换:使用 dbo.Temp 代替
    #Temp;
  • 不能创建和删除临时表
    • 移除代码“create table
      #temp”,使用“delete from
      dbo.Temp”子句取代,将旧数据清空;
    • 移除代码“drop table
      #temp”,建议使用 “delete
      from dbo.Temp” 子句,在当前Session结束前将当前Session产生的数据清空,节省内存空间;

msdb.dbo.sysjobactivity