SQL Server代理作业的巨大性能飞跃：从2天到2小时的调优

SELECT sj.name AS [Name],
   sh.step_id,
   sh.step_name AS [StepName],
   DATETIMEFROMPARTS(
       LEFT(padded_run_date, 4),         -- year
       SUBSTRING(padded_run_date, 5, 2), -- month
       RIGHT(padded_run_date, 2),        -- day
       LEFT(padded_run_time, 2),         -- hour
       SUBSTRING(padded_run_time, 3, 2), -- minute
       RIGHT(padded_run_time, 2),        -- second
       0) AS [LastRunDateTime],          -- millisecond
   CASE
       WHEN sh.run_duration > 235959
           THEN CAST((CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) / 24) AS VARCHAR) + '.' + RIGHT('00' + CAST(CAST(LEFT(CAST(sh.run_duration AS VARCHAR), LEN(CAST(sh.run_duration AS VARCHAR)) - 4) AS INT) % 24 AS VARCHAR), 2) + ':' + STUFF(CAST(RIGHT(CAST(sh.run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':')
       ELSE STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(sh.run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
       END AS [LastRunDuration (d.HH:MM:SS)],
       sh.run_status,
       sh.message,
       sh.server,
       jstep.command
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sh
   ON sj.job_id = sh.job_id
left JOIN  [msdb].[dbo].[sysjobsteps] AS [jstep]
   ON sj.job_id = jstep.job_id and  sh.step_id=jstep.step_id
CROSS APPLY (
   SELECT RIGHT('000000' + CAST(sh.run_time AS VARCHAR(6)), 6),
       RIGHT('00000000' + CAST(sh.run_date AS VARCHAR(8)), 8)
   ) AS shp(padded_run_time, padded_run_date)
 where name='ACT_JOB'
 and sh.run_duration>='6000'  -- 执行时长大于60分钟
 -- and instance_id>=557203
 order by instance_id desc
GO

-- 数据库中碎片率大于30%的所有索引
SELECT db_name() dbname,
       OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS tb_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       CAST(ips.avg_fragmentation_in_percent AS DECIMAL(5, 2)) avg_fragmentation_in_percent, -- 索引碎片总计百分比，大于30%就得重建
       CAST(ips.avg_page_space_used_in_percent AS DECIMAL(5, 2)) avg_page_space_used_in_percent, -- 索引页填充度,小于70%就得重建
       ips.index_depth,
       ips.page_count,
       ips.fragment_count,
       ips.alloc_unit_type_desc,
       'ALTER INDEX '+ i.name +' ON  '+ OBJECT_NAME(ips.object_id) +'  REBUILD WITH (MAXDOP=16);' index_rebuild
FROM (
select * from  
sys.dm_db_index_physical_stats(db_id(), null, NULL, NULL, NULL )  a
 where a.index_level=0 and  alloc_unit_type_desc='IN_ROW_DATA'
 )AS ips
LEFT JOIN sys.indexes AS i
ON (ips.object_id = i.object_id AND ips.index_id = i.index_id and ips.index_level=0)
where i.name is not null
and  ips.avg_fragmentation_in_percent > 30
ORDER BY page_count DESC;

-- 缺失索引
select * from (
SELECT TOP 20
    CONVERT (decimal (28, 1),  migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)   ) AS estimated_improvement,
    migs.unique_compiles,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.last_user_scan,
    migs.avg_user_impact,
    mid.database_id,
    mid.object_id,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    mid.statement,
    'CREATE INDEX idx_missing_index_' + CONVERT(varchar,mid.database_id)  + '_' + 
        CONVERT(varchar,mid.object_id)  + '_' +
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') +' with (maxdop=32);'  AS create_index_statement
FROM sys.dm_db_missing_index_groups mig  WITH(NOLOCK) 
JOIN sys.dm_db_missing_index_group_stats migs  WITH(NOLOCK) 
   on  migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid  WITH(NOLOCK)
   on  mig.index_handle = mid.index_handle
where 1=1
 and migs.last_user_seek >='2024-01-30 13:40'
-- and migs.user_seeks>2
ORDER BY estimated_improvement DESC
) aa
where estimated_improvement > 100
and avg_user_impact>30
order by  database_id,object_id,equality_columns,included_columns
GO

select * from sql3 where Duration>=6000000;

 select  min(timekey),max(timekey) from  FACT_AA
 select max(timekey)   from FACT_AA where timekey >= getdate()-30


select * into   FACT_AA_tmp  from FACT_AA where timekey >= getdate()-30

EXEC sp_rename 'FACT_AA', 'FACT_AA_bk';
EXEC sp_rename 'FACT_AA_tmp', 'FACT_AA';


-- SSMS右键获取创建索引的语句，创建索引。。。

-- 某个进程具体等待
SELECT sp.status,sp.cmd,sp.lastwaittype,sp.waitresource,sp.cpu,sp.physical_io,sp.spid,kpid,blocked,dbid
FROM master.dbo.sysprocesses sp WITH(NOLOCK) 
where sp.spid=126;