Time-Consuming query from SQL Monitor's source code

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Time-Consuming query from SQL Monitor's source code

Postby Freman » Wed Sep 18, 2013 3:10 am

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!

Code: Select all
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
 
Posts: 6
Joined: Thu Sep 13, 2012 7:33 am

Postby Brian Donahue » Thu Sep 19, 2013 11:45 am

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
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Re:

Postby Freman » Wed Feb 12, 2014 1:23 am

Brian Donahue wrote: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
 
Posts: 6
Joined: Thu Sep 13, 2012 7:33 am


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 1 guest