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

Long running query - (database mail) sp_readrequest

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
0

Comments

3 comments

Sort by
  • Brian Donahue
    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
    0
  • jasonmowbray
    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
    0
  • Brian Donahue
    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
    0

Add comment

Please sign in to leave a comment.