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: Alles auswählen
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
