Long running query - (database mail) sp_readrequest

Monitor, diagnose and receive alerts to problems across your SQL Servers.

Moderators: eddie davis, david connell, dlkj, Colin Millerchip, priyasinha, Emma A

Long running query - (database mail) sp_readrequest

Postby clombardo » Tue Apr 21, 2009 3:18 am

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.

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
clombardo
 
Posts: 2
Joined: Tue Apr 21, 2009 3:00 am

Postby Brian Donahue » Tue Apr 21, 2009 9:42 am

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Long running query - (database mail) sp_readrequest

Postby jasonmowbray » Fri Jan 21, 2011 3:46 pm

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
jasonmowbray
 
Posts: 2
Joined: Fri Jan 21, 2011 3:45 pm

Postby Brian Donahue » Fri Jan 21, 2011 4:14 pm

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Response 1

Who is online

Users browsing this forum: No registered users and 0 guests