Serious async in SQL Server Mail related SQL statement_ NETWORK_ IO wait event case

Time:2020-10-22

 

DPA monitoring found that a SQL server server executed system stored procedures in the last two days msdb.dbo.sp_ Serious async occurs when a SQL in mailitemresultsets is used_ NETWORK_ IO wait. As shown in the screenshot below

 

clip_image001

 

Further analysis shows that it is mainly the execution of stored procedures msdb.dbo.sp_ Async appears in the following SQL statement in mailitemresultsets_ NETWORK_ IO wait

 

SELECT 
      mi.mailitem_id,
      mi.profile_id,
      (SELECT name FROM msdb.dbo.sysmail_profile p WHERE p.profile_id = mi.profile_id) as 'profile_name',
      mi.recipients,
      mi.copy_recipients,
      mi.blind_copy_recipients,
      mi.subject,
      mi.body, 
      mi.body_format, 
      mi.importance,
      mi.sensitivity,
      ISNULL(sr.send_attempts, 0) as retry_attempt,
      ISNULL(mi.from_address, '') as from_address,
      ISNULL(mi.reply_to, '')     as reply_to
   FROM sysmail_mailitems as mi
      LEFT JOIN sysmail_send_retries as sr
         ON sr.mailitem_id = mi.mailitem_id 
   WHERE mi.mailitem_id = @mailitem_id

 

Further analysis shows that any one of them is associated with the table sysmail_ SQL related to allitems will have serious async_ NETWORK_ IO wait:

 

SELECT * FROM msdb.dbo.sysmail_allitems WITH(NOLOCK)
WHERE sent_status != 'sent' 
ORDER BY sent_date DESC;

 

In addition, sysmail was found during the analysis_ There are only more than 70000 records in the mailitems table, but the size of the table is close to 10g, as shown in the following screenshot:

 

 

clip_image002

 

 

This is obviously not normal, sysmail_ There must be some very large email records in mailitems, because this system often generates some report data to send to users through SQL. So I wanted to check to see if there were really some oversized emails. You have to check sysmail here_ The row size of the mailitems table, so use the following script to view the table sysmail_ The size of the row record in mailitems.

 

USE msdb;
GO
 
IF object_id('sp_GetRowSize') is not null
drop procedure sp_GetRowSize
GO
CREATE procedure sp_GetRowSize(@Tablename varchar(100),@pkcol varchar(100))
AS 
BEGIN
declare @dynamicsql varchar(MAX)
 
-- A @pkcol can be used to identify max/min length row
set @dynamicsql = 'select ' + @PkCol +' , (0'
 
-- traverse each record and calculate the datalength
select @dynamicsql = @dynamicsql + ' + isnull(datalength(' + name + '), 1)' 
    from syscolumns where id = object_id(@Tablename)
set @dynamicsql = @dynamicsql + ') as rowsize from ' + @Tablename + ' order by 2 desc'
 
 
print (@dynamicsql)
 
END

 

 

As shown in the following screenshot, there are some mail records whose rowsize is super large. Under normal circumstances, the rowsize is only about 1122 bytes, while the mailitem_ Id = 5146768 this record has 1352285196 bytes. If it is converted into size, select 1352285196.0/1024/1024 ~=1290m, really speechless!!

 

 

EXEC sp_GetRowSize ‘sysmail_mailitems’, ‘mailitem_id’

 

clip_image003

 

 

  The reason is not complicated, that is, there is a logic error in the SQL that generates the e-mail, which makes the body of the e-mail extremely large msdb.dbo.sysmail_ Allitems become very large, and the IO performance of SQL statements related to them becomes worse, and async appears_ NETWORK_ IO wait. In fact, similar cases have been encountered before, please refer toSQL Server 2008 R2 execution stored procedure sp_ Mailitemresultsets cause a large number of preemptive_ OS_ Waitforsingleobjec waitIt’s just that you didn’t dig deep into the root cause at that time!

 

 

 

Solution

 

So how to solve this problem? Very simple, it is to delete the table msdb.dbo.sysmail_ Make the size of the record in allitems smaller. You can also delete those emails_ A record with a very large ID. It can be handled with the following script

 

/******************************************************************************************************
Script function: the following example conditionally removes e-mail from a database mail system
*******************************************************************************************************/
DECLARE @GETDATE datetime  
SET @GETDATE = GETDATE()-2;  
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;  
GO  

 

In fact, sysmail_ delete_ mailitems_ The logic in SP is also deleted msdb.dbo.sysmail_ Records in allitems, as shown below, should be processed when the business is idle during processing, otherwise it will cause a lot of blocking.

 

DELETE 
FROM msdb.dbo.sysmail_allitems 
WHERE ((@sent_before IS NULL) 
OR ( send_request_date < @sent_before)) 
AND ((@sent_status IS NULL) 
OR (sent_status = @sent_status))

 

 

CREATE PROCEDURE  
sysmail_delete_mailitems_sp  
@sent_before DATETIME = NULL,  -- sent before
  
@sent_status varchar(8) = NULL -- sent status
  
AS  
   BEGIN  
      SET @sent_status = LTRIM(RTRIM(@sent_status))  
      IF @sent_status = ''  
      SET @sent_status = NULL  
      IF ( (@sent_status IS NOT NULL) AND  
      (LOWER(@sent_status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ( 'unsent', 'sent', 'failed' 
      , 'retrying') ) )  
      BEGIN  
         RAISERROR(14266, -1, -1, '@sent_status', 'unsent, sent, failed, retrying')  
         RETURN(1) -- Failure
  
      END  
      IF ( @sent_before IS NULL AND @sent_status IS NULL )  
      BEGIN  
         RAISERROR(14608, -1, -1, '@sent_before', '@sent_status')  
         RETURN(1) -- Failure
  
      END 
/* BEGIN ACTIVE SECTION (comment inserted by DPA) */  
      DELETE  
      FROM msdb.dbo.sysmail_allitems  
      WHERE ((@sent_before IS NULL)  
      OR ( send_request_date < @sent_before))  
     AND ((@sent_status IS NULL)  
      OR (sent_status = @sent_status)) 
/* END ACTIVE SECTION (comment inserted by DPA) */  
         DECLARE @localmessage nvarchar(255)  
         SET @localmessage = FORMATMESSAGE(14665, SUSER_SNAME(), @@ROWCOUNT) exec  
         msdb.dbo.sysmail_logmailevent_sp @event_type=1,  
         @[email protected]  
      END