SQL server, SQLServerPedia Syndication

BROKER_RECEIVE_WAITFOR and Database Mail.

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:

  1. Go to SSMS (Connect to server)>Management>Database Mail.
  2. Right click on Database Mail and select ‘Configure Database Mail’.
  3. Click next if you see a welcome screen. This will take you to ‘Select Configuration Task’.
  4. Select the ‘View or change system parameters’ radio button and click ‘Next’.
  5. You will be taken to the ‘Configure System Parameters’ screen.
  6. 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.

Advertisements
Standard

2 thoughts on “BROKER_RECEIVE_WAITFOR and Database Mail.

  1. Bill says:

    The Microsoft SQL Server development team has a great blog site at http://blogs.msdn.com/b/sql_service_broker/.

    Specifically they have a post on the various waittypes that service broker can have. http://blogs.msdn.com/b/sql_service_broker/archive/2008/12/01/service-broker-wait-types.aspx

    If you query sys.service_queues from your msdb database you will see two queues, InternalMailQueue and ExternalMailQueue. These are used by dbmail.

    Queues can have activation stored procedures associated with them. Review the activation_procedure column of sys.service_queues.These receive messages from the queue. Starting and stoping the activations stored procedures frequently slows down message processing. The waitfor command allows the receive command to stay active for the timeout period in case another message arrives in the queue. This is a performance improvement.

    Bill — Microsoft

  2. Roger Wolter says:

    A common scenario in SQL is to wait for a row to appear on a table and then process that row. This is commonly done with a select statement thst either returns nothing or returns a row when a row is available. We usually call that “polling” for a row. Polling can be pretty inefficient and the performance will depend a lot on how often you poll. If you poll every few minutes, a row can be present for quite a while before it’s processed. On the other hand, if you poll every second, you can consume a lot of resources looking for a row that isn’t there. Service Broker got around this problem by having the RECEIVE statement block until a row is available on the queue. While it is waiting for a message to be placed on the queue for processing it is in the wait state you see when you examine wait statistics. A long wait in a RECEIVE just means that messages aren’t coming too often. In the case of Database Mail, the only thing that will reduce this wait is to send mail more often. Long waits on Service Broker RECEIVES are not a performance problem. The timeout value you were playing with determines how long the RECEIwill wait for a message before giving up and returning no result. Making this timeout shorter will waste resources because stopping the RECEIVE and starting another one uses some resources without accomplishing anything useful while just continuing to wait uses almost no resources.

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s