在不涉及实时金额等对数据要求严格的查询语句上,加nolock查询,来减少对资源的锁定 如,select count(1) from tb (nolock)
一般来说如果阻塞时间非常短,那就对性能的影响不是很大。你首先得确认你的系统是不是由阻塞造成的,以及阻塞对你系统性能的影响究竟有多大--查询阻塞SELECT A.request_session_id, B.blocking_session_id, B.resource_address, B.wait_type, B.wait_duration_ms, A.resource_associated_entity_id, A.resource_type, A.request_type, D.[text], G.request_type, F.[text] FROM SYS.dm_tran_locks AS A INNER JOIN SYS.dm_os_waiting_tasks AS B ON A.lock_owner_address=B.resource_address INNER JOIN SYS.dm_exec_requests AS C ON B.session_id=A.request_session_id CROSS APPLY SYS.dm_exec_sql_text(C.sql_handle) AS D LEFT JOIN SYS.dm_exec_requests AS E ON E.session_id=B.blocking_session_id OUTER APPLY SYS.dm_exec_sql_text(E.sql_handle) AS F LEFT JOIN SYS.dm_tran_locks AS G ON E.session_id=G.request_session_id
如,select count(1) from tb (nolock)
B.blocking_session_id,
B.resource_address,
B.wait_type,
B.wait_duration_ms,
A.resource_associated_entity_id,
A.resource_type,
A.request_type,
D.[text],
G.request_type,
F.[text]
FROM SYS.dm_tran_locks AS A INNER JOIN
SYS.dm_os_waiting_tasks AS B ON A.lock_owner_address=B.resource_address INNER JOIN
SYS.dm_exec_requests AS C ON B.session_id=A.request_session_id CROSS APPLY
SYS.dm_exec_sql_text(C.sql_handle) AS D LEFT JOIN
SYS.dm_exec_requests AS E ON E.session_id=B.blocking_session_id OUTER APPLY
SYS.dm_exec_sql_text(E.sql_handle) AS F LEFT JOIN
SYS.dm_tran_locks AS G ON E.session_id=G.request_session_id
这个我怎么能知道是否是阻塞造成的呢?因为用户一直说系统慢,我就去查堵,但是堵都是些简单的sql语句,我就觉得很神奇,更神奇的是这个数据库几乎每分钟都有这种简单sql语句造成的堵,且很快消失。系统很多时候都慢得要死,重启服务器都不行