sqlserver常用运维sql

Myth丶恋晨 2023-12-08 06:48 247阅读 0赞

`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'`

sqlserver常用运维sql

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 sqlserver常用运维sql

相关 SQL Server运维常用语句

相关软件运维常用工具

相关 linux运维常用命令

相关运维之常用运维工具

相关运维常用命令集合

相关 linux运维常用指令

相关运维人员常用工具

相关运维常用对联

相关 SQLServer常用运维SQL整理