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'
如果对您有帮助,轻轻点个赞!