sqlserver常用运维sql

1. sqlserver 检测sql

 

    SELECT top 10 

        (total_elapsed_time / execution_count)/1000 N'平均时间ms' 

        ,total_elapsed_time/1000 N'总花费时间ms' 

        ,total_worker_time/1000 N'所用的CPU总时间ms' 

        ,total_physical_reads N'物理读取总次数' 

        ,total_logical_reads/execution_count N'每次逻辑读次数' 

        ,total_logical_reads N'逻辑读取总次数' 

        ,total_logical_writes N'逻辑写入总次数' 

        ,execution_count N'执行次数' 

        ,creation_time N'语句编译时间' 

        ,last_execution_time N'上次执行时间' 

        ,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'执行语句' 

        ,qp.query_plan 

    FROM  sys.dm_exec_query_stats AS qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 

    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  total_elapsed_time / execution_count DESC; 

 

     

2. 死锁

 

    select   

    request_session_id spid,  

    OBJECT_NAME(resource_associated_entity_id) tableName   

    from   

    sys.dm_tran_locks  

    where   

    resource_type='OBJECT'

 

    GRANT SELECT ON  sys.dm_tran_locks TO zabbix

 

3. 连接数

 

    SELECT count(1) as icount FROM

    [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT

    [DBID]

    FROM

    [Master].[dbo].[SYSDATABASES]

    WHERE

    NAME='JSTMBS'

    );

 

4. 数据文件大小

 

    select  convert(float,sum(size)) * (8192.0/1024.0)/1024.0/1024  from JSTMBS.dbo.sysfiles 

 

5. 查看作业 备份JSTMBS 的状态

 

    select top 1

    CASE HIST.run_status

        WHEN 0 THEN 'Failed' 

        WHEN 1 THEN 'Succeeded'  

        WHEN 2 THEN 'Retry'  

        WHEN 3 THEN 'Canceled'  

        END as status

    FROM msdb.dbo.sysjobs JOB

    INNER JOIN msdb.dbo.sysjobhistory HIST ON HIST.job_id = JOB.job_id

    WHERE

    JOB.name = '备份JSTMBS'

    and HIST.step_id =0

    ORDER BY HIST.run_date desc;

 

6. sqlserver 统计碎片

 

    select B.name,C.name,A.avg_fragmentation_in_percent from  sys.dm_db_index_physical_stats(DB_ID() ,object_id('WF_WorkItem') ,NULL,NULL,NULL) A,sysobjects B,sys.indexes  C

    where A.object_id = B.id  and A.object_id = C.object_id and C.index_id = A.index_id

    and C.name is not null

    and avg_fragmentation_in_percent > 90

 

 

7. 查看触发器

    SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id

    WHERE tb1.type='TR';

 

8. 调试触发器

思路：创建存储过程执行update语句，debug

-- 新建触发器

    Create TRIGGER [dbo].[OverloadCaseInfoForupdate] ON [dbo].[GL_OverloadCaseInfo]

    for update

    AS

    DECLARE @CaseId varchar(50),  @num int

    select @CaseId=CaseId from inserted

 

    select @num=count(CaseId) from GL_OverloadCaseInfo where CaseId=@CaseId and (IsAccept=0 or IsAccept=2)

 

    if @num = 0

        begin

            ROLLBACK TRANSACTION 

        end

 

-- 创建存储过程

    Create proc [dbo].[testtri]

    as

    begin

        update GL_OverloadCaseInfo set PlateColor='7' where CaseId='HZ9482f663ec7fb901643a9dbf163b9d'

    end

 

-- 调试

    点击对应的数据库，找到存储过程 testtri ，右键 执行存储过程，点击上面 “调试”，按F11,会进入触发器脚本。

 

 

9. 创建链接服务器

     

    EXEC sp_addlinkedserver 'DB name','','SQLOLEDB','xxx.xx.xx.xxx'

    EXEC sp_addlinkedsrvlogin 'DB name','false',NULL,'user','XXXXXX'