设置数据库为SINGLE_USER模式,减少锁定时间

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON
ALTER DATABASE dbname SET MULTI_USER
 

当数据库处于read committed隔离级别时,与Oracle数据库中的效果相同,这时,update操作的旧数据会保存在tempdb中称为version store的部分,在Oracle数据库,这些旧数据会保存在undo表空间。只要事务不提交,其他连接可以看到update之前的旧数据。下面查询中的version_store_reserved_page_count字段可以得到tempdb中version store所占的空间大小:

select * from sys.dm_db_file_space_usage

 

--查看数据库的事务隔离相关参数
select name,
       user_access,
       user_access_desc,
       snapshot_isolation_state,
	   snapshot_isolation_state_desc,
       is_read_committed_snapshot_on
from sys.databases

--ALTER READ_COMMITTED_SNAPSHOT
ALTER DATABASE MES_WORKBECH_TD SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MES_WORKBECH_TD SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE MES_WORKBECH_TD SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE MES_WORKBECH_TD SET MULTI_USER;


alter database MES_WORKBECH_LOG SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
alter database MES_WORKBECH_LOG SET ALLOW_SNAPSHOT_ISOLATION ON
alter database MES_WORKBECH_LOG SET READ_COMMITTED_SNAPSHOT ON;
alter database MES_WORKBECH_LOG SET MULTI_USER

ALTER DATABASE MES_WORKBECH_LOG SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MES_WORKBECH_LOG SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE MES_WORKBECH_LOG SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE MES_WORKBECH_LOG SET MULTI_USER;

  

 
posted on 2020-09-01 09:36  湖东  阅读(3526)  评论(0编辑  收藏  举报