SQL Server 2008 R2執行存盤程序sp_MailItemResultSets引起大量PREEMPTIVE_OS_WAITFORSINGLEOBJEC等待

從監控工具DPA中發現一個資料庫（SQL Server 2008 R2）的等待事件突然彪增，下鉆分析發現資料庫執行存盤程序sp_MailItemResultSets時，引起了非常嚴重的等待(High Wait)，而主要的等待事件為PREEMPTIVE_OS_WAITFORSINGLEOBJEC， 如下截圖所示:

查詢正在執行的SQL，發現會話正在執行下面SQL(存盤程序sp_MailItemResultSets中的一個SQL陳述句)，等待事件為ASYNC_NETWORK_IO，  

USE msdb;

go

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

關于ASYNC_NETWORK_IO與PREEMPTIVE_OS_WAITFORSINGLEOBJEC的關系如下：

這個等待事件表示一個執行緒正在向外部客戶端行程同步某個物件的資料，因此出現此種等待，而且通常和ASYNC_NETWORK_IO等待事件同時出現，根據我的觀察，查詢正在執行的SQL，等待事件為”ASYNC_NETWORK_IO“而并非”PREEMPTIVE_OS_WAITFORSINGLEOBJEC“

關于這個等待事件的更多詳細資訊，具體見鏈接“PREEMPTIVE_OS_WAITFORSINGLEOBJECT”，當前資料庫版本為SQL Server 2008R2

Description:

This wait type is when a thread is calling the Windows WaitForSingleObject function for synchronization with an external client process that is communicating using that object.

Other information:

This wait type is commonly seen in conjunction（同時出現） with ASYNC_NETWORK_IO, depending on the network transport used to communicate with the   client, so to troubleshoot, follow the same steps as for ASYNC_NETWORK_IO.

Note that when a thread calls out to Windows, the thread changes from non-preemptive (SQL Server controls the thread) to preemptive (Windows controls the thread) mode. The thread’s state will be listed as RUNNING, as SQL Server doesn’t know what Windows is doing with the thread.

確實是一個非常奇怪的現象，然后我又去檢查系統的應用日志，結果發現大量的錯誤：

錯誤資訊比較奇怪，讓人摸不著頭腦，也沒有看到有相關資料介紹，主要有下面兩種錯誤：

1：Database Engine Instance=xxxxx;Mail PID=7248;Error Message:The connection is not open.

2: Database Engine Instance=xxxxx;Mail PID=7248;Error Message:Exception of type 'System.OutOfMemoryException' was thrown.

驗證SQL陳述句性能， 發現SQL陳述句的確非常慢，從執行計劃來看，沒有什么例外情況，而且這個也是系統資料庫，不應該存在一些索引問題，

但是檢查dbo.sysmail_mailitems表，發現此表記錄數為2722，但是表的大小接近8G了，非常不正常，對比了其它幾個資料庫服務器，發現這個表非常小，檢查郵件記錄里面是否有大量附件，也沒有發現有大量附件，

處理問題的時候，沒去定位是那潭訓那些記錄占用了大量空間，急著解決問題，放棄分析這些情況了，可惜了！

官方也沒有相關資料，只能猜測是因為dbo.sysmail_mailitems的大小引起了性能問題，然后我嘗試用下面SQL清理這個表的記錄

/******************************************************************************************************

    Script Function        ：    以下示例洗掉資料庫郵件日志中所有失敗的電子郵件

*******************************************************************************************************/

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp   

    @sent_status = 'failed' ;  

GO 

/******************************************************************************************************

    Script Function        ：    以下示例洗掉資料庫郵件系統中的所有電子郵件

*******************************************************************************************************/

DECLARE @GETDATE datetime  

SET @GETDATE = GETDATE();  

EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;  

GO  

最后清理過后驗證發現，這個存盤程序的確非常快了，資料庫中該等待事件直接消失了，系統應用日志中關于Mail PID的錯誤也消失了，后續觀察發現，這個表也變得特別小了，完全沒有之前那么大了，

標籤：SQL Server

上一篇：分享攢了多年的mssql腳本

下一篇：解決SQL SERVER 2012自增ID突然斷開的問題（ID突然增加1000的）