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'
