Red Gate forums :: View topic - Time-Consuming query from SQL Monitor's source code
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Time-Consuming query from SQL Monitor's source code

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
Freman



Joined: 13 Sep 2012
Posts: 6

PostPosted: Wed Sep 18, 2013 3:10 am    Post subject: Time-Consuming query from SQL Monitor's source code Reply with quote

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 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'
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6581

PostPosted: Thu Sep 19, 2013 11:45 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Freman



Joined: 13 Sep 2012
Posts: 6

PostPosted: Wed Feb 12, 2014 1:23 am    Post subject: Re: Reply with quote

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!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group