I am using SQL server 2008R2 instance which is monitored by red-gate SQL monitor. Database mail process is taking more than 5 minutes to execute. It always come up in my long running query list.
Below is the sql fragment. I have no idea how to make it execute faster and where to tune it. Any direction will be much appreciated.
Process ID: 79
Process name: DatabaseMail - DatabaseMail - Id<8708>
Process login time: 5 Oct 2011 5:03 PM
Query start time: 5 Oct 2011 5:03 PM
Query duration: 585 sec
SQL process fragment
-- sp_readrequest : Reads a request from the the queue and returns its
CREATE PROCEDURE sp_readrequest
@receive_timeout INT -- the max time this read will wait for new message
SET NOCOUNT ON
-- Table to store message information.
DECLARE @msgs TABLE
-- Declare variables to store row values fetched from the cursor
Oct 04 '11 at 10:03 PM