We had a performance problem on one of our production SQL servers and I jumped to have a look at the DB. Everything looked fine under the current requests. Sessions were being executed quickly and very few transactions were happening. However, there was one row that stayed on for a while. This row was in a SUSPENDED state and performing a DELETE command in MSDB. The last wait was on a BROKER_RECEIVE_WAITFOR. Now I started to scramble for service broker information, but there were no user defined service broker queues.
The reassuring part was that the session used up very little CPU and IO. After two days of troubleshooting with the application/dev/QA team and end users, we found that the issue was on the application server. Apparently queries were pulled into the app server and then manipulated on the app server, thereby beating the purpose of using SQL server.
Anyhow, the BROKER_RECEIVE_WAITFOR wait was still persistent. All the other transactions flew by quickly, but this one would stay on for a while and then reappear with a different session id. After a bit of reading, here is what I found:
BROKER_RECEIVE_WAITFOR occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.
In essence, you have some service which is waiting for a response, a “listener” if you will. For example, e-mail would be an example of a “listener” which may wait indefinitely for a message. Or someone may have set-up service broker to “listen” for certain events. You would have to know what is running on your system to get a breakdown. If someone is using SQL or .NET to run a WAITFOR (RECEIVE..) command, then you can see if a timeout has been specified or not. If not, then the code will run indefinitely. Link.
This process was not hung, it is intended to remain running so the sending of further emails is faster. Consider it like the SQL server agent, you wouldn’t want the service to need to be started up each time a job needed to be re-run. The default setting of 10,000 seconds is set to be a rough compromise between frequency and need, setting it to ten seconds may be putting more load on the server as the mail server needs to stop and start the communication channel with the exchange server more than it needs. It all depends on the number of emails you are sending, we send thousands a day so wouldn’t want this service to be turned off and on again at all. Link.
Down to the root of the issue:
So we were using DB mail and were getting a long waiting session on BROKER_RECEIVE_WAITFOR. Since the session was waiting for a specified amount of time, there had to be some sort of setting that specified this wait. Here is where that setting is:
- Go to SSMS (Connect to server)>Management>Database Mail.
- Right click on Database Mail and select ‘Configure Database Mail’.
- Click next if you see a welcome screen. This will take you to ‘Select Configuration Task’.
- Select the ‘View or change system parameters’ radio button and click ‘Next’.
- You will be taken to the ‘Configure System Parameters’ screen.
- The value of the parameter ‘Database Mail Executable Minimum Lifetime (seconds)’ will determine how long your task will wait before renewing.
I was so excited that I checked the session again in a query and refreshed its wait duration. The time was in milliseconds and was currently showing 274638, that is around 274 seconds. So, in theory, once the wait duration reached 600000 or 600 seconds, the wait should be over. I refreshed the query occasionally till about 595 seconds and then voila, session disappeared. I would say this confirms that this BROKER_RECEIVE_WAITFOR wait is a result of the Minimum Lifetime setting in Database Mail.