大家好，

怎麼查都查不出為甚麼 Sql Server 2005 吃掉 3.5G 那麼多的記憶體，原先懷疑程式有重複連線的問題，後來雖然查出果真如此，但等所有員工都下班後再觀察主機，從四百多個連線減少到幾十個連線，足足少掉四百個連線，但卻只釋放出 150MB 的記憶體，所以判定記憶體狂吃跟連線數量不相干。以下三首指令都執行過，並沒有再釋放出更多的記憶體。

DBCC FREESYSTEMCACHE('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE WITH NO_INFOMSGS

主機重開後，Sql Server 只吃五、六百個 MB 左右，等員工等都一一連線進來之後，記憶體開始愈吃愈多，最後達到 3.5G，員工全部都下班了，Sql Server 還是不肯釋放記憶體，真傷腦筋。

哪位大大好心幫忙解決一下？

這很難有答案, 
不過最原始的是先檢查程式端, 當每一位客戶連線至SQL後, 如何開啟資料庫及筆數多寡, 如果可行的話, 試著改成開啟一筆方式測試看看記憶體配置的問題再說吧!
===================引 用 cancer 文 章===================
大家好，

怎麼查都查不出為甚麼 Sql Server 2005 吃掉 3.5G 那麼多的記憶體，原先懷疑程式有重複連線的問題，後來雖然查出果真如此，但等所有員工都下班後再觀察主機，從四百多個連線減少到幾十個連線，足足少掉四百個連線，但卻只釋放出 150MB 的記憶體，所以判定記憶體狂吃跟連線數量不相干。以下三首指令都執行過，並沒有再釋放出更多的記憶體。

DBCC FREESYSTEMCACHE('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE WITH NO_INFOMSGS

主機重開後，Sql Server 只吃五、六百個 MB 左右，等員工等都一一連線進來之後，記憶體開始愈吃愈多，最後達到 3.5G，員工全部都下班了，Sql Server 還是不肯釋放記憶體，真傷腦筋。

哪位大大好心幫忙解決一下？

那是 sql server 的自然機制，也就是說不是bug，也不是斷線後沒釋放…  詳情請看以下的連結:

support.microsoft.com/kb/321363/en-us

建議就看這英文的，右邊可以改翻中文，但這種翻法反而讓人不太理解。

重點大概是透過 min server memory and max server memory 的設定，就可以達成你想要的。

只是，你確定你需要釋出嗎? 因為我個人的經驗是sql server 通常是全年不停機，而且整機就只為了sql。

------


蕭沖
--All ideas are worthless unless implemented--

C++ Builder Delphi Taiwan G+ 社群
http://bit.ly/cbtaiwan

編輯記錄

「SQL Server 可是重要的系統服務呢，怎麼可以輕易的釋放記憶體，記憶體釋放了，效能下降了再來說 SQL Server 很慢很爛嗎？」
以上是嘴砲，請無視！

aftcast 網友說的是正解，而且是唯一解

------
歡迎參訪 "腦殘賤貓的備忘錄" http://maolaoda.blogspot.com/

謝謝各位的回應，主機是專供 Sql Server 用的，而程式碼又真的相當複雜。
我先前沒有設 Sql Server 的最大記憶體使用量，於是發生 Sql Server 吃太多記憶體，只剩一點點給 OS用，OS 的一些基本程序一直使用虛擬記憶體，硬體 IO 大增，CPU 都在 85% 以上，查詢和存檔都變得非常的慢，大家都以為是當機了，後來把最大記憶體設為 3072MB，留 1 G 給 OS，但它會多吃，最高會到 3.4G 多一些。
現在上班時間，可用記憶體，都在 60MB 到 200MB 之間，但大部份時間都在 100MB 以下，常常有員工打內線向我抱怨資料處理速度太慢，我走到他們的位置觀察，畫面都反白，同樣的操作再做一次，又變回正常速度，員工都說 "電話怕你，你來看就會變成沒問題"，速度變慢都是短時間的，過一陣子又恢復正常。
那些 Sql Server 保留的記憶體，是待用的？還是已使用不能再利用？如果是前者，就不用釋放記憶體了。
工作管理員還看到 ReportServicesServece.exe，佔用一百多 MB，太多了，我家的電腦記憶體只有 256MB，要跑 XP、上網等程式，都夠用，光一支報表程式卡住一百多 mb，實在又夠多餘，我沒有用微軟的報表系統，把它關掉，在工作管理員中關閉沒多久，它又自動跑出來，好像只能從開機程式表下手。

cancer你好，
1/ 照上面的那篇微軟的文章裡，它就有提到最大的記憶體不包含com元件等dll的載入之類的。所以實際上你看到的會超過最大量一些。
2/ 多出來的是待用的。
3/ reportservice之類的占很大的問題…因為sql server 本來就是預設跑在"server等級的"機器上，能叫server等級而且運作的，一般memory不太可能小，尤其是近年來ram那麼的便宜。
4/ 最後的重點: 我想你會去注意memory的問題，是有人回你說，怎麼變慢了… 這應該是有觸發你去注意的點。在此，我提出個人的經驗，這類的問題通常是使用的sql的語法不夠精鋉造成，如: 長時間lock某些資料，長時間的transaction，update的筆數過大，dead lock等問題所造成。也就是說是 「程式人員」的問題機率高一點。mssql 也是商用級的軟體，平心來說，還算很ok。以前我在某電信公司上班，用了很多的mssql server，每分每秒都有人在call電話，也在記資料，還有客戶在查資料等…… 如果那麼慘，我看也不用營業了。問題在一般的程式人員，寫的sql語法真的算普通級，不是大師級，所以常常會有短時間卡卡的問題。此外，sql雖每天不關機，但不知道是否每晚，如半夜的時候，有做維護資料計劃，把一些舊資料往另一個table搬。而主資料則常常保持在一個期間內的筆數? 當某個table肥到不行時，效能自然也會變差，至於轉到"歷史"資料table的東西，你又該怎麼把線上的join在一起? 這就是專業資料庫人員與一般的分野了。又如，我們給客戶查詢的資料，通常是用replication的"同步"db。而寫的部份則才用正的那個db。寫與查是分開的。又，比如說，對於不是很重要需要即時的查詢，我們都要用 dirty query，如 select * from foo with (nolock) where ...... 。還index的精心設計。還有每個sql都經過一些 query analyzer的時間測式，找出最佳語法 (我們知道要達成同一個目的，常有數種寫法)…等等
總之，資料庫事實上是很深的學問，不是單純 select / delete /update等…
還有，有些人喜歡在程式裡用 數個for來找資料，這也是可以用不同的語法來處理，這種差異可達 2-3 分鐘的差別出來。

太多的技巧了，仔細去查問題。至於mssql本身的問題，我想應該是最小的吧!

===================引 用 cancer 文 章===================
謝謝各位的回應，主機是專供 Sql Server 用的，而程式碼又真的相當複雜。
我先前沒有設 Sql Server 的最大記憶體使用量，於是發生 Sql Server 吃太多記憶體，只剩一點點給 OS用，OS 的一些基本程序一直使用虛擬記憶體，硬體 IO大增，CPU 都在 85% 以上，查詢和存檔都變得非常的慢，大家都以為是當機了，後來把最大記憶體設為 3072MB，留 1 G 給 OS，但它會多吃，最高會到 3.4G 多一些。
現在上班時間，可用記憶體，都在 60MB 到 200MB 之間，但大部份時間都在 100MB 以下，常常有員工打內線向我抱怨資料處理速度太慢，我走到他們的位置觀察，畫面都反白，同樣的操作再做一次，又變回正常速度，員工都說 "電話怕你，你來看就會變成沒問題"，速度變慢都是短時間的，過一陣子又恢復正常。
那些 Sql Server 保留的記憶體，是待用的？還是已使用不能再利用？如果是前者，就不用釋放記憶體了。
工作管理員還看到 ReportServicesServece.exe，佔用一百多 MB，太多了，我家的電腦記憶體只有 256MB，要跑 XP、上網等程式，都夠用，光一支報表程式卡住一百多 mb，實在又夠多餘，我沒有用微軟的報表系統，把它關掉，在工作管理員中關閉沒多久，它又自動跑出來，好像只能從開機程式表下手。

------


蕭沖
--All ideas are worthless unless implemented--

C++ Builder Delphi Taiwan G+ 社群
http://bit.ly/cbtaiwan

aftcast 您好，謝謝解答。
當初是想設 3G，留 1G 給 OS 用的，原來這不包括 Sql Server 的 dll 佔的記憶體，變成佔了 3.2 到 3.5G 之間，OS 剩 500mb 到 800mb 可以用，加上 ReportServicesService 又佔了 100mb 左右，系統不夠用了，看起來又要設定最大記憶體使用量了。
至於 Sql 指令的寫法，因為程式碼太複製、太龐大，幾百個 dll 檔，主要畫面光是存檔的 Delphi 程式碼就有好幾百行，有十幾個資料表用 UpdateBatch(arAll) 一次存檔，表頭三百個欄位，主項次資料表也是三百個欄位，還要比對欄位內容有沒有異動，有異動的，要新增一筆異動記錄存到資料庫，異動前和異動後的內容都要存起來，有事才能找人來負責。新增一張帳單，必須讀取資料庫十一次，來把相關欄位先填好，再讓使用者輸入。能改善就改善，不能改善就算了，只要能做到 "平均時間不要差距太多"，這樣就可以了，如果現在查詢花了十秒，我可以解釋是資料庫資料龐大，但如果等一下相同指令再查一次，花了兩分鐘，這樣就說不過去了。

>>> 異動前和異動後的內容都要存起來，有事才能找人來負責
這是好的。但我建議用trigger來處理，自動的新增至"異動log"。我以前的電信公司也是如此。

>>>新增一張帳單，必須讀取資料庫十一次，來把相關欄位先填好，再讓使用者輸入
不知是否有使用 view dirty read ( with (nolck))的方式來查，並將之寫成 stored procdure (sp)，這樣效能又會差很多

如果以上的做都通通都抓入 dephi裡的 dataset中，然後 for ，然後甚至在裡面又FOR 幾次，然後填值。這樣就會慢到不行! 我的經驗就是 實測可差到好幾分鐘的差別。

>>>>>如果現在查詢花了十秒，我可以解釋是資料庫資料龐大，但如果等一下相同指令再查一次，花了兩分鐘，這樣就說不過去了。

這樣的說法也不太公平。除非你限制整個SERVER僅一個人連線，或許這樣的測式才有意義。若是N個人同時連上去的話，這次快，下次慢，這就變得很"正常"。原因就是若有不良的SQL語法就會造成各種LOCK (含最輕的與最重的)。比如說，某人剛好在UPDATE 資料，B這個人剛好要查，那這時候B就會發現慢很多。而若B在查的時候，剛好沒人查，或是別人也僅是SELECT，那就又快一點。 所以我才會一直和你說 WITH (NOLOCK) 這個指令有沒有用到。 我所見過極多數都不知道要如此來用。比如說，印帳單，印的是上個月的資料，非即時性的。於是 NOLOCK 的方式不僅可得到正確答案，而且不會鎖，也不怕鎖。在N人上線時，有人查，有人交易等等複雜的情形下，這樣NO LOCK效能就整個拉出差異來。

以往的經驗，幾乎都是寫 SP，寫VIEW，寫TRIGGER來處理大資料庫系統。即都是實做在SEVRVER上的。程式(如DELPHI)僅是很單純的叫一下，也不太FOR N次，就連要寫，也是呼叫SP，讓SP可能一次寫N個TABLE。


以上是我以前任資料庫管理師時帶新人重要的課題教學之一。請參考一下。


註: 又如果這個SERVER剛裝的時候很快，然後經過半年、一年或更久，就變得很慢，那也是我上樓回的，就是TABLE變大了。而這時候該注意的是，每天半夜要做資料庫維護計畫，含移動一些資料到別的TABLE，含RE-INDEX 等，這樣才可以保持從安裝，到數年，速度都沒有太大的改變。


===================引 用 cancer 文 章===================
aftcast 您好，謝謝解答。
當初是想設 3G，留 1G 給 OS 用的，原來這不包括 Sql Server 的 dll 佔的記憶體，變成佔了 3.2 到 3.5G 之間，OS 剩 500mb 到 800mb 可以用，加上 ReportServicesService 又佔了 100mb 左右，系統不夠用了，看起來又要設定最大記憶體使用量了。
至於 Sql 指令的寫法，因為程式碼太複製、太龐大，幾百個 dll 檔，主要畫面光是存檔的 Delphi 程式碼就有好幾百行，有十幾個資料表用 UpdateBatch(arAll) 一次存檔，表頭三百個欄位，主項次資料表也是三百個欄位，還要比對欄位內容有沒有異動，有異動的，要新增一筆異動記錄存到資料庫，異動前和異動後的內容都要存起來，有事才能找人來負責。新增一張帳單，必須讀取資料庫十一次，來把相關欄位先填好，再讓使用者輸入。能改善就改善，不能改善就算了，只要能做到 "平均時間不要差距太多"，這樣就可以了，如果現在查詢花了十秒，我可以解釋是資料庫資料龐大，但如果等一下相同指令再查一次，花了兩分鐘，這樣就說不過去了。

------


蕭沖
--All ideas are worthless unless implemented--

C++ Builder Delphi Taiwan G+ 社群
http://bit.ly/cbtaiwan

編輯記錄

先結案，還可以再討論。aftcast 大的 with(nolock) 是最佳解決方法，有些查過的資料不需要記住，不要鎖住比較節省記憶體。
1.欄位異動記錄，這個部份用 trigger 來記錄會有問題，我的資料表有些欄位超多，最多的有三百個欄位，我只挑出一百多個欄位來比對，因為有些是異動時間、異動人員、計算出來的數字等等非使用者能夠修改的欄位，這些欄位不用記錄異動，也不該記錄，不然，主管要查異動的時候，會有一大堆不必要的異動資料。而且，我的異動判斷是 Trim() 過後才判斷的，例如 "AB" 和 "AB "，不能算是有異動，尤其是 Memo 欄位，最後一行多按一個 Enter，不能算有異動。
2.資料抓到 DataSet 後用迴圈處理是一定要的，但我是用 ltBatchOptimistic，到儲存時，才一次存到資料庫，都是本機記憶體中處理，所以沒有問題。本機處理是必須的，有些還一定不能用 Sql 指令，例如 Round()，資料庫的 Round() 是 Banking Rounding，我們的財政人員對 Banking Rounding 很感冒，常常被財務人員問為什麼 4.5 沒有進位到 5？怎麼解釋都沒用。
3.相同指令但查詢時間差異太大，這個部份，我是以全部員工都就位的情形下來評估的，一直以來查詢時間都不會相差太大，但最近卻出現差異太大，應該是資料變多的關係，Sql Server 抓在手頭上的記憶體還是不夠用，我會把程式一支一支拿出來，把單純查詢不會回存的指令，統統加上 with(nolock) 不要鎖住，這樣應該有很大的改善，謝謝 aftcast 大大。
4.SP,View,Trigger，Trigger 我沒用過，View 的部份我不敢用，據同事的經驗，View 是先抓整個資料表之後再過濾出資料，據說速度很慢，SP 對效能很有幫助，但我的程式很多都是無法用 Sql 來計算的，例如要把運費分攤到每一個項次，因為是台幣，必須要用整數，分攤後的項次加總，一定有一點點差異，這個時候，要再做分攤，例如有 8 個項次，要攤 12 元的運費，每個項次先加一元，剩下四元，分給最後四個項次，有時候還要挑金額最多的來攤，不然，單價一元，數量一元，攤一元運費給它，客戶會問為什麼單價一元，數量是一件，貨價怎麼會是二元？總之，程式很複雜，只能用 Delphi 來計算，算完第一次，再算第二次，甚至第三次。
5.Index 的部份，另一位同事有幫我加一些，但加的不多，Index 會提高效率，但會不會對修改產生效率上的影響？我怕存檔時候會變慢。