How can we help you today? How can we help you today?

Time-Consuming query from SQL Monitor's source code

Hi All,
Rcently I am bothered by some performance issues. I found the follow query made a serious time-consuming which looks like coming from the SQL Monitor's source query code.
If anyone can help me analyze this , many thanks!
SELECT procc.blocked AS [procc.blocked], blocking.login_time AS [blocking.login_time], 
rtrim(ltrim(procc.cmd)) AS [rtrim(ltrim(procc.cmd))], 
procc.cpu AS [procc.cpu], CASE WHEN procc.dbid > 0 THEN DB_NAME(procc.dbid) ELSE NULL END AS [CASE WHEN procc.dbid > 0 THEN DB_NAME(procc.dbid) ELSE NULL END],
 CASE WHEN procc.blocked > 0 THEN rtrim(ltrim(query2.text)) 
 ELSE NULL END AS [CASE WHEN procc.blocked > 0 THEN rtrim(ltrim(query2.text)) ELSE NULL END], 
 CASE WHEN procc.blocked > 0 OR (procc.last_batch < DATEADD(s, -15, GETDATE()) 
 AND NOT (procc.status = 'dormant' OR procc.status = 'sleeping' OR procc.status = 'done')) 
 THEN rtrim(ltrim(query.text)) ELSE NULL END AS [Full Command],
  rtrim(ltrim(procc.hostname)) AS [rtrim(ltrim(procc.hostname))], procc.last_batch AS [procc.last_batch], 
  rtrim(ltrim(procc.lastwaittype)) AS [rtrim(ltrim(procc.lastwaittype))], 
  rtrim(ltrim(procc.loginame)) AS [rtrim(ltrim(procc.loginame))], procc.login_time AS [procc.login_time], 
  procc.memusage AS [procc.memusage], procc.open_tran AS [procc.open_tran], 
  procc.physical_io AS [procc.physical_io], rtrim(ltrim(procc.program_name))
   AS [rtrim(ltrim(procc.program_name))], procc.spid AS [procc.spid], 
   rtrim(ltrim(procc.status)) AS [rtrim(ltrim(procc.status))], procc.uid AS [procc.uid], 
   rtrim(ltrim(procc.waitresource)) AS [rtrim(ltrim(procc.waitresource))],
    procc.waittime AS [procc.waittime], procc.waittype AS [procc.waittype]
     FROM master..sysprocesses AS procc WITH ( NOLOCK ) 
LEFT JOIN master..sysprocesses AS blocking WITH ( NOLOCK ) ON procc.blocked = blocking.spid 
CROSS APPLY sys.dm_exec_sql_text(CONVERT(varbinary(64), procc.sql_handle)) AS query 
OUTER APPLY sys.dm_exec_sql_text(CONVERT(varbinary(64), blocking.sql_handle)) AS query2 
WHERE procc.cmd != 'AWAITING COMMAND'
Freman
0

Comments

2 comments

Sort by
  • Brian Donahue
    The query you highlight looks to be from SQL Monitor. You can tell this in your profiler session by the application name, which should rather be Red Gate SQL Monitor or Red Gate SQL Tools...

    The query is used to get a list of running processes from the server.

    I don't know anything about how to make this more efficient.

    The best I can offer it to try to find the polling frequency for this query and perhaps have it poll less often.
    Brian Donahue
    0
  • Freman
    The query you highlight looks to be from SQL Monitor. You can tell this in your profiler session by the application name, which should rather be Red Gate SQL Monitor or Red Gate SQL Tools...

    The query is used to get a list of running processes from the server.

    I don't know anything about how to make this more efficient.

    The best I can offer it to try to find the polling frequency for this query and perhaps have it poll less often.

    If this is the case, please offer me the method to reduce the polling frequency, tks!
    Freman
    0

Add comment

Please sign in to leave a comment.