【SQL Server DBA】日常巡检语句3：特定监控(阻塞、top语句、索引、作业)

来源：互联网发布：软件开发公司名字编辑：程序博客网时间：2024/05/18 16:58

【SQL Server DBA】日常巡检语句3：特定监控(阻塞、top语句、索引、作业)

原创 2013年10月15日 17:49:13

1、查询阻塞信息、锁定了哪些资源

[sql] view plain copy
--1.查看阻塞信息  
select spid,loginame,waitresource from master..sysprocesses   
where blocked <> 0  
/*  
spid    loginame    waitresource  
53  ggg-PC\Administrator RID:1:1:1385:0  
*/  
  
  
  
--2.查看语句  
dbcc inputbuffer(53)  
/*  
eventInfo  
select * from xx  
*/  
  
  
  
--3.查看锁的信息  
exec sp_lock @spid1 = 53  
/*  
spid    dbid    ObjId   IndId   Type    Resource    Mode    Status  
53  1   1335727861  0   PAG 1:1385                              IS  GRANT  
53  1   1335727861  0   RID 1:1385:0                            S   WAIT  
53  1   1335727861  0   TAB                                     IS  GRANT  
*/  
  
--select OBJECT_ID(1335727861) as table_name  
  
  
  
--4.打开数据库  
select *  
from sysdatabases  
where dbid = 1  
/*  
name    dbid    sid mode    status  status2 crdate  reserved    category    cmptlevel   filename    version  
master  1   0x01    0   65544   1090520064  2003-04-08 09:13:36.390 1900-01-01 00:00:00.000 0   100 C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf  661  
*/  
  
  
  
--5.根据锁信息中的ObjId列：1335727861，找到了这个xx表  
select *  
from sysobjects  
where id = 1335727861  
/*  
name    id  xtype   uid info    status  base_schema_ver replinfo    parent_obj  crdate  ftcatid schema_ver  stats_schema_ver    type    userstat    sysstat indexdel    refdate version deltrig instrig updtrig seltrig category    cache  
xx  1335727861  U   1   0   0   0   0   0   2013-12-25 08:55:07.523 0   0   0   U   1   3   0   2013-12-25 08:55:07.523 0   0   0   0   0   0   0  
*/  

查找死锁

[sql] view plain copy
exec sp_who_lock    
/*    
create procedure sp_who_lock      
as      
begin      
    declare @spid int,@bl int,      
    @intTransactionCountOnEntry int,      
    @intRowcount int,      
    @intCountProperties int,      
    @intCounter int      
    create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)      
    IF @@ERROR<>0 RETURN @@ERROR      
    insert into #tmp_lock_who(spid,bl) select 0 ,blocked      
    from (select * from sys.sysprocesses where blocked>0 ) a       
    where not exists(select * from (select * from sys.sysprocesses where blocked>0 ) b       
    where a.blocked=spid)      
    union select spid,blocked from sys.sysprocesses where blocked>0      
    IF @@ERROR<>0 RETURN @@ERROR      
        -- 找到临时表的记录数      
        select @intCountProperties = Count(*),@intCounter = 1      
        from #tmp_lock_who      
    IF @@ERROR<>0 RETURN @@ERROR      
    if @intCountProperties=0      
    select '现在没有阻塞和死锁信息' as message      
    -- 循环开始      
    while @intCounter <= @intCountProperties      
    begin      
    -- 取第一条记录      
    select @spid = spid,@bl = bl      
    from #tmp_lock_who where id = @intCounter       
    begin      
    if @spid =0       
        select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'      
    else      
        select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'      
    DBCC INPUTBUFFER (@bl )      
    end      
    -- 循环指针下移      
    set @intCounter = @intCounter + 1      
    end      
    drop table #tmp_lock_who      
    return 0      
end       
*/   

查看进程中正在执行的SQL

[sql] view plain copy
dbcc inputbuffer(spid)    
  
exec sp_who3    
/*    
CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL )    
AS     
    BEGIN    
    
    
        SELECT  SPID = er.session_id ,    
                Status = ses.status ,    
                [Login] = ses.login_name ,    
                Host = ses.host_name ,    
                BlkBy = er.blocking_session_id ,    
                DBName = DB_NAME(er.database_id) ,    
                CommandType = er.command ,    
                SQLStatement = st.text ,    
                ObjectName = OBJECT_NAME(st.objectid) ,    
                ElapsedMS = er.total_elapsed_time ,    
                CPUTime = er.cpu_time ,    
                IOReads = er.logical_reads + er.reads ,    
                IOWrites = er.writes ,    
                LastWaitType = er.last_wait_type ,    
                StartTime = er.start_time ,    
                Protocol = con.net_transport ,    
                ConnectionWrites = con.num_writes ,    
                ConnectionReads = con.num_reads ,    
                ClientAddress = con.client_net_address ,    
                Authentication = con.auth_scheme    
        FROM    sys.dm_exec_requests er    
                OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st    
                LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id    
                LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id    
        WHERE   er.session_id > 50    
                AND @SessionID IS NULL    
                OR er.session_id = @SessionID    
        ORDER BY er.blocking_session_id DESC ,    
                er.session_id     
    
    
    END    
*/   

