SQL SERVER – sp_readrequest – Long Running Process

Just another day while doing performance tuning engagement, Senior DBA of the organization and I both noticed that one of the SP sp_readrequest  is continuously listed as long running SP. Well, the good part was that it was not taking too much of the resources. The page read and write were pretty low and the SP was not blocking anything at all. Initially we decided to ignore this SP as there were quite a lots of other things which we needed to take care to get maximum performance of the server.

However, after a while when we had a breather, we decided to take a look at this SP. sp_readrequest reads a request from the database mail queue and returns its content. If you open the content of this SP, you can notice that this runs a loop (cursor) inside the SP where it is actually using the passed parameter as a timeout value. The default timeout value is 600 that means this SP may run upto 600 seconds (10 minutes) before it exits.

You can see this value of 600 by running the following command.

Solarwinds
SELECT *
FROM msdb.dbo.sysmail_configuration

Now you can update this value to any lower value and the stored procedure sp_readrequest will run for the shorter period of the time.

SQL SERVER - sp_readrequest - Long Running Process DatabaseMailExeMinimumLifeTime-800x348

Solution

UPDATE msdb.dbo.sysmail_configuration
SET paramvalue = 30
WHERE paramname = 'DatabaseMailExeMinimumLifeTime'

Please note that you are just reducing the execution time for this SP, which is pretty harmless anyway from the beginning. If it does not bother you much, I suggest you just the default settings as it is.

Update: Lots of people ask me to provide query mentioned in this blog post which lists all the expensive queries, here is the link for the same: SQL SERVER – Find Most Expensive Queries Using DMV  (Please bookmark it)

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Troubleshooting: When Database Creation Takes Long Time
Next Post
SQL SERVER – 10 Questions Where YES May Be a Wrong Answer – Performance Health Check

Related Posts

1 Comment. Leave new

  • Does it impact jobs to run with some delay? I see this in my environment causing MSDB waits, Can you me explain me?

    Reply

Leave a Reply

Menu