Mar 19, 2017 8:30:31 PM by Pinal Dave
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.
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.
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.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Tags: SQL Server
Written by Pinal Dave
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 1700 articles on the subject on his blog at http://blog.sqlauthority.com. He is a dynamic and proficient Principal Database Architect who specializes in SQL Server Performance Tuning and has 7+ years of hands-on experience. He holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is also Regional Mentor for PASS Asia. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.