Konkurrierende Prozesse und Wait-Resources

Welcher Admin kennt das nicht: Die Anwender schreien, dass die Anwendung zu langsam ist? Was tun? Erstmal einen Blick auf die aktuellen Prozesse werfen. Welche Prozesse haben den Status "suspended"?

SELECT *
FROM master..sysprocesses p
WHERE p.status = 'suspended'

Aber was soll diese Wait-Resource-Spalte mit der kryptischen Zeichenfolge? Diese Frage habe ich mir auch schon gestellt und aus diesem Grund eine Prozedur erstellt, die aus der kryptischen Zeichenfolge weitgehend den genauen Teil, der blockiert wird, spezifiziert.

Es gibt verschiedene Arten von waitresource-Strings. Ein wichtiges Kriterium für die Erkennung des Objektes, auf den gewartet wird, ist der vorangestellte Objekttyp. Dieser Typ kann folgende Ausprägungen haben:

RID: In diesem Fall liegt die Sperre auf der internen ROWID einer Tabelle. Eine ROWID existiert nur auf Tabellen, die keinen eindeutigen Clustered Index haben. Es ist eine vom SQL Server erzeugte GUID zur eindeutigen Identifikation des Datensatzes.

PAGE / PAG: Im Falle einer Page als Wartemedium handelt es sich um eine dedizierte 8-KB-Seite im SQL Server. Das können verschiedene Arten von Pages sein. Meistens handelt es sich um Index-Pages und Table-Pages.

EXT: EXT steht für Extent, welcher physikalisch mit einer Page vergleichbar ist, aber aus einer Menge von 8 Pages = 64 KB besteht.

OBJECT / TAB: In diesem Fall wird auf das vollständige Objekt, meistens eine Tabelle, gewartet, das heißt, der Prozess möchte das Objekt vollständig sperren.

KEY: Wenn eine Tabelle kein Heap ist, sondern einen clustered Index hat, so wird analog zur RowID auf Zeilen von Primärschlüssel gewartet.

Mit diesen Erkenntnissen habe ich eine Prozedur erstellt, die aus diesem String, der die Wait-Resource identifiziert, das spezifische Objekt, also die Tabelle, und wenn möglich sogar die betroffenen Zeilen zurückgibt.

Bei dem Typ KEY ist es nämlich möglich durch den in Klammern stehenden Teil die betreffenden Zeilen im SQL Server zu identifizieren.

USE [ADMINDB]GOIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ADMIN_Resolve_WaitResource]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ADMIN_Resolve_WaitResource]GO

/*
Autor: Patrick Fiedler

Website: http://www.fiedler-sql-consulting.de
Ablauf: Löst die WaitResourcen-Strings auf, wie zum Beispiel:
     OBJECT: 2:965199099:16
     RID: 6:1:2618395:15
     KEY: 7:72057594040811520 (4a98f6ce4bc0)
     PAGE: 5:1:13259434
     TAB: 4:149575571:0
*/create procedure [dbo].[ADMIN_Resolve_WaitResource]
       @WaitResource nvarchar(100),
       @ResourceName nvarchar(100) = null output,
       @WithTableOutput bit = 0,
       @WithInvolvedRowsOutput bit = 1
as

set nocount on

declare @ResourceType nvarchar(50),
  @dbid int,
  @fileid int,
  @pageid bigint,
  @hobtid bigint,
  @ObjectId int,
  @Objectname nvarchar(200),
  @IndexId int,
  @LockRes nvarchar(50),
  @sql nvarchar(max)

set @ResourceType=substring(@WaitResource,1,CHARINDEX(':',@WaitResource)-1)
set @WaitResource=substring(@WaitResource,CHARINDEX(':',@WaitResource)+1,200)

create table #TempPage(
  ParentObject nvarchar(200),
  Object nvarchar(200),
  Field nvarchar(200),
  Value nvarchar(max)
)

if(@ResourceType in ('PAGE','PAG','EXT'))
begin
-- Datenbank-ID trennen
set @dbid=SUBSTRING(@WaitResource,1,CHARINDEX(':',@WaitResource)-1)
-- FileID trennen
set @fileid=SUBSTRING(@WaitResource,CHARINDEX(':',@WaitResource)+1,CHARINDEX(':',@WaitResource,CHARINDEX(':',@WaitResource)+1)-CHARINDEX(':',@WaitResource)-1)
-- PageID trennen
set @pageid=reverse(SUBSTRING(REVERSE(@WaitResource),1,charindex(':',reverse(@WaitResource))-1))