2、top 语句

[sql] view plain copy
--SQL Server启动以来累计使用CPU资源最多的语句。  
select   
    highest_cpu_queries.*,  
      
    q.dbid,   
    q.objectid,   
    q.number,   
    q.encrypted,  
     q.[text]  
from   
(  
    select top 10 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(plan_handle) as q  
--where text like '%%'  
order by highest_cpu_queries.total_worker_time desc  
  
  
  
--我们也可以找到最经常做编重新译的存储过程,也就是recompile过。  
select top 10    
    a.sql_handle,   
    a.plan_generation_num,    
    a.execution_count,  
      
    s.dbid,    
    s.objectid,  
    s.text   
from sys.dm_exec_query_stats a  
cross apply sys.dm_exec_sql_text(sql_handle) as s  
  
where plan_generation_num >1  
order by plan_generation_num desc  
  
  
  
--返回做IO数目最多的10条语句以及它们的执行计划  
select top 10   
      
    (qs.total_logical_reads / qs.execution_count) as avg_logical_reads,  
    (qs.total_logical_writes / qs.execution_count) as avg_logical_writes,  
    (qs.total_physical_reads / qs.execution_count) as avg_phys_reads,  
      
    qs.execution_count,   
       
    qs.statement_start_offset,  
    qs.statement_end_offset,  
      
    qt.dbid,  
    qt.objectid,  
      
    SUBSTRING(qt.text,  
              qs.statement_start_offset/2,   
              (case when qs.statement_end_offset = -1   
                         then len(convert(nvarchar(max), qt.text)) * 2   
                    else qs.statement_end_offset   
               end - qs.statement_start_offset  
               ) / 2  + 1  
              ) as statement      
from sys.dm_exec_query_stats qs  
cross apply sys.dm_exec_sql_text(sql_handle) as qt  
cross apply sys.dm_exec_query_plan(plan_handle) as q  
order by   
 (total_logical_reads + total_logical_writes) / Execution_count Desc  
  
  
  
--返回最经常运行的10条语句  
SELECT TOP 10  
    cp.cacheobjtype,  
      
    cp.usecounts,      --使用这个缓存的执行计划的次数  
    cp.size_in_bytes,  --缓存的执行计划使用的字节数  
      
    qs.execution_count,     --执行次数,与usecounts相等.  
    qs.plan_generation_num, --用来区分:重新编译语句和存储过程  
           
    qs.statement_start_offset,  
    qs.statement_end_offset,  
      
    qt.dbid,  
    qt.objectid,  
    SUBSTRING(qt.text,  
              qs.statement_start_offset/2,   
              (case when qs.statement_end_offset = -1   
                         then len(convert(nvarchar(max), qt.text)) * 2   
                    else qs.statement_end_offset   
               end - qs.statement_start_offset  
               ) / 2  + 1  
              ) as statement  
FROM sys.dm_exec_query_stats qs  
  
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt  
  
inner join sys.dm_exec_cached_plans as cp   
        on qs.plan_handle=cp.plan_handle  
           and cp.plan_handle=qs.plan_handle  
             
where cp.usecounts>4  
ORDER BY [dbid],[Usecounts] DESC  

3、索引

[sql] view plain copy
-- 当前数据库可能缺少的索引  
select   
    s.group_handle,     --标识缺失索引组,在服务器中是唯一的,一个索引组仅包含一个索引  
      
    s.unique_compiles,  --从索缺失索引组受益的不同查询的编译和重新编译数  
      
    s.user_seeks,      --如果用户查询使用了组中建议索引，所导致的查找次数  
    s.user_scans,      --如果用户查询使用了组中建议索引，所导致的扫描次数    
    s.last_user_seek,  --如果用户查询使用了组中建议索引，所导致上次查找的日期和时间  
    s.last_user_scan,  --如果用户查询使用了组中建议索引，所导致上次扫描的日期和时间  
    s.avg_total_user_cost, --如果用户查询使用了组中建议索引，所能减少的平均成本  
    s.avg_user_impact,     --如果实现了建议索引，那么用户查询的成本将按此百分比平均下降  
      
    s.system_seeks,    --如果系统查询（如自动统计信息查询）使用了组中建议索引，所导致的查找次数  
    s.system_scans,    --如果系统查询使用了组中建议索引，所导致的扫描次数  
    s.last_system_seek,      --如果系统查询使用了组中建议索引，所导致上次查找的日期和时间  
    s.last_system_scan,      --如果系统查询使用了组中建议索引，所导致上次扫描的日期和时间  
    s.avg_total_system_cost, --如果系统查询使用了组中建议索引，所能减少的平均成本  
    s.avg_system_impact,     --如果实现了建议索引，那么系统查询的成本将按此百分比平均下降  
      
      
    g.index_group_handle, --标识缺失索引组  
    g.index_handle,       --标识由index_group_handle组指定的缺失索引。一个索引组包含一个索引  
      
      
    d.index_handle,  
    d.database_id,       --标识索引缺失的表所在的数据库id  
    d.object_id,         --标识索引缺失的表  
    d.statement,  
      
    d.equality_columns,  --构成相等谓词的列的逗号分隔列表，如下：table.column = constant_value  
    d.inequality_columns,--构成不等谓词的列的逗号分隔列表，=之外的任何比较运算符都表示不等.  
                         --以下形式的谓词:table.column > constant_value  
    d.included_columns,  --用于查询的涵盖列的逗号分隔列表。有关涵盖列或包含列的详细信息  
      
    c.column_id,  
    c.column_name,  
    c.column_usage  /*  
                      EQUALITY:列提供一个表示相等的谓词，形式为：table.column = constant_value                  
                      INEQUALITY:列包含表示不等的谓词，形式为：table.column > constant_value  
                      INCLUDE:列不用于谓词赋值，但用于其他原因，例如包含一个查询。  
                      =之外的任何比较运算符都表示不等。  
                    */    
from sys.dm_db_missing_index_group_stats s    --缺失索引组的摘要信息，不包括空间索引  
inner join sys.dm_db_missing_index_groups g   --特定缺失索引组中包含的缺失索引（不含空间索引）信息  
        on s.group_handle = g.index_group_handle         
inner join sys.dm_db_missing_index_details d  --返回有关缺失索引的详细信息，不包括空间索引  
        on d.index_handle = g.index_handle  
cross apply sys.dm_db_missing_index_columns(d.index_handle) c  --缺少索引的表的列的信息  
order by s.avg_user_impact desc  
  
  
  
--索引的使用情况  
select DB_NAME(t.database_id)  dbname,  
       OBJECT_NAME(t.object_id) tablename,  
         
       i.name indexname,  
       t.user_seeks,  
       t.user_scans,  
       t.user_lookups,  
       t.user_updates  
from sys.dm_db_index_usage_stats t  
inner join sys.indexes i  
        on t.object_id = i.object_id and  
           t.index_id = i.index_id   
  
  
  
--修改次数最多的索引，通过Database_id,object_id,index_id和partition_number找是哪个数据库的哪个索引  
SELECT top 100 *   
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)  
order by leaf_insert_count +   
         leaf_delete_count +   
         leaf_update_count desc  
  
  
  
