憶一次因SQLServer記憶體占用飆高導致的工廠停工

一、還有五小時到達戰場

       現在回想起來，整件事還挺離譜的......

       中午午休，正在公司總部（重慶）附近和同事們一起享受午餐；

       突然接到上司電話，要求我立即出發去廣州一趟，今天中午有個工廠因為我們的程式出問題導致停工了！！！

       我立即反饋，由於我們的程式都是運行在Windows上的，只要給我遠程桌面許可權，我可以馬上開始解決，爭取馬上讓產線復工！

       上司：“你還是立即出發，過去一趟吧，這件事特別嚴重，於情於理得派人過去一趟，而且客戶也強調要求派人到現場的。”

       得，背上我心愛的小包，出發吧......

二、抵達現場

       一路計程車與飛機，抵達現場已是五小時後，立即開始作戰！

       首先，查看一下環境：環境整潔、管理完善的小型工廠，設備不多；除了廠方的生產設備以外，屬於我們的只有一臺伺服器、多台觸屏一體機、一些其他設備。

       生產設備是受我們的設備防呆管控的；現因我們的設備一直報錯，導致生產設備無法生產；一條線的幾個工人已經有薪休假一個班次了......

三、界定問題設備

       首先，檢查所有觸屏一體機的系統、程式、日誌，發現全部自身無異常，但所有訪問伺服器介面均無響應；

       “一些其他設備”只是簡單的輸入或輸出設備，在此事上不重要；

       已初步確認是伺服器上有問題。

四、界定問題程式與原因

       從各管理員那裡獲得相應許可權，接入區域網，用遠程桌面進入伺服器。

       伺服器的配置，我看了直接傻眼，往好點說，除了顯卡，和我十年前用九千塊買的家用游戲電腦差不多。

       伺服器里只有IIS和SQLServer2008，直接查看任務管理器，發現SQLServer記憶體占用80+%，其他程式占用不多；

       這種情況，按我這些年遇到的各種情況，我知道的基本上只有以下四種猜測了：

              1、邏輯死迴圈

                     ①數據與程式共同造成死迴圈

                            這種情況一般出現在多年前開發的程式上，也不排除現在還有人這麼寫......

                            舉個例子，有兩條數據{ID:1,ParentID:2}與{ID:2,ParentID:1}，於是程式反覆調用Select ID,ParentID from x where ID=1與Select ID,ParentID from x where ID=2。

                     ②SQL死迴圈

                     以上兩種邏輯死迴圈，使用SQL Server Profiler跟蹤一下SQL語句就明白了；

                     不過，我這次跟蹤發現沒有新加入執行的SQL，先排除邏輯死迴圈。

              2、死鎖

                     執行以下SQL語句查詢正在等待鎖釋放的語句：

--查詢死鎖語句,獲取blocked欄位值
select * from sys.sysprocesses where spid>50 and blocked<>0
----查詢阻塞或者死鎖的語句（@blocked替換為查詢到的blocked欄位值）
--dbcc inputbuffer(@blocked)
----殺死死鎖（@blocked替換為查詢到的blocked欄位值；不懂死鎖的同行還是不要輕易執行此kill語句）
--kill @blocked

                     很遺憾，查出來沒有數據，本次也不是死鎖造成的。

              3、記憶體泄漏

                     網上有很多描述各種情況下SqlServer2008記憶體泄漏的文章，這裡不多做描述，他們的解決辦法都是升級至Microsoft SQL Server 2008 SP3。

                     很可惜，伺服器上就是SP3，先跳過此情況。

              4、索引與緩存

                     前面幾種情況均不是，只能猜測是此情況引起的，且索引與緩存造成記憶體飆高的情況，我也暫時不知道怎麼檢查，一般直接釋放記憶體試試。

                     順便提一下，下麵幾個語句可以清除緩存，使SQLServer重新填充緩存，但不會釋放已占用記憶體，所以不適用解決高記憶體占用：

--創建一個檢查點，在該點保證全部臟頁都已寫入磁碟，從而在以後的恢復過程中節省時間。
CHECKPOINT
--清除存儲過程相關的緩存
DBCC FREEPROCCACHE
--清除會話緩存
DBCC FREESESSIONCACHE
--清除系統緩存
DBCC FREESYSTEMCACHE('All')
--清除所有緩存
DBCC DROPCLEANBUFFERS

五、揣著糊塗裝明白，開始解決問題

       通過設置最大伺服器記憶體，避免記憶體占用過高，並釋放多占用的記憶體。

       伺服器共有16GB記憶體，這裡我設置為8192MB。

       任務管理器里SQLServer進程的記憶體占用比開始緩慢下降，回到生產區操作觸屏一體機均已正常！！

       通知客戶，可以恢復生產了。

       由於發生大故障，客戶也不敢立即放我走，輓留我幾天......

       接下來幾天，我也不想閑著，聯繫公司給與許可權，拉取下來代碼，將各設備上的程式挨個解決死迴圈風險、解決死鎖風險、空值檢測、演算法優化等。

       然後，寫了一份詳細文檔，介紹了下次再出同樣問題該如何解決，給與客戶。（這很難界定，我方不知道是沒有還是沒有收到每年的服務費，這似乎不該我方分配人力負責；對方有第三方運維人員，但並不想管，推給我們了；客戶自己不懂怎麼運維，無法承接；唉，一筆爛賬。）

       最後，停留一周，飛回總部了。

六、一個月後，居然又出現相同問題了？

       一個月後，深夜突然來電話，居然又出現故障了？？？

       不過，由於此次正在其他大廠出差，一時走不開，只能遠程解決了。

       深夜把對方伺服器管理員騷擾一下，得以進入遠程桌面；

       將上面檢查再做一遍，發現依舊沒有任何頭緒，而且最大伺服器記憶體是8192MB，但實際占用早已超過！

       僥幸，將最大伺服器記憶體改為1024MB，又改回8192MB，居然開始了記憶體釋放！！

       看來，還是會有一些意外情況會導致過高占用，需要定時監測、釋放伺服器記憶體：

/*
    將以下SQL設置為SQLServer代理作業，每五分鐘執行一次
*/
declare @mb bigint,@set_mb int,@sql nvarchar(1000),@max int,@min int,@now sql_variant
set @max=4096--最大伺服器記憶體(MB)
set @min=1024--臨時伺服器記憶體(MB)：如果出現意外情況，實時伺服器記憶體超過最大伺服器記憶體，將會將最大伺服器記憶體設置為臨時伺服器記憶體，以實現強制釋放部分記憶體
set @mb=(
    SELECT top 1 (virtual_address_space_committed_kb / 1024) AS '已提交或已映射到物理頁的已保留虛擬地址空間量（MB）'
        FROM sys.dm_os_process_memory
)--實時伺服器記憶體(MB)
set @now=(
    select top 1 value from sys.configurations where name='max server memory (MB)'
)--之前設置的最大伺服器記憶體(MB)
if(@mb>@max)
    set @set_mb=@min
else
    set @set_mb=@max
if(@now!=@set_mb)
begin
    set @sql='
        exec sp_configure ''show advanced options'', 1
        RECONFIGURE
        exec sp_configure ''max server memory'', '+convert(varchar(500),@set_mb)+'
        RECONFIGURE
    '
    EXECUTE(@sql)
end

七、後記

       後來，我得知此次派遣，公司居然賺了一萬塊錢，倒也不覺得直接派去現場待一星期離譜了......