sql server 常用脚本（日常查询所需）

-- descr : a simple sql script to view sql server jobs run status
-- last_run_status  1:success 0:fail
　　select category = jc.name,
　　category_id = jc.category_id,
　　job_name = j.name,
　　job_enabled = j.enabled,
　　last_run_time = cast(js.last_run_date as varchar(10)) + ‘-‘ + cast(js.last_run_time as varchar(10)),
　　last_run_duration = js.last_run_duration,
　　last_run_status = js.last_run_outcome,
　　last_run_msg = js.last_outcome_message + cast(nullif(js.last_run_outcome,1) as varchar(2)),
　　job_created = j.date_created,
　　job_modified = j.date_modified
　　from msdb.dbo.sysjobs j
　　inner join msdb.dbo.sysjobservers js
　　on j.job_id = js.job_id
　　inner join msdb.dbo.syscategories jc
　　on j.category_id = jc.category_id
　　where j.enabled = 1
　　and js.last_run_outcome in (0,1,3,5) -- 0:Fail 1:Succ 3:Cancel 5:First run
　　and jc.category_id not between 10 and 20 -- repl
  --and js.last_run_outcome = 0  只用于查看失败的作业

    and js.last_run_outcome = 0

SELECT  ar.replica_server_name AS [副本名称] ,
       ar.availability_mode_desc as [同步模式],
        DB_NAME(dbr.database_id) AS [数据库名称] ,
        dbr.database_state_desc AS [数据库状态],
        dbr.synchronization_state_desc AS [同步状态],
        dbr.synchronization_health_desc AS [同步健康状态],
        ISNULL(CASE dbr.redo_rate
                 WHEN 0 THEN -1
                 ELSE CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rate
               END, -1) AS [Redo延迟(秒)] ,
        ISNULL(CASE dbr.log_send_rate
                 WHEN 0 THEN -1
                 ELSE CAST(dbr.log_send_queue_size AS FLOAT)
                      / dbr.log_send_rate
               END, -1) AS [Log传送延迟(秒)] ,
        dbr.redo_queue_size AS [Redo等待队列(KB)] ,
        dbr.redo_rate AS [Redo速率(KB/S)] ,
        dbr.log_send_queue_size AS [Log传送等待队列(KB)] ,
        dbr.log_send_rate AS [Log传送速率(KB\S)]
FROM    [master].sys.availability_replicas AS AR
        INNER JOIN [master].sys.dm_hadr_database_replica_states AS dbr 
        ON ar.replica_id = dbr.replica_id
WHERE   dbr.redo_queue_size IS NOT NULL

SELECT  creation_time  N‘语句编译时间‘
        ,last_execution_time  N‘上次执行时间‘
        ,execution_count  N‘执行次数‘
        ,case datediff(ss,creation_time,last_execution_time) when 0 then 0 
            else execution_count/datediff(ss,creation_time,last_execution_time) end N‘每秒执行次数‘
        ,total_physical_reads N‘物理读取总次数‘
        ,total_logical_reads/execution_count N‘每次逻辑读次数‘
        ,total_logical_reads  N‘逻辑读取总次数‘
        ,total_logical_writes N‘逻辑写入总次数‘
        , total_worker_time/1000 N‘所用的CPU总时间ms‘
        , total_elapsed_time/1000  N‘总花费时间ms‘
        , (total_elapsed_time / execution_count)/1000  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‘执行语句‘
        ,db_name(st.dbid) as dbname,st.objectid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.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 ‘%fetch%‘
ORDER BY   execution_count DESC;