set @sql=N'use ['+DB_NAME(@dbid)+']; DBCC PAGE (@dbid, @fileid, @pageid, 3) with tableresults'

begin try
  insert into #TempPage
  exec sp_executesql @sql, N'@dbid int, @fileid int, @pageid bigint',@dbid=@dbid, @fileid=@fileid, @pageid=@pageid
end try
begin catch
  set @sql=N'use ['+DB_NAME(@dbid)+']; DBCC PAGE (@dbid, @fileid, @pageid) with tableresults'
  insert into #TempPage
  exec sp_executesql @sql, N'@dbid int, @fileid int, @pageid bigint',@dbid=@dbid, @fileid=@fileid, @pageid=@pageid
end catch

set @ObjectId=(select Value from #TempPage where Field='Metadata: ObjectId')
set @IndexId=(select Value from #TempPage where Field='Metadata: IndexId')

set @sql='   use ['+DB_NAME(@dbid)+'];   select @ResourceName=so.name+'' - ''+case when '+CAST(@IndexId as nvarchar)+'=0 then ''Heap'' else isnull(si.name,''N/A'') end,     @ObjectName=so.name   from sys.objects so   left join     sys.indexes si on so.object_id=si.object_id   where so.object_id='+CAST(@ObjectId as nvarchar)+'    and si.index_id='+CAST(@IndexId as nvarchar)
exec sp_executesql @sql, N'@ResourceName nvarchar(100) output, @ObjectName nvarchar(200) output', @ResourceName=@ResourceName out, @ObjectName=@ObjectName out

if(@WithTableOutput=1 and @WithInvolvedRowsOutput=1)
begin
  set @LockRes=substring(@WaitResource, charindex('(',@WaitResource), 4000)

  set @sql='    use ['+db_name(@dbid)+'];    select Comment=''Involved Rows'',      InternalRowId= %%lockres%%,      o.*    from (     select t.Value     from #TempPage t     where t.Field in (''RowId'',''KeyHashValue'')    ) x    inner join      ['+@Objectname+'] o with (index='+cast(@IndexId as nvarchar)+') on x.Value= %%lockres%%    '
  exec(@sql)

end

if(@WithTableOutput=1)
begin
  select WaitResource=@ResourceType+': '+@WaitResource,
    ObjectId=@ObjectId,
    IndexId=@IndexId,
    ResourceName=@ResourceName
end
end else
if(@ResourceType='KEY')
begin
-- Datenbank-ID trennen
set @dbid=SUBSTRING(@WaitResource,1,CHARINDEX(':',@WaitResource)-1)
-- FileID trennen
if(charindex('(',@WaitResource)>0)
  set @hobtid=SUBSTRING(@WaitResource,CHARINDEX(':',@WaitResource)+1,CHARINDEX('(',@WaitResource,CHARINDEX(':',@WaitResource)+1)-CHARINDEX(':',@WaitResource)-1)
else
  set @hobtid=SUBSTRING(@WaitResource,CHARINDEX(':',@WaitResource)+1,100)

set @sql='   use ['+DB_NAME(@dbid)+'];   SELECT @ObjectId=o.object_id,     @Objectname=o.name,     @IndexId=i.index_id,     @ResourceName=o.name + '' - '' + isnull(i.name,''N/A'')   FROM sys.partitions p   INNER JOIN     sys.objects o ON p.OBJECT_ID = o.OBJECT_ID   LEFT JOIN     sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID           AND p.index_id = i.index_id   WHERE p.hobt_id = '+cast(@hobtid as nvarchar)+' '
--print @sql
exec sp_executesql @sql, N'@ObjectId bigint output, @Objectname nvarchar(200) output, @IndexId int output, @ResourceName nvarchar(100) output', @ObjectId=@ObjectId out, @Objectname=@Objectname out, @IndexId=@IndexId out, @ResourceName=@ResourceName out
--print @ResourceName

if(@WithTableOutput=1 and @WithInvolvedRowsOutput=1)
begin
  set @LockRes=substring(@WaitResource, charindex('(',@WaitResource), 4000)

  set @sql='    use ['+db_name(@dbid)+'];    select Comment=''Involved Rows'',      InternalRowId= %%lockres%%,      *    from ['+@Objectname+'] with (index='+cast(@IndexId as nvarchar)+')    where %%lockres%% = '''+@LockRes+''''
  exec(@sql)

end

if(@WithTableOutput=1)
begin
  select WaitResource=@ResourceType+': '+@WaitResource,
    ObjectId=@ObjectId,
    IndexId=@IndexId,
    ResourceName=@ResourceName
end
end else
if(@ResourceType in ('OBJECT','TAB'))
begin
-- Datenbank-ID trennen
set @dbid=SUBSTRING(@WaitResource,1,CHARINDEX(':',@WaitResource)-1)
-- ObjectID trennen
set @ObjectId=SUBSTRING(@WaitResource,CHARINDEX(':',@WaitResource)+1,CHARINDEX(':',@WaitResource,CHARINDEX(':',@WaitResource)+1)-CHARINDEX(':',@WaitResource)-1)

set @sql='use ['+DB_NAME(@dbid)+']; set @ResourceName=object_name('+CAST(@ObjectId as nvarchar)+')'
exec sp_executesql @sql, N'@ResourceName nvarchar(100) output', @ResourceName=@ResourceName out

if(@WithTableOutput=1)
begin
  select WaitResource=@ResourceType+': '+@WaitResource,
    ObjectId=@ObjectId,
    IndexId=-1,
    ResourceName=@ResourceName
end
end else
if(@ResourceType='RID')
begin
-- Datenbank-ID trennen
set @dbid=SUBSTRING(@WaitResource,1,CHARINDEX(':',@WaitResource)-1)
--print @dbid
-- FileID trennen
set @fileid=SUBSTRING(@WaitResource,CHARINDEX(':',@WaitResource)+1,CHARINDEX(':',@WaitResource,CHARINDEX(':',@WaitResource)+1)-CHARINDEX(':',@WaitResource)-1)
-- ObjectID trennen
set @PageId=reverse(SUBSTRING(reverse(@WaitResource),CHARINDEX(':',reverse(@WaitResource))+1,CHARINDEX(':',reverse(@WaitResource),CHARINDEX(':',reverse(@WaitResource))+1)-CHARINDEX(':',reverse(@WaitResource))-1))
--print @ObjectId

set @sql=N'use ['+DB_NAME(@dbid)+']; DBCC PAGE (@dbid, @fileid, @pageid, 3) with tableresults'

insert into #TempPage
exec sp_executesql @sql, N'@dbid int, @fileid int, @pageid bigint',@dbid=@dbid, @fileid=@fileid, @pageid=@pageid

set @ObjectId=(select Value from #TempPage where Field='Metadata: ObjectId')
set @IndexId=(select Value from #TempPage where Field='Metadata: IndexId')

set @sql='   use ['+DB_NAME(@dbid)+'];   select @ResourceName=so.name+'' - ''+case when '+CAST(@IndexId as nvarchar)+'=0 then ''Heap'' else isnull(si.name,''N/A'') end,     @Objectname=so.name   from sys.objects so   left join     sys.indexes si on so.object_id=si.object_id         and si.index_id='+CAST(@IndexId as nvarchar)+'   where so.object_id='+CAST(@ObjectId as nvarchar)+'    '
exec sp_executesql @sql, N'@ResourceName nvarchar(100) output, @Objectname nvarchar(200) output', @ResourceName=@ResourceName out, @Objectname=@Objectname out

if(@WithTableOutput=1 and @WithInvolvedRowsOutput=1)
begin
  set @LockRes=substring(@WaitResource, charindex(':',@WaitResource)+1, 4000)
  set @sql='    use ['+db_name(@dbid)+'];    select Comment=''Involved Rows'',      InternalRowId= %%lockres%%,      *    from ['+@Objectname+']    where %%lockres%% = '''+@LockRes+''''
  exec(@sql)

end

if(@WithTableOutput=1)
begin
  select WaitResource=@ResourceType+': '+@WaitResource,
    ObjectId=@ObjectId,
    IndexId=@IndexId,
    ResourceName=@ResourceName
end
end

drop table #TempPage

--select @ResourceName