請教一下各位先進 : 我用 Multi-Thread (無論多少資料,皆以固定數量的 Thread 新增資料庫),之前資料量還不大的時候,還算正常,後來開始大量新增資料,當資料庫成長到 1G 以上大小後,就開始出現 tempdb full 的錯誤訊息,之後就會造成我的程式無法連接資料庫新增資料,後來手動將 tempdb size 變大後 (500MB),結果變得都不能存取資料庫,連接 DB TimeOut (MSSQL 的工具連接都還好,有時候會 TimeOut 無法連接).. 我有利用 sp_lock 去看 Lock 的 object,大約 780 個左右,我是利用 TADOQuery ,每個 Thread 大約新增 12000 筆資料左右(10 個 Thread 同時run), 大約跑了 60000 * 12000 筆資料後就開始變得很不正常... 請問有經驗的先進,一般是怎樣規劃 MSSQL ,以及大概有哪些限制?? 遇到已經新增太多資料的時候,該怎麼修正此問題.. ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ----

是否可說明你目前 Data Server 的硬體與軟體條件 , SQL Server 2000,2003 在Enterprise/Develop 以上有 支援到 Address Windows Extension , 就是32位元CPU與OS最大 的定址空間 , 這方面在下也不是很清處 , 僅提供參考 . PS 780 Thread 在 SEREVER RUN , 資料量 60000 * 12000 筆 太神奇了 , 請受小弟一拜 .

引言: 是否可說明你目前 Data Server 的硬體與軟體條件 , SQL Server 2000,2003 在Enterprise/Develop 以上有 支援到 Address Windows Extension , 就是32位元CPU與OS最大 的定址空間 , 這方面在下也不是很清處 , 僅提供參考 . PS 780 Thread 在 SEREVER RUN , 資料量 60000 * 12000 筆 太神奇了 , 請受小弟一拜 .

應該不是 780 個 Thread ,我確定每次只有 10 個 Thread 在執行,會等到這十個 Thread 完成後,才繼續下 10 個 Thread (慘痛經驗,沒有限制資源,資料量大時一掛便發起 19xx 個 Thread,系統就接近當了...).. 我的機器是 雙 K8 (64Bit) , SATA DISK (Raid 0 1), 1G ECC DDR RAM .. OS : Win2K DB: MSSQL 2000 空間跟效能應該還可以吧?? 因為從一個禮拜前,我就不斷的作強度測試,每天一直餵 10000 * 12000 左右的資料,不斷的跑,最後就掛掉了 .. ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ----

MSSQL 2000是否為標準版 ? 如是可能須要升級一下 , 如此才能用到32BIT最大定址空間 64G 建議你實體記億體大於3G , 相關細節請查閱SQL Server Online Book .

Hi, 發生這種狀況的原因, 就你的描述看來, 弟的推測應該是使用大量的 temp table 的關係造成, 在資料庫的 transaction log 檔剩餘空間不足以應付某一次的 temp table 使用時, 便會造成你所看到的現象, 以下兩種方式請你進行檢查,    1. sp_helpdb tempdb 檢查你的 tempdb 的 transaction 檔案大小, 建議你可以將此值修正為更大, 讓你的程式使用 temp table 時, 一次的 transaction 造成的 log 可以在完成一次 check point 前可以寫完, (由於 tempdb 的 recovery mode 為 simple, 所以不用擔心要維護此 db 的 transaction log), 注意, 是將 transaction log 的大小直接增大, 而不是將自動增量增大, 這樣仍不足以應該單一大量 transaction log 的增長. 指令下法為,

  alter database tempdb modify file (name=templog, size =200MB)

大小可以依照你一次可能使用的量而定, 200MB 已經大的恐怖了, 弟再強調一次, 這是指包裝一次的 transaction 量, 而非多次的總合, 多次的 transaction, sql server 會自行處理 check point 回寫 datafile 的時間點, 無需我們處理! 當然, 若是發生的原因並非是弟的推測, 上述方法可能無效. 2. 建議你將每個 thread 使用存取資料庫後的 connection 完全釋放, 以降低資料庫負載, 由於使用 temp table 若不自行 drop 時, 必須等到該 connection 中斷才會釋放, 就系統資源的角度看, 是比較不理想的. 所以無論是自行釋放或是中斷 connection 對系統都有相當的幫助. 3. 不知 insert 進資料庫的記錄是何種類型, 若不考慮 transaction 的狀況, 還可以利用 bulk insert 的模型, 不過這個方法並無法避免你現在發生在 tempdb 的 9002 錯誤, 只是在 insert 資料庫時, 使用 bulk insert 也可以大量減少資料庫的負載. 另外, 1G 的記憶體實在有點小, 建議你要增加超過 3G 並開啟 AWE 記憶體管理, 相信對效能會有幫助, 不過僅支援企業版! 以上為弟的一些拙見.

