線上訂房服務-台灣趴趴狗聯合訂房中心
發文 回覆 瀏覽次數:9407
推到 Plurk!
推到 Facebook!

Sql Server 2005 吃掉 3.5G 記憶體,怎麼辦?

答題得分者是:sryang
cancer
高階會員


發表:58
回覆:319
積分:190
註冊:2004-07-31

發送簡訊給我
#1 引用回覆 回覆 發表時間:2011-05-04 20:23:43 IP:220.128.xxx.xxx 未訂閱
大家好,

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

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

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

哪位大大好心幫忙解決一下?
P.D.
版主


發表:603
回覆:4038
積分:3874
註冊:2006-10-31

發送簡訊給我
#2 引用回覆 回覆 發表時間:2011-05-04 23:03:41 IP:118.169.xxx.xxx 未訂閱
這很難有答案, 
不過最原始的是先檢查程式端, 當每一位客戶連線至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 還是不肯釋放記憶體,真傷腦筋。

哪位大大好心幫忙解決一下?
aftcast
站務副站長


發表:81
回覆:1485
積分:1763
註冊:2002-11-21

發送簡訊給我
#3 引用回覆 回覆 發表時間:2011-05-04 23:16:29 IP:122.126.xxx.xxx 訂閱
那是 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
編輯記錄
aftcast 重新編輯於 2011-05-04 09:17:21, 註解 無‧
sryang
尊榮會員


發表:39
回覆:762
積分:920
註冊:2002-06-27

發送簡訊給我
#4 引用回覆 回覆 發表時間:2011-05-05 07:30:18 IP:211.79.xxx.xxx 訂閱
「SQL Server 可是重要的系統服務呢,怎麼可以輕易的釋放記憶體,記憶體釋放了,效能下降了再來說 SQL Server 很慢很爛嗎?」
以上是嘴砲,請無視!

aftcast 網友說的是正解,而且是唯一解
------
歡迎參訪 "腦殘賤貓的備忘錄" http://maolaoda.blogspot.com/
cancer
高階會員


發表:58
回覆:319
積分:190
註冊:2004-07-31

發送簡訊給我
#5 引用回覆 回覆 發表時間:2011-05-05 09:17:37 IP:220.128.xxx.xxx 未訂閱
謝謝各位的回應,主機是專供 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,實在又夠多餘,我沒有用微軟的報表系統,把它關掉,在工作管理員中關閉沒多久,它又自動跑出來,好像只能從開機程式表下手。
aftcast
站務副站長


發表:81
回覆:1485
積分:1763
註冊:2002-11-21

發送簡訊給我
#6 引用回覆 回覆 發表時間:2011-05-05 09:46:55 IP:210.64.xxx.xxx 訂閱
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
cancer
高階會員


發表:58
回覆:319
積分:190
註冊:2004-07-31

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


發表:81
回覆:1485
積分:1763
註冊:2002-11-21

發送簡訊給我
#8 引用回覆 回覆 發表時間:2011-05-05 20:59:40 IP:122.126.xxx.xxx 訂閱
>>> 異動前和異動後的內容都要存起來,有事才能找人來負責
這是好的。但我建議用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 重新編輯於 2011-05-05 07:05:08, 註解 無‧
cancer
高階會員


發表:58
回覆:319
積分:190
註冊:2004-07-31

發送簡訊給我
#9 引用回覆 回覆 發表時間:2011-05-06 09:29:57 IP:220.128.xxx.xxx 未訂閱
先結案,還可以再討論。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 會提高效率,但會不會對修改產生效率上的影響?我怕存檔時候會變慢。
系統時間:2024-03-29 9:55:13
聯絡我們 | Delphi K.Top討論版
本站聲明
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇!