SQL Server邮件相关SQL语句出现严重的ASYNC_NETWORK_IO等待事件案例

微信扫一扫,分享到朋友圈

SQL Server邮件相关SQL语句出现严重的ASYNC_NETWORK_IO等待事件案例

DPA监控发现一台SQL Server服务器最近两天执行系统存储过程msdb.dbo.sp_MailItemResultSets中的某个SQL时,出现较严重的ASYNC_NETWORK_IO等待。如下截图所示

进一步分析发现,主要是执行存储过程msdb.dbo.sp_MailItemResultSets中下面这段SQL语句出现ASYNC_NETWORK_IO等待

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

进一步分析,发现随便一个与表sysmail_allitems有关的SQL都会出现严重的ASYNC_NETWORK_IO等待:

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

另外,分析过程中发现sysmail_mailitems表只有7万多条记录,但是表的Size大小接近10G大小,如下截图所示:

这显然明显不正常,sysmail_mailitems中肯定有一些超大的邮件记录,因为这个系统经常有通过SQL,生成一些报表数据发送给用户。于是我想检查一下是否真的有一些超大的邮件。这里就必须查看sysmail_mailitems表的行大小,于是用下面脚本查看表sysmail_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

如下截图所示,还真的有一些邮件记录的rowsize超级大,正常情况下,rowsize只有1122个字节左右大小,而mailitem_id=5146768 这条记录居然有1352285196字节,如果换算成大小的话SELECT 1352285196.0/1024/1024   ~=1290M, 真是无语了!!

EXEC sp_GetRowSize ‘sysmail_mailitems’ , ‘mailitem_id’

  原因倒也不复杂,就是生成邮件的SQL出现逻辑错误,导致邮件的Body变得无比巨大,导致msdb.dbo.sysmail_allitems变得非常大,与之相关的SQL语句IO性能变差,出现ASYNC_NETWORK_IO等待。其实以前也遇到过类似案例,请见 SQL Server 2008 R2执行存储过程sp_MailItemResultSets引起大量PREEMPTIVE_OS_WAITFORSINGLEOBJEC等待 ,只是当时没有继续深挖Root Cause而已!

解决方案

那么如何解决这个问题呢? 很简单,就是删除表msdb.dbo.sysmail_allitems中的记录,让其Size变小。也可以删除那些mail_id非常大的记录。可以用下面脚本处理

/******************************************************************************************************
    Script Function        :    以下示例按条件删除数据库邮件系统中的电子邮件
*******************************************************************************************************/
DECLARE @GETDATE datetime 
SET @GETDATE = GETDATE()-2; 
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE; 
GO 

其实sysmail_delete_mailitems_sp中的逻辑也是去删除msdb.dbo.sysmail_allitems 中的记录,如下所示,在处理的过程中,需要在业务空闲的时候处理,否则会引起大量阻塞。

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, 
         @description=@localmessage 
      END 

微信扫一扫,分享到朋友圈

SQL Server邮件相关SQL语句出现严重的ASYNC_NETWORK_IO等待事件案例

Yii2学习笔记

上一篇

钟摆协议(Pendulum Protocol)安全、可扩展的去中心化数据库二层协议

下一篇

你也可能喜欢

SQL Server邮件相关SQL语句出现严重的ASYNC_NETWORK_IO等待事件案例

长按储存图像,分享给朋友