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.

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.

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)