SQL2005 "Session (SQL)" View für Perform. Troubles

Ggf. ist das Thema schon etwas alt, habe dazu hier aber noch nichts gefunden.

Im Performance Troubleshooting Guide gibt es eine SQL View "Session (SQL)" die über ein SQL Script angelegt wird. Das bisher oder immer noch ausgelieferte funktioniert allerdings bei Blocks (Datenbanksperren) nicht korrekt (SUBSTRING Fehler) mit dem SQL Server 2005. Hier eine neue Version die so auch in das neue Perfromance Troubleshooting Guide kommt bzw. gekommen ist:

Code:

CREATE VIEW dbo.[Session (SQL)] AS
-- calcs used to identify string position within WaitResource column
-- Pos1= charindex(':',@waitresource, 5)
-- Pos2= charindex('(',@waitresource, charindex(':',@waitresource, 5) +1)
-- @hobt_id = substring(@waitresource, @Pos1 +1, @Pos2 - @Pos1 - 2)

SELECT     
  SP.[spid] AS [Connection ID],
  RTRIM(SP.[loginame]) AS [User ID],
  SD.[name] AS [Database Name],
  CAST(CASE WHEN SP.[spid] = @@SPID THEN 1 ELSE 0 END AS TINYINT) AS [My Session],
  CONVERT(DATETIME, CONVERT(CHAR(10), SP.[login_time], 120)  + ' 00:00:00:000', 121) AS [Login Date],
  CONVERT(DATETIME, '1754-01-01 ' + CONVERT(CHAR(8), SP.[login_time], 108), 120) AS [Login Time],
  RTRIM(SP.[program_name]) AS [Application Name],
  RTRIM(SP.[hostname]) AS [Host Name],
  CAST(CASE WHEN SP.[blocked] > 0 THEN 1 ELSE 0 END AS TINYINT) AS [Blocked],
  CAST(SP.[blocked] AS INTEGER) AS [Blocked by Connection ID],
  COALESCE(RTRIM(SP2.[loginame]),'') AS [Blocked by User ID],
  COALESCE(RTRIM(SP2.[hostname]),'') AS [Blocked by Host Name],
  CAST(SP.[waittime] AS decimal(38, 20)) AS [Waiting Time (ms)],
  CAST(SP.[cpu] AS decimal(38, 20)) AS [CPU],
  CAST(SP.[memusage] AS decimal(38, 20)) [Memory Usage],
  CAST(SP.[physical_io] AS decimal(38, 20)) AS [Physical I/O],
  CAST(
    CASE
  when left(SP.[waitresource],4) = 'KEY:' AND SP.[blocked] > 1 AND convert(smallint, substring (SP.[waitresource], 5, charindex(':',SP.[waitresource], 5) - 5)) = db_id()    -- We have a block and a KEY WaitResource value related to the NAVISION DB!
   then obj.name
  when left(SP.[waitresource],4) = 'KEY:' AND SP.[blocked] > 1 AND convert(smallint, substring (SP.[waitresource], 5, charindex(':',SP.[waitresource], 5) - 5)) <> db_id()    -- We have a block and a KEY WaitResource value NOT related to the NAVISION DB!
   then 'DB: ' + db_name(convert(smallint, substring (SP.[waitresource], 5, charindex(':',SP.[waitresource], 5) - 5)) )
  else ''
    END AS VARCHAR(100))
   AS [Wait Resource]
FROM [master].[dbo].[sysprocesses] AS SP JOIN [master].[dbo].[sysdatabases] AS SD ON (SP.dbid = SD.dbid)
LEFT OUTER JOIN [master].[dbo].[sysprocesses] AS SP2 ON (SP.[blocked] = SP2.[spid])
left join sys.partitions part on substring(SP.[waitresource], charindex(':',SP.[waitresource], 5)+1   , charindex('(',SP.[waitresource], charindex(':',SP.[waitresource], 5) +1) - charindex(':',SP.[waitresource], 5) - 2  ) = part.hobt_id
left join sys.objects obj on part.object_id = obj.object_id
WHERE SP.[program_name] <> ''
and left(SP.[waitresource],4) = 'KEY:'
union all
SELECT     
  SP.[spid] AS [Connection ID],
  RTRIM(SP.[loginame]) AS [User ID],
  SD.[name] AS [Database Name],
  CAST(CASE WHEN SP.[spid] = @@SPID THEN 1 ELSE 0 END AS TINYINT) AS [My Session],
  CONVERT(DATETIME, CONVERT(CHAR(10), SP.[login_time], 120)  + ' 00:00:00:000', 121) AS [Login Date],
  CONVERT(DATETIME, '1754-01-01 ' + CONVERT(CHAR(8), SP.[login_time], 108), 120) AS [Login Time],
  RTRIM(SP.[program_name]) AS [Application Name],
  RTRIM(SP.[hostname]) AS [Host Name],
  CAST(CASE WHEN SP.[blocked] > 0 THEN 1 ELSE 0 END AS TINYINT) AS [Blocked],
  CAST(SP.[blocked] AS INTEGER) AS [Blocked by Connection ID],
  COALESCE(RTRIM(SP2.[loginame]),'') AS [Blocked by User ID],
  COALESCE(RTRIM(SP2.[hostname]),'') AS [Blocked by Host Name],
  CAST(SP.[waittime] AS decimal(38, 20)) AS [Waiting Time (ms)],
  CAST(SP.[cpu] AS decimal(38, 20)) AS [CPU],
  CAST(SP.[memusage] AS decimal(38, 20)) [Memory Usage],
  CAST(SP.[physical_io] AS decimal(38, 20)) AS [Physical I/O],
  CAST('' as VARCHAR(100)) as [Wait Resource]
FROM [master].[dbo].[sysprocesses] AS SP JOIN [master].[dbo].[sysdatabases] AS SD ON (SP.dbid = SD.dbid)
LEFT OUTER JOIN [master].[dbo].[sysprocesses] AS SP2 ON (SP.[blocked] = SP2.[spid])
left join sys.partitions part on substring(SP.[waitresource], charindex(':',SP.[waitresource], 5)+1   , charindex('(',SP.[waitresource], charindex(':',SP.[waitresource], 5) +1) - charindex(':',SP.[waitresource], 5) - 2  ) = part.hobt_id
left join sys.objects obj on part.object_id = obj.object_id
WHERE SP.[program_name] <> ''
and (left(SP.[waitresource],4) <> 'KEY:' or SP.[waitresource] is null)
go