--返回当前数据库所有碎片率大于25%的索引  
declare @dbid int  
select @dbid = db_id()  
  
SELECT *   
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL)  
where avg_fragmentation_in_percent > 25  
  
order by avg_fragmentation_in_percent desc  
  
  
  
--页的分裂次数,可以适当加大fillfactor的值  
use master  
go  
  
select leaf_allocation_count,     --由于页拆分所引起的页分配的累积计数  
       nonleaf_allocation_count,  --叶级以上由页拆分引起的页分配的累积计数  
         
       leaf_page_merge_count,   --叶级页合并的累积计数  
       nonleaf_page_merge_count --叶级以上页合并的累积计数  
from sys.dm_db_index_operational_stats  
(  
db_id('数据库名'),object_id('数据库名.dbo.temp_lock'),1,null  
)  

4、如何用脚本获得sql server作业的执行情况

[sql] view plain copy
select j.name as job_name,  
         
       js.step_name,  
         
       h.run_date,  
       h.run_time,  
       h.run_duration,  
       h.server,  
         
       case run_status  
            when 0  then '失败'  
            when 1  then '成功'  
            when 2  then '重试'  
            when 3  then '取消'  
            when 4  then '正在进行'  
       end as run_status  
              
from msdb.dbo.sysjobhistory h  
  
inner join msdb.dbo.sysjobs j  
        on h.job_id = j.job_id  
          
inner join msdb.dbo.sysjobsteps js  
        on js.job_id = h.job_id  
           and js.step_id = h.step_id  
    

阅读全文

0 0