We are using SQL Server 2008 Database Mail to send emails to our website visitors. I don't know whether SQL Server sends mail one by one by picking up from the queue or it will use threads to send emails in simultaneous fashion.
If Database Mail uses threads, is there any way to increase number of threads to run concurrently?
Taken from Books Online: Database Mail
To minimize the impact on SQL Server, the component that delivers e-mail runs outside of SQL Server, in a separate process. SQL Server will continue to queue e-mail messages even if the external process stops or fails. The queued messages will be sent once the outside process or SMTP server comes online.
Database Mail use Service Broker technology:
Database Mail provides background, or asynchronous, delivery. When you call sp_send_dbmail to send a message, Database Mail adds a request to a Service Broker queue. The stored procedure returns immediately. The external e-mail component receives the request and delivers the e-mail.
The actual email delivery is handled by your SMTP server and so this will bear the brunt of the workload and should be provisioned according to capacity/email traffic requirements.
I'd suggest trying a large volume of mails and seeing how well it performs. I don't know whether database mail is sent in serial or parallel - if you need to guarantee well performing mail, though, I'd suggest you send mails from a client application rather than using the database mail in SQL Server which I doubt was designed with this purpose in mind.
SQL Server uses threads to send mail, but your mail connector might not.
As best I can tell, Database Mail doesn't make any attempt at multithreading. It does use Service Broker, so calling sp_send_dbmail will return immediately and not wait for the message to be sent, however.
Looking at msdb.sys.service_queues (the queue definitions for Service Broker, which is how Database Mail is implemented), I see queues named InternalMailQueue and ExternalMailQueue, and both have max_readers set to 1. Looking at their activation procedures (sp_ExternalMailQueueListener and sp_sysmail_activate) doesn't suggest there's anything fancy going on there in terms of threading. The former does some error handling and conversation cleanup, and the latter does the actual mail sending via master..xp_sysmail_activate, which appears to be a blocking call that produces a return code.
So as far as I can see, there's no real attempt at multithreading for the sake of sending lots of messages simultaneously. It only does asynchronous queuing so your application doesn't have to wait for messages to be sent before continuing.
