MSSQL 运维

 

1.会话信息
select * from sys.dm_exec_sessions;
2.锁信息
select l.resource_type,l.resource_associated_entity_id,OBJECT_NAME(sp.OBJECT_ID) AS ObjectName
,l.request_status,l.request_mode,request_session_id,
l.resource_description
from sys.dm_tran_locks l
left join sys.partitions sp
on sp.hobt_id = l.resource_associated_entity_id
where l.resource_database_id=DB_ID();
3. 执行sql
DBCC INPUTBUFFER(sid)

4.部分业务受并行影响

USE DB_3000;
GO
EXEC sp_configure 'showadvancedoptions',1;
GO
RECONFIGURE WITHOVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism',1;
GO
RECONFIGUREWITHOVERRIDE;
GO

5.调整事务隔离级别

- 调整隔离级别
ALTER DATABASE [DB_3000] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE [DB_3000] SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE [DB_3000] SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE [DB_3000] SET MULTI_USER;

- 回退隔离级别
ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE ;
ALTER DATABASE [test] SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE [test] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [test] SET MULTI_USER;