金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 查看Job执行的历史记录,在内存中创建临时表和

查看Job执行的历史记录,在内存中创建临时表和

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-29 12:37

在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'
  • 金沙棋牌app手机下载,数据仅存储在内存中,在读写数据时,不会产生任何的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

虽然临时表的使用和管理有点麻烦,但是,这点麻烦和大幅的性能提升来比,微不足道,建议使用内存优化表来代替临时表,体验飞一般的速度。

Records current SQL Server Agent job activity and status. The column last_executed_step_id is the id of the last step completed.  If the job is on the first step it’s NULL.  So getting the current step is a simple formula of ISNULL(last_executed_step_id,0)+1.

三,维护

2,查看当前正在运行的Job

1,通过RLS来创建内存临时表,如何清理临时表占用的内存空间?

SELECT
    j.name AS job_name,
    ja.start_execution_date, 
    ISNULL(ja.last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM msdb.dbo.sysjobactivity ja with(nolock)
LEFT JOIN msdb.dbo.sysjobhistory jh with(nolock)
    ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j 
    ON ja.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps js
    ON ja.job_id = js.job_id
    AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
AND stop_execution_date is null;

试想出现异常,在当前Session将海量数据插入到临时表之后,Session异常终止,此时,Session没有来得及清空(Purge)临时表中的数据,这些数据仍然驻留在内存中。如果这种异常出现的频率很高,那么会导致内存优化表消耗大量的系统内存,必须有机制来定期清理临时表占用的内存空间。

 

step1,创建一个用户,RLSAdmin,授予db_owner的权限

参考文档:

--create User
create user RLSAdmin without login;
alter role db_owner
add member RLSAdmin;
go

A T-SQL query to get current job activity

step2,修改Predicate Function,如果用户是RLSAdmin,允许访问Base Table的所有数据行;

SQL Server Agent Tables (Transact-SQL)

--create predicate function
CREATE FUNCTION rls.fn_SpidFilter
(@SpidFilter smallint)  
RETURNS TABLE  
WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
RETURN  
    SELECT 1 AS fn_SpidFilter  
    WHERE @SpidFilter = @@spid or User_Name()='RLSAdmin';  
go

SQL Server Agent Tables (Transact-SQL)

step3,创建Schedule,定期检查数据库中的临时表,如果发现临时表中的存在未被清理的无效数据,那么删除该部分数据,释放内存。

execute as RLSAdmin;

delete temp
from dbo.SessionTempTable temp
left join sys.dm_exec_sessions s
    on temp.SpidFilter=s.session_id 
        and s<>@@spid 
        and s.session_id>50
where s.session_id is null; 

revert;

该脚本仅仅提供一种思路,在产品环境中,需要多测试,以防错误删除数据。

 

参考文档:

Memory-Optimized Table Variables

Faster temp table and table variable by using memory optimization

Improving temp table and table variable performance using memory optimization

CREATE TYPE (Transact-SQL)

Indexes for Memory-Optimized Tables

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:查看Job执行的历史记录,在内存中创建临时表和

关键词:

上一篇:年数据统计

下一篇:没有了