Изучаем блокировки на MSSQL Server

Административные скрипты MS SQL > Изучаем блокировки на MSSQL Server
01.08.2016 17:39:30


Наиболее часто встречающиеся слова в статье:

[varchar] [Resource] [Operating] [Изучаем] [блокировки] [sp_lock] [db_name] [''GRANT'']


Статья:


Operating lock on MSSQL Server

Изучаем блокировки на MSSQL Server

CREATE TABLE #t1 (spid int,[dbid] int,[ObjId] int ,IndId int,[Type] varchar(50),[Resource] varchar(50),[Mode] varchar(50), [Status] varchar(50) )
insert #t1
exec sp_lock
select spid,db_name([dbid]) as DB ,[Resource],[Mode],[Status] from #t1 where [Status]<>'GRANT'
select [Status],[Mode] , count(*) as [Col] from #t1 group by [Status],[Mode]
drop table #t1

 

Расширенное изучение :

CREATE TABLE #t1 (spid int,[dbid] int,[ObjId] int ,IndId int,[Type] varchar(50),[Resource] varchar(50),[Mode] varchar(50), [Status] varchar(50) )
insert #t1
exec sp_lock
-- Какие процессы ожидают захвата каких ресурсов.

select count(*) as LockCnt, left(o.name, 32 ) as ObjectName

, l.Status, l.Type, l.Mode, l.spid

, max(Resource) as SampleResource

, left(max(ss.loginame), 16 ) as loginname

, max(ss.cmd) as cmd

, max(ss.lastwaittype) as lastwaittype

, left(max(ss.program_name), 32 ) as program_name

, left(max(ss.hostname), 32) as hostname

, max(ss.program_name) as full_program_name

from #t1 l, sysobjects o with (nolock)

, master..sysprocesses ss with (nolock)

where o.id = l.ObjId and l.spid = ss.spid

and l.Status not in ( 'GRANT', 'CNVT')

and l.dbid = db_id()

group by o.name, l.Status, l.Type, l.Mode, l.spid

-- Кто мешает выполняться процессам из предыдущего запроса, кто держит их ресурсы.

select count(*) as LockCnt, left(o.name, 32 ) as ObjectName

, l.Status, l.Type, l.Mode, l.spid

, max(Resource) as SampleResource

, left(max(ss.loginame), 16 ) as loginname

, max(ss.cmd) as cmd

, max(ss.lastwaittype) as lastwaittype

, left(max(ss.program_name), 32 ) as program_name

, left(max(ss.hostname), 32) as hostname

, max(ss.program_name) as full_program_name

from #t1 l, sysobjects o with (nolock)

, master..sysprocesses ss with (nolock)

where o.id = l.ObjId and l.spid = ss.spid

and l.dbid = db_id()

and l.Status in ( 'GRANT', 'CNVT')

and exists ( select *

from #t1 l2

where l2.Status not in ( 'GRANT', 'CNVT')

and l2.dbid = db_id()

and l.ObjId = l2.ObjId

and l.Resource = l2.Resource

)

group by o.name, l.Status, l.Type, l.Mode, l.spid
drop table #t1