DBACockpit -> Database processes is really slow.

[former_member211576]

Hi experts,

  I found that Database processes is really slow recently.

It took about 3 minutes to query. However, if I use the T-SQL like below directly from SSMS, it returns instantly.

Please help.

select GETDATE(), r.session_id, s.host_name, s.program_name, s.host_process_id,

r.status, r.wait_time,wait_type,r.wait_resource,

substring(qt.text,(r.statement_start_offset/2) +1,

(case when r.statement_end_offset = -1

then len(convert(nvarchar(max), qt.text)) * 2

else r.statement_end_offset end -r.statement_start_offset)/2)

as stmt_executing,r.blocking_session_id, r.cpu_time,r.total_elapsed_time,r.reads,r.writes,

r.logical_reads, r.plan_handle

from sys.dm_exec_requests r

cross apply sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions s

where r.session_id > 50 and r.session_id=s.session_id

order by r.session_id, s.host_name, s.program_name, r.status

[former_member211576 · Accepted solution]

Hi experts,

  why is this SQL expensive? Is this the SAP version of Database processes?

insert into #rawlockdata ( spid, holdspid, pid, open_tran, status, username,

hostname, progname, command, inputbuff, waittime, waitrsc, lastwtype, dbname,

objid, objname, indexname, lockrsc, locktype, lockmode, lockstat, refcnt,

reqown, sqlhandle,start_os,end_os,planhandle )

select spid,blocked,hostprocess,open_tran,sp.status, loginame,hostname,

program_name,cmd, (

SELECT SUBSTRING(text,stmt_start/2,40)

FROM sys.dm_exec_sql_text(sp.sql_handle)), CASE WHEN waittime > 0 and waittime

< 2147483647 THEN waittime ELSE 0

END, convert(nchar(40),waitresource),convert(nchar(32),lastwaittype),

db_name(dbid), objid = CASE dm.resource_type WHEN 'OBJECT' THEN

dm.resource_associated_entity_id WHEN 'KEY' THEN (

select object_id

from sys.partitions

where hobt_id = dm.resource_associated_entity_id ) WHEN 'PAGE' THEN (

select object_id

from sys.partitions

where hobt_id = dm.resource_associated_entity_id ) WHEN 'RID' THEN (

select object_id

from sys.partitions

where hobt_id = dm.resource_associated_entity_id ) WHEN 'DATABASE' THEN

dm.resource_database_id WHEN 'FILE' THEN 0 WHEN 'EXTENT' THEN 0 WHEN 'METADATA'

THEN 0 WHEN 'HOBT' THEN (

select object_id

from sys.partitions

where hobt_id = dm.resource_associated_entity_id ) ELSE 0

END, objname = CASE dm.resource_type WHEN 'OBJECT' THEN

object_name(dm.resource_associated_entity_id) WHEN 'KEY' THEN (

select object_name ( object_id )

from sys.partitions

where hobt_id = dm.resource_associated_entity_id ) WHEN 'PAGE' THEN (

select object_name ( object_id )

from sys.partitions

where hobt_id = dm.resource_associated_entity_id ) WHEN 'RID' THEN (

select object_name ( object_id )

from sys.partitions

where hobt_id = dm.resource_associated_entity_id ) WHEN 'DATABASE' THEN

db_name(dm.resource_database_id) WHEN 'FILE' THEN 'file' WHEN 'EXTENT' THEN

'extent' WHEN 'METATDATA' THEN 'metadata' WHEN 'HOBT' THEN (

select object_name ( object_id )

from sys.partitions

where hobt_id = dm.resource_associated_entity_id ) ELSE ''

END, '', convert(nvarchar(32),dm.resource_description), convert(nvarchar(10),

dm.resource_type), convert(nvarchar(35),dm.request_mode), convert(nvarchar(15),

dm.request_status), dm.request_reference_count, convert(nvarchar(12),

dm.request_owner_type), CASE when req.sql_handle IS NULL then CASE when

c.most_recent_sql_handle IS NULL then NULL else c.most_recent_sql_handle

end else req.sql_handle

end, CASE when req.sql_handle is NULL then sp.stmt_start else

req.statement_start_offset

end, CASE when req.sql_handle is NULL then sp.stmt_end else

req.statement_end_offset

end, req.plan_handle

from master..sysprocesses as sp

join sys.dm_os_waiting_tasks ow on spid = blocking_session_id

join sys.dm_tran_locks as dm2 on ow.session_id = dm2.request_session_id

join sys.dm_tran_locks as dm on ow.blocking_session_id = dm.request_session_id

and dm.resource_associated_entity_id = dm2.resource_associated_entity_id and

dm.resource_description = dm2.resource_description left outer

join sys.dm_exec_requests as req on sp.spid = req.session_id left outer

join sys.dm_exec_connections as c on req.connection_id = c.connection_id

where dm.request_status = 'GRANT' and dm2.request_status = 'WAIT' -- final

results

[leslie_moser · Accepted solution]

Hello Dennis,

The temporary table #rawlockdata is used by an SAP delivered SQL Server Agent job and it gathers information about blocking locks on your system. By default we deliver this job deactivated so if you see this statement running frequently then someone has activated the job.

You can see the data collected by the job in DBA Cockpit -> Performance -> Blocking Lock History. If you press the Information button on that screen there is detailed information about the job and the data it is gathering.

Also on that screen there is a button "Turn collector job off". You can simply press this to disable the job. In the future, should you wish to reenable the job you can then press this same button to do so. At that time, the text of the button will read "Turn collector job on".

Best regards, Leslie

___________________________

Leslie Moser
Developer, Microsoft Platforms U.S.

Netweaver Development Tools
SAP Labs, LLC