Dear All :    昨天太晚睡了,昏了頭,修正一下數據 : 1.每個 Thread 只處理 500 ~ 700 筆資料,以我要處理的資料單位一次批次要處理 500~700 * 25 (被切割成每 10 個 Thread 批次處理) 2.我上傳的資料量大約 60000 個單位 (60000 * 12000 估算資料量),不過今天去 Count(*) ,跑了3分半,約 142,560,000 * 2 筆紀錄 ,從這個點後,大部分的資料都被我的 Error Handle 函式處理掉 (移到 Error 目錄) 3. 資料實體實際佔用量是 11 G ...    補充一點 : transaction mode 是 simple

引言: MSSQL 2000是否為標準版 ? 如是可能須要升級一下 , 如此才能用到32BIT最大定址空間 64G 建議你實體記億體大於3G , 相關細節請查閱SQL Server Online Book .

stillalive 兄您好 : 我的 MSSQL 是個怪異的版本 ...:( Enterprise Evaluation Edition 我有注意 Memory 的問題,很正常,用工作管理員看 Loading 大約只有一半.. 我覺得應該不是記憶體空間不夠的問題,還是我誤會您要傳達給我的意思??

引言: 1. sp_helpdb tempdb 檢查你的 tempdb 的 transaction 檔案大小, 建議你可以將此值修正為更大, 讓你的程式使用 temp table 時, 一次的 transaction 造成的 log 可以在完成一次 check point 前可以寫完, (由於 tempdb 的 recovery mode 為 simple, 所以不用擔心要維護此 db 的 transaction log), 注意, 是將 transaction log 的大小直接增大, 而不是將自動增量增大, 這樣仍不足以應該單一大量 transaction log 的增長. 指令下法為, alter database tempdb modify file (name=templog, size =200MB) 大小可以依照你一次可能使用的量而定, 200MB 已經大的恐怖了, 弟再強調一次, 這是指包裝一次的 transaction 量, 而非多次的總合, 多次的 transaction, sql server 會自行處理 check point 回寫 datafile 的時間點, 無需我們處理! 當然, 若是發生的原因並非是弟的推測, 上述方法可能無效. 2. 建議你將每個 thread 使用存取資料庫後的 connection 完全釋放, 以降低資料庫負載, 由於使用 temp table 若不自行 drop 時, 必須等到該 connection 中斷才會釋放, 就系統資源的角度看, 是比較不理想的. 所以無論是自行釋放或是中斷 connection 對系統都有相當的幫助. 3. 不知 insert 進資料庫的記錄是何種類型, 若不考慮 transaction 的狀況, 還可以利用 bulk insert 的模型, 不過這個方法並無法避免你現在發生在 tempdb 的 9002 錯誤, 只是在 insert 資料庫時, 使用 bulk insert 也可以大量減少資料庫的負載. 另外, 1G 的記憶體實在有點小, 建議你要增加超過 3G 並開啟 AWE 記憶體管理, 相信對效能會有幫助, 不過僅支援企業版!

timhuang Sir 您好: 1.我的程式並沒有直接對 temp table 作存取的動作(也沒有 Create),且就在我手動將 tempdb size (增至 500) ,tempdb log size (增至 200) ,我的 AP Server 就一直出現 DB Connection TimeOut ,這時候就沒有再出現 tempdb (9002) 的訊息了,重新開機,也檢查過 tempdb 與 tempdb log size ,都很正常,不過我是利用 Enterprise Manager 更改的 (應該一樣吧??) 2.我利用 DataModule 的 Destory 來釋放 connection,且無 create tempdb 3.bulk insert 可能無法達到我的要求,原因如先前與 timhuang Sir 討論關於 "如何提升 insert 大量資料效率"那篇討論,我需要每個資料單位做transation,且要由 file 解析出資料後,轉入資料庫 .. 描述一下資料庫表格的結構 : 我 insert 的表格有三個,一個 Master ,兩個 slave (S1,S2)都與 Master PK 關聯 .. Master與 S1 資料表格儲存的資料單很單純,不是 integer 就是 varchar ,每個欄位佔的空間都不多,大約 10 多個欄位 1 個 Master 資料關聯了 500 ~ 700 個 S1 與 S2 資料 (S1,S2 數量相等) S2,只有四個欄位,但有一個欄位很特別,存放要剖析的原始檔,宣告為 image 我先 insert Master 的資料,並取得 PK 值,然後才將 PK 值帶入 insert command 內,insert S1,S2 資料 (500~700 次迴圈逐筆 insert),這樣的動作在一個 thread 內執行一次 transaction ,若 Master ,S1,S2 任一發生錯誤,需要 rollback 處理.. 可否請 timhuang Sir 指點一下,關於 MSSQL 系統會動到 tempdb 的時機?? 另,如果 Mickey Sir 與 Change36 Sir 有看到這篇的話,可否與 timhuang Sir 一起指導一下小弟如何對 MSSQL 做一些參數調整 (如設定 RAM 大小,實體資料庫/log 大小,成長百分比,tempdb 規劃大小,以及 partition 分割等等) 吐一下苦水 : 手上的 Project 人力就只有我自己,所以要身兼 DBA ,很可憐的是,一面寫系統,一面學 T-SQL ,與 MSSQL 連接的 SQL ,現在碰到問題,還要自己調整參數,兩個月學 MSSQL (設計加管理),搞得現在這種狼狽樣,實在是很慘的兩個月 ... ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ---- 發表人 -

會使用到 tempdb 的機會不少, 一般的狀況除了 #table, ##table 這種暫存資料表, 暫存預儲程序外, 尚有 1. 4種 cursor (static, dynamic, keyset, forward-only), 2. 系統自動產生的工作表 (worksheet), 如使用 group by, order by, union 等查詢時, 3. 查詢時的多工緩衝處理副本抄寫 (index spool, table spool) 4. 建立 index 時使用 SORT_IN_TEMPDB 參數 基本上會使用到 tempdb 的狀況大約如上, 就你描述的狀況還是很難判斷問題的所在, 而且現在發生的問題是 connection timeout, 也就是建立 connection 時間過長, 很有可能是資料庫太過 busy. 另外你的 transaction 包裝也相當大, 不是很理想, 這樣會鎖定大量的系統資源. 請問存取資料庫是採用 ado 嗎? 你所謂的迴圈是在 delphi 中還是在 sql command 中呢?

引言: 會使用到 tempdb 的機會不少, 一般的狀況除了 #table, ##table 這種暫存資料表, 暫存預儲程序外, 尚有 1. 4種 cursor (static, dynamic, keyset, forward-only), 2. 系統自動產生的工作表 (worksheet), 如使用 group by, order by, union 等查詢時, 3. 查詢時的多工緩衝處理副本抄寫 (index spool, table spool) 4. 建立 index 時使用 SORT_IN_TEMPDB 參數 基本上會使用到 tempdb 的狀況大約如上, 就你描述的狀況還是很難判斷問題的所在, 而且現在發生的問題是 connection timeout, 也就是建立 connection 時間過長, 很有可能是資料庫太過 busy. 另外你的 transaction 包裝也相當大, 不是很理想, 這樣會鎖定大量的系統資源. 請問存取資料庫是採用 ado 嗎? 你所謂的迴圈是在 delphi 中還是在 sql command 中呢?

timhuang Sir : 感謝您提供的資訊,十分受用 ... 我是用 ADO 元件連結的,在 Delphi 中跑迴圈,以 Prepare := true 的方式,執行 insert SQL command ... 之前我有懷疑過 SQL Server 過於 Busy,所以,我把 ADOConnection , command timeout 與 timeout 時間加長,不過似乎沒什麼效果 (我再加長試試看).. 我今天在另一台機器上裝了一個空的 DB ,重新執行我的程式,可以正常 work ,所以我想應該是 MSSQL 那邊在資料量過大的時候出現一些問題,有什麼方法(工具)或是數據可以看出 MSSQL 正處於 busy 而無法回應 Client?? 我之前觀察過 SQL Server Lock 物件,我也覺得我的 transaction 耗費太多資源,也 Lock 住太多物件,不過,我沒什麼資料庫的經驗,所以一直沒有想到好做法,只能先確保資料正確,有什麼比較好的作法嗎?? ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ----

我的 MSSQL 是個怪異的版本 ...:( Enterprise Evaluation Edition =================================== "Evaluation" 這個字整死很多人 , 建議你換正式版試試看 . ps dont forget extend pysical memory to 3G above and set up AWE . 發表人 - stillalive 於 2004/07/25 05:33:30

hi, 關於資料庫調校及規劃部分, 是一門專門的技術, 相信坊間有很多書可以參考, 但絕大多數皆是一些準則及基礎理論, 根據這些大綱, 然後配合實際的硬體環境, 就可以選擇出一套適合的配套措施. 就目前你的狀況來看, 弟能提供的建議, 就是將 transaction 的包裝再行調整, 將鎖定的資源降低, 讓系統的處理速度提高. 至於如何 monitor 系統, 可以利用 performance monitor 來將要觀察的資訊列出即可.

恕刪 只想問個問題，你實際操作的DB的Transaction Log檔案有多大了？ Orson

引言: hi, 關於資料庫調校及規劃部分, 是一門專門的技術, 相信坊間有很多書可以參考, 但絕大多數皆是一些準則及基礎理論, 根據這些大綱, 然後配合實際的硬體環境, 就可以選擇出一套適合的配套措施. 就目前你的狀況來看, 弟能提供的建議, 就是將 transaction 的包裝再行調整, 將鎖定的資源降低, 讓系統的處理速度提高. 至於如何 monitor 系統, 可以利用 performance monitor 來將要觀察的資訊列出即可.

timhuang Sir : Sorry,再凹一下,請問有沒有推薦的書籍?? 我手上有兩本旗標的書,我已經看完也具有一些基本的知識,有沒有更深入一點的書?? 對將 transaction 的包裝再行調整,可否舉個例子,目前我還是沒有什麼頭緒(是否可以切割 500 ~ 700 筆資料分批 commit 然後再處理若發生錯誤,整批 rollback??) 我今天已經把問題抓出來了,拼湊出問題的腳本了.. 1. 因為我不斷的餵資料,所以 tempdb 與其 log file 不斷的長大,當然實體存放資料的 DB 也不斷的長大,即使是 simple Mode ,其 Transaction Log 自然也成長得很可觀 2.透過不斷的觀察 tempdb/log 與自行設計之 DB/log 的使用空間,發現 tempdb 成長可到達 1G 以上,且不斷成長 (由於 tempdb 需要一段時間才會自動釋放空間,但我 feed 資料的速度太快,以致不斷累積) 3.之前應該是 tempdb 成長到 Disk 的上限 (當初只劃分 30 GB,存放資料的實體,也成長到 11G,那時 Transaction Log 約 8 GB,扣除原本佔用的空間只剩下 8 GB,我想應該是在不斷增大的情況下,超過了 Disk 上限) => 這個部分可以透過完成一批次數量後利用 dbcc 縮減 tempdb 的 size 4. 我想造成 Database TimeOut 的原因是,當初我使用工具壓縮,釋放了可用空間,卻沒有真正縮減 size , DB 嘗試想要取得更大的空間失敗(自動成長 10%),所以拒絕了 Client 的連接,故收到 TimeOut 的錯誤.. 所以應該是 Disk Full 造成的連鎖錯誤反應 ... (以上為個人推測,若有誤請用力指正) 今天將 tempdb/myDB partition 切割到比較大的磁碟後,然後繼續跑我的 AP Server 繼續狂餵資料,目前一切正常(密切觀察 size 中..) 感謝 stillalive 兄與 timhuang 前輩的耐心指導,學了不少... ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ----

書籍的部分可能還是得要自己去找找, 弟沒有特別的建議可以提供給你. 不過就你測試的狀況來推論, 你的 transaction 包裝確實是很大, 但就 tempdb 的使用量看來, 已超出弟能給的建議, 只能說將你的資料再處理, 分階段進行 transaction 的包裝, 以某 table 的一筆資料出發, 展開影響的筆數後, 將鎖定資源降低, 以此為一最小單位的 transaction, 如此一來應該可以解決你包裝 transaction 過大的問題. 至於 shrink tempdb log file 就不必要了, 因為你會用到的量就是這麼大, 即使你 shrink 下來, 之後仍會 dynamic append, 反而讓時間浪費在 allocation disk 上. 雖然說會佔用較大的磁碟空間, 但裡面的資料會因為 check point 的發生而回寫, 不需要我們來維護.

引言: 至於 shrink tempdb log file 就不必要了, 因為你會用到的量就是這麼大, 即使你 shrink 下來, 之後仍會 dynamic append, 反而讓時間浪費在 allocation disk 上. 雖然說會佔用較大的磁碟空間, 但裡面的資料會因為 check point 的發生而回寫, 不需要我們來維護.

timhuang Sir : 非常感謝您撥空與我討論,每次都收穫很多,的確,我現在觀察 tempdb 成長到 1.5 GB 便停止了,目前資料成長到 17G, 大約玩到 50 G 我就要停手了,這已經超過系統可能的容量太多了 .. ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ----