Red Gate forums :: View topic - Long running query - (database mail) sp_readrequest
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Response 1
SQL Response 1 forum

Long running query - (database mail) sp_readrequest

Search in SQL Response 1 forum
Post new topic   Reply to topic
Jump to:  
Author Message
clombardo



Joined: 21 Apr 2009
Posts: 2

PostPosted: Tue Apr 21, 2009 3:18 am    Post subject: Long running query - (database mail) sp_readrequest Reply with quote

Hi I would like to suggest an enhancement request to filter/exclude known long running queries. We get 20-30 of these every day and my research (google) tells me to ingore these so that's exactly what I'd like to do. See quote below.

Quote:
So I've been running a trace on some SQL 2005 servers for awhile now and I'm starting to analyze them. One of the things I look at is statement top 10 duration. It was interesting to see all 10 entries to on every day I monitored to be:

TextData - exec sp_readrequest @receive_timeout=600000
ApplicationName - DatabaseMail90 - Id<2224>
Duraction - 609325251


The Id varied and the duration went up and down a little but that seemed interesting to me. I initially thought I had a Database mail problem and went to investigate. Nothing seemed out of place. So I went to BOL and looked up sp_readrequest only to find out it is an undocumented sp.

In fact doing a google search only comes back with 14 entries and only 1 really gives decent information which you can see here. But basically it said to ignore the entries as it's not doing any Reads, Writes, or CPUs as it is essentially doing a WAITFOR. Evidently this is due to the fact that Database Mail utilizes Service Broker to operate.

While I understand and don't have a problem with things happening behind the scenes that should be ignored, I do think it's poor that these things we're supposed to ignore are undocumented anywhere. In effect, how do we know it's performing "by design" and should be ignored if we don't have any information about it?



I apologise if this has already been requested but as you know the sqeaky wheel get the oil.

Thanks

Charles
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6677

PostPosted: Tue Apr 21, 2009 9:42 am    Post subject: Reply with quote

Hi Charles,

We are aware of this and have requested that SQL Response be modified to specifically ignore SQL 2005+ Database Mail. Because this feature of SQL is build on Microsoft Queueing technology (MSMQ), it always takes awhile to send mail because it's waiting for more mail to arrive, at least that's how I understand it. Anyway, it's probably worthwhile to add a special case to Response because this is always going to show as a long-running query even though it is functioning prefectly well.
Back to top
View user's profile Send private message
jasonmowbray



Joined: 21 Jan 2011
Posts: 2

PostPosted: Fri Jan 21, 2011 3:46 pm    Post subject: Long running query - (database mail) sp_readrequest Reply with quote

Dear Brian,

I am having the same issue as clombardo, running SQL Response 1.3. 0.58. Is there a way to avoid this?

Regards,

Jason
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6677

PostPosted: Fri Jan 21, 2011 4:14 pm    Post subject: Reply with quote

SQL Monitor (Response's successor) has the ability to ignore based on a regular expression. This is what users are currently doing to avoid notifications about Database Mail.
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