As a SQL Server database administrator, one of the common challenges we face is optimizing the performance of our databases. In a recent performance tuning engagement, my colleague and I came across an interesting scenario involving a long-running stored procedure (SP) called sp_readrequest.

Initially, we noticed that this SP was consistently listed as a long-running query, although it was not consuming excessive resources. The page read and write operations were minimal, and it was not causing any blocking issues. With a long list of other performance-related tasks to tackle, we decided to prioritize other areas first.

However, during a lull in our workload, we decided to investigate further. Upon examining the code of the sp_readrequest SP, we discovered that it contained a loop (cursor) that utilized a timeout parameter. By default, the timeout value was set to 600 seconds (10 minutes). This meant that the SP could potentially run for up to 10 minutes before exiting.

If you wish to view the current timeout value, you can execute the following command:

SELECT * FROM msdb.dbo.sysmail_configuration

To reduce the execution time of the sp_readrequest SP, you can update the timeout value to a lower value. However, it’s important to note that this SP is relatively harmless and does not significantly impact performance. Therefore, if it doesn’t cause any major concerns, it is recommended to keep the default settings as they are.

If you still wish to modify the timeout value, you can use the following solution:

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

By executing this query, you will reduce the execution time of the sp_readrequest SP to 30 seconds.

It’s worth mentioning that this solution only affects the execution time of the SP and does not have any significant impact on the overall performance of the server.

Remember, when it comes to performance tuning, it’s essential to prioritize the most critical areas first. While the sp_readrequest SP may appear as a long-running query, it is often not a major concern. However, if you still wish to optimize its execution time, the provided solution can be implemented.

For more information on optimizing SQL Server performance and identifying expensive queries, you can refer to the following resource: SQL Server Performance Tuning Guide.