K3資料庫優化方案

2020 年 1 月 7 日
筆記

K/3 系統性能優化解決方案 作者：詩歡

–重建索引速度較慢，請在系統空閑時間進行

DBCC DBREINDEX(t_icitem)

DBCC DBREINDEX(t_item)

DBCC DBREINDEX(t_itemclass)

DBCC DBREINDEX(t_itemright)

DBCC DBREINDEX(t_user)

DBCC DBREINDEX(t_group)

if not exists(select 1 from sysindexes where name='ix_group_fgroupid')

create index ix_group_fgroupid on t_group(fgroupid)

if not exists(select 1 from sysindexes where name='ix_itemright_ftypeid')

create index ix_itemright_ftypeid on t_itemright(ftypeid)

1 SQL Server調整

當用戶使用K3系統一段時間以後，發現系統的響應時間越來越長。這種情形往往是由於賬套資料庫缺乏維護引起的。缺乏維護的資料庫會存在過多地碎片、過期的統計、隱含著可能的錯誤查詢結果的資料庫的邏輯和物理的不一致性，這些都會直接影響系統的性能。這裡介紹解決上述賬套資料庫性能問題常用的方法。

1.1 使用DBCC語句發現和解決上述問題。

DBCC: 資料庫一致性檢查器。

打開SQL 查詢分析器 ，執行如下語句。

u DBCC SHOWCONTIG 顯示指定表的數據和索引的有關數據碎片的資訊DBCC SHOWCONTIG(表名[，索引名])

在有大的改動的表，引入數據的表，或者引起低效查詢的表上使用該語句。

例：DBCC SHOWCONTIG（』T_ITEM』）

u DBCC DBREINDEX 重建指定資料庫中表的一個或多個索引。

例1：重建某個索引

                               DBCC DBREINDEX ('T_ITEM', uk_item2, 80)

例2：重建所有索引

DBCC DBREINDEX ('T_ITEM',』』,80)

u DBCC SHOW_STATISTICS 顯示指定表上的指定目標(例如一個索引名稱))的當前分布統計資訊。這些統計資訊是被SQL Server查詢優化器使用的DBCC SHOW_STATISTICS(表名，目標)

例：DBCC SHOW_STATISTICs('t_item','pk_item')

u sp_updatestats & UPDATE STATISTICS 更新統計資訊； sp_updatestats 對當前資料庫中所有用戶定義的表運行 UPDATE STATISTICS.

使用UPDATE STATISTICS 語句的時機：在一個空表上創建一個索引，然後在以後應用它。執行TRUNCATE TABLE語句，然後在以後重新應用該表。通過使用FULLSCAN或SAMPLE選項請求明細的索引統計資訊。

例1. UPDATE STATISTICS T_ITEM

例2. UPDATE STATISTICS T_ITEM(PK_ITEM)

例 3. USE AIS20011203150410

EXEC sp_updatestats

u DBCC CHECKTABLE 檢查指定表或索引視圖的數據、索引及 text 、ntext 和 p_w_picpath 頁的完整性。如果你相信一個指定的表可能被破壞了，這條命令非常有用。

u DBCC CHECKDB 檢查指定資料庫中的所有對象的分配和結構完整性。這條命令發現並修復資料庫地址分配和表內部的全部錯誤。實際上，CHECKDB驗證資料庫內部一切事物的完整性，但是，DBCC CHECKDB是一個耗費CPU和磁碟資源的操作，每個需要檢查的數據都必須首先從磁碟中讀出到記憶體中。而且，DBCC CHECKDB 使用tempdb進行排序。要獲得較高的DBCC性能，推薦在下面的情況下運行DBCC：

l 在系統使用率較低的情況下運行CHECKDB；

l 確信當前沒有執行其他磁碟I/O操作，如磁碟備份操作；

l 將tempdb放在另一個磁碟系統上，或者放在一個快速磁碟子系統上；

l 為tempdb提供足夠的空間，運行DBCC帶上參數ESTIMATE ONLY（顯示執行 DBCC CHECKDB 操作所需tempdb 空間的數量），估計tempdb需要多少磁碟空間；

l 避免運行消耗大量CPU時間的查詢和批處理；

l 在DBCC命令運行時，減少事物活動；

l 使用NO_INFOMSGS選項（壓縮使用空間使用的資訊和報告）減少處理和tempdb使用率。

例：DBCC CHECKDB ('AIS20011203150410') WITH NO_INFOMSGS,ESTIMATEONLY

u DBCC SQLPERF 提供有關所有資料庫中的事務日誌空間使用情況的統計資訊。日誌文件的閑余空間的減少，會降低系統的性能。系統會在備份時日誌截斷日誌文件，所以要求用戶要制定一份良好的備份方案。

例：DBCC SQLPERF ( LOGSPACE )

1.2 使用資料庫維護計劃

使用資料庫維護計劃器是一種標準且方便的可對多個賬套資料庫同時設置維護任務維護模式。下面介紹其建立方法：

本方案所介紹的資料庫維護計劃側重於資料庫的優化,即性能的提高。

1) 打開Enterprise Manager，展開伺服器，展開管理,然後單擊資料庫維護計劃。從操作（Action）中選擇新建維護計劃 ,可以看到圖4.1所示的歡迎螢幕，單擊下一步按鈕。

2) 選擇資料庫，選擇K3賬套所在的資料庫（可選一個或多個）。單擊下一步按鈕。

圖2 選擇資料庫

3) 更新資料庫優化資訊。選擇重新組織數據和索引頁，選擇使用原有可用空間重新組織頁面。選擇當增長超過50MB時，從資料庫文件中刪除未使用空間，收縮後保留的可用空間為10%的數據空間。單擊下一步按鈕。

圖3更新資料庫優化資訊

4) 檢查資料庫完整性。選擇檢查資料庫完整性,包含索引以及嘗試修復所有小問題。單擊下一步。

圖4 檢查資料庫完整性

5) 指定資料庫備份計劃,備份在優化方案中暫不考慮，跳過，單擊下一步。

圖5資料庫備份計劃

6) 指定事務日誌備份計劃在優化方案中暫不考慮，跳過，單擊下一步。

圖6指定事物備份計劃

7) 生成報表。選擇將報表寫入目錄中的文本文件，選擇刪除早於4周的報表文件。或者選擇將電子郵件報表發送到操作員，然後花時間閱讀這個報表，看看資料庫中是否有任何需要注意的問題。單擊下一步。

圖7生成報表

8) 維護計劃歷史記錄。

SQL Server每次運行時保持維護計劃的歷史。可以瀏覽這個歷史，看看操作中何時遇到故障，然後確定故障原因。如果只有單台機器，則要在本地伺服器存放歷史紀錄，但如果網路中又多台機器，則要將歷史紀錄存放在中央伺服器中，以便從各台機器上方便的訪問。下面選擇預設在本地存放1000行歷史紀錄。單擊下一步。

圖 8 維護歷史紀錄

9) 完成資料庫維護計劃嚮導。用於命名和檢查具體工作，在計劃名中輸入：K3賬套資料庫維護計劃。單擊完成按鈕生成計劃。

圖9 完成資料庫維護計劃嚮導

1.3 發現死鎖和消除死鎖

死鎖形成的原因是不同的，有的死鎖系統可以自動地偵測和消除而另外一些則需要管理員調整請求

死鎖發生在兩個或多個進程同時等待被其中一個進程保留著的鎖。該進程將不會釋放它保留的鎖直到它獲得被其它進程保留的資源，反過來也一樣。當一個死鎖被被確認以後，SQL Server通過自動選擇可以立即打斷死鎖的執行緒來結束死鎖。

許多阻塞的問題發生在由於一個進程保留鎖過長時間，引起一系列被阻塞的進程等待其它進程釋放鎖。SQL Server不能識別阻塞鎖並自動地解決它們,所以必須監控阻塞鎖的存在並手工消除它。

在一個應用中建立一個鎖的超時設置是一個防止阻塞鎖的方法。這允許應用監控阻塞鎖並回滾進程而不是不確定地等待或阻塞語句的重提交。

下面，介紹手工消除死鎖的方法：

1) 系統長時間沒有響應，可以在SQL查詢分析器中執行系統存儲過程sp_lock 和sp_who ，如圖所示，spid 57正在等待資源。

Spid ：系統進程 ID

執行命令：sp_who 57 可以得到關聯該進程和鎖的用戶的登錄名稱，主機名稱和狀態等資訊。

圖1. 運行sp_lock顯示的鎖資訊

2) 轉到SQL Server Enterprise Manager,展開管理，展開當前活動， 展開鎖 / 進ID ,如圖所示，spid57被spid56阻塞。

圖2. 顯示鎖的阻塞情況

3) 雙擊spid56,然後單擊取消進程 （Kill Process）。

4) spid57阻塞解除。

2 硬體調整

硬體調整，是為K3系統的正常運行要求的工作量提供足夠的硬體資源的行動。要調整系統的硬體，就要決定可以為K3系統分配那些資源以改進其性能，這些資源包括附加的記憶體、CPU、I/O資源或所有這些資源的組合。調整系統性能的工作主要涉及決定應該增加哪種資源，以及增加多少資源。

硬體調整是非常重要的，因為許多典型的性能問題是由不充足的或配置失當的硬體組件導致的。I/O子系統是一個資料庫調整的關鍵性部分。通過提供足夠的CPU、記憶體與I/O資源。可以避免許多性能問題。

通過監控相關的計數器，可以及時發現和解決引起系統性能降低的硬體問題。

2.1 控制記憶體的使用

SQL Server 要求記憶體是基於靜態記憶體的需要：一是它自己的程式程式碼和內部數據結構，例如內核的工作負載，打開對象，鎖。二是數據高速快取。

基於有效的系統資源和這些資源的競爭需要, SQL Server動態地獲得和釋放數據高速快取。如果SQL Server的數據高速快取需要更多的記憶體，它查詢作業系統檢查是否有物理記憶體可以利用。如果有，SQL Server在數據高速

存中使用它並且在記憶體中保留先前讀到的數據。

為阻止Windows 2000頁面調度,SQL Server依賴Server activity增減數據高速快取以保留4MB~10MB剩餘物理記憶體。對SQL Server不足的記憶體分配或使用會引起數據連續地從硬碟上而不是高速快取上讀取,這將降低系統的性能。

請觀察以下與記憶體有關的計數器,以便及時發現和解決記憶體上的問題。

使用工具:性能監視器

監控記憶體和分頁的使用

對象: 計數器

描述

指導

Memory: Available Bytes

監控被進程執行使用的有效位元組數。

（可用物理記憶體量）

這個計數器應該總是大於5000KB;低值顯示物理記憶體整體的缺乏和需要提高。

推薦值：大於4MB

Memory: Page/sec

為了訪問不在記憶體中的頁而讀取或寫入磁碟的總頁數。

該計數器應該從不持續大於零.如果值持續大於零，Windows 2000作業系統正在使用頁面調度來填充記憶體.

推薦值：小於5

Process: Page Faults/sec/SQL Server Instance

缺頁/秒

處理器中的Page Faults的計數值。當進程所引用的虛擬記憶體頁不在其主記憶體的工作集中時，將發生頁錯誤。如果某一頁已在主記憶體中（位與備用列表內），或者它正被共享此頁的其他進程使用，Page Fault 將不會導致系統從磁碟調入該頁。

這個計數器的高值表明過多的頁面調度和磁碟壓力,檢查是否是SQL Server 或其他的進程引起過多的頁面調度。

隔離SQL Server 使用的記憶體

Process: Working Set/SQL Server Instance

監控用於SQL Server的一個實例的SQL Server進程的記憶體的

數量。

這個計數器應該大於5000KB。當這個計數器低於5000KB，沒有更多的記憶體可供SQL Server 使用。

SQL Server: Buffer Manager: Buffer Cache Hit Ratio

高速快取命中率

監控高速快取中不需從硬碟中讀取的頁的百分率,。不用區分用於高速快取的是物理記憶體還是頁面調度記憶體。

這個計數器應該大於90%，因為它顯示的是發現在記憶體中的頁的數量。

SQL Server: Buffer Manger: Total Pages

監控高速快取中頁的總數量，包括資料庫，free和來自其他進程的stolen頁。

低值顯示連續的磁碟輸入輸出或壓力.考慮增加更多的記憶體.

SQL Server: Memory Manager Total Server Memory

監控伺服器正在使用的動態記憶體的總的數量。

如果該計數器與可用的物理記憶體比較持續高，則需加更多的記憶體。

2.2 監控執行緒和處理器的使用

優化處理器性能是輸出量和響應時間之間的一種平衡。

處理器的性能

當你檢查處理器的使用，考慮SQL Server實例正在做的工作的類型。如果SQL Server正在做大量的計算，例如包含集合的查詢或綁定記憶體這種不需要磁碟輸入輸出的查詢，100%的處理器時間可能被使用。

對於多處理器的系統,你需要監控每個處理器的這個計數器的分離的實例。確定所有處理器的平均值，可使

計數器：System:% Total Processor Time 。

執行緒

每個SQL Server的實例都是一個獨立的作業系統進程，SQL Server2000的實例使用Windows執行緒，有時是纖程

去有效的管理並發的任務。

1) 一個進程是一個應用的實例，例如SQL Server並且能有一個或多個任務。

2) 一個執行緒是進程任務的一種機制，並且被用來計劃處理器的時間。

當一個執行緒處於等待一個操作（例如讀寫磁碟）完成的空閑期時，Windows 2000作業系統通過轉換執行緒來最大化處理器的使用。執行緒間的轉換叫做context switching. 每個SQL Server的實例用戶連接的一個執行緒池,池中的執行緒被叫做工作執行緒。

當Processor: %Processor Time 持續接近100%並且System: Processor Queue Length 顯示更多的應用的進程正在等待處理器，或者當System: Context Switches/Sec 較高。顯示出現了系統瓶頸。當Processor:% Processor Time 接近100%並且System: Context Switches/Sec 接近8000，考慮更快的處理器，附加的處理器或者轉換到使用纖程。

請觀察以下與記憶體有關的計數器,以便及時發現和解決處理器上的問題。

使用工具：Windows 性能監視器

對象: 計數器

描述

指導

Processor: %Processor Time

以處理器運行非空閑執行緒所經歷時間的百分比表示。它被視為用於處理有效工作的時間比。每一個處理器在空閑時將會指定一個空閑執行緒來消耗未被其他執行緒使用的處理器時間段。

這個計數器應該低於90%，如果這個計數器較高，應降低工作負荷，提高工作效率或者或加大處理器的能力。

System: Context Switches/sec

監控處理器每秒在執行緒間轉換的次數。

在一個多處理器的電腦上,如果這個計數器達到8000,並且Processor:% Processor Time計數器超過90%,考慮使用SQL Server fiber scheduling.

System: Processor Queue Length

監控等待進程時間的執行緒的數目

這個計數器不應該持續大於2。如果這個計數器持續大於2，降低工作負荷,提高工作負荷的效率，或者增加處理器的能力，在多處理器的系統中可以增加處理器。

Processor: % Privileged Time

在「特權模式」下處理器運行非空閑執行緒所經歷時間的百分比。Windows NT服務層，執行體子程式及Windows NT內核都是在「特權方式」下運行。

如果處理器的大部分時間被用來做系統內核命令,並且物理硬碟的計數器較高，考慮提高硬碟輸入輸出子系統的性能。

Processor: %User Time

在「用戶模式」下處理器運行非空閑執行緒所經歷時間的百分比。所有應用程式碼及子系統碼都在「用戶模式「下運行。

這個能確定其它進程或應用正在執行或阻止SQL Server操作。

2.3 監控硬碟輸入輸出

SQL Server 使用Windows 2000 I/O calls 執行磁碟的讀寫。SQL Server管理何時和如何執行磁碟讀寫，但依賴Windows執行底層的輸入輸出操作。I/O子系統包括系統匯流排，磁碟控制卡，磁碟，磁帶驅動器，CD-ROM驅動器和許多其它的I/O設備。磁碟經常是系統的最大的瓶頸。

監控硬碟輸入輸出將幫助你確定讀頁和寫頁是否超出硬碟子系統的能力。一個忙碌的硬碟子系統也可以顯示不足的記憶體所引起的過多的頁面調度輸入輸出。

下面的表描述了優化對象計數器，你可以用來監控你的硬碟子系統的性能。

使用工具:Windows 性能監視器

對象：計數器

描述

指導

PhysicalDisk: %Disk Time

所選的驅動器忙於處理讀取或寫入請求作服務所花費時間的百分比。

這個計數器應當持續低於90%。

推薦值：小於50%

PhysicalDisk:Avg.Disk Queue Length

指在取樣間隔期內，對所選磁碟的讀寫操作被排入隊列的平均次數。

這個計數器應該不超過中心值的兩倍。

PhysicalDisk:Disk Read/sec

讀取磁碟的速度

這個計數器應該續低於硬碟子系統的能力。

PhysicalDisk: Disk Writes/sec

寫入磁碟的速度

這個計數器應持續低於硬碟子系統的能力。

如果這些硬碟計數器顯示你的硬碟正在超負荷運行，考慮：

1. 通過使用一個更快的硬碟，提高硬碟輸入輸出能力

2. 把一些文件轉移到一個附加硬碟或伺服器上

3. 增加一個硬碟陣列

4. 提高硬碟的數量有助於減少硬碟的壓力。

SQL SERVER中一些常見性能問題的總結

1.對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

2.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null

可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：

select id from t where num=0

3.應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。

4.應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

5.in 和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in(1,2,3)

對於連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查詢也將導致全表掃描：

select id from t where name like '%abc%'

若要提高效率，可以考慮全文檢索。

7.如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但優化程式不能將訪問計劃的選擇推遲到運

行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將

進行全表掃描：

select id from t where num=@num

可以改為強制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8.應盡量避免在 where 子句中對欄位進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100

應改為:

select id from t where num=100*2

9.應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)='abc'–name以abc開頭的id

select id from t where datediff(day,createdate,'2005-11-30')=0–『2005-11-30』生成的id

應改為:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where 子句中的「=」左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

11.在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。

12.不要寫一些沒有意義的查詢，如需要生成一個空表結構：

select col1,col2 into #t from t where 1=0

這類程式碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：

create table #t(…)

13.很多時候用 exists 代替 in 是一個好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

14.並不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重複時，SQL查詢可能不會去利用索引，如一表中有欄位sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

15.索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

16.應儘可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那麼需要考慮是否應將該索引建為 clustered 索引。

17.盡量使用數字型欄位，若只含數值資訊的欄位盡量不要設計為字元型，這會降低查詢和連接的性能，並會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字元串中每一個字元，而對於數字型而言只需要比較一次就夠了。

18.儘可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的欄位內搜索效率顯然要高些。

19.任何地方都不要使用 select * from t ，用具體的欄位列表代替「*」，不要返回用不到的任何欄位。

20.盡量使用表變數來代替臨時表。如果表變數包含大量數據，請注意索引非常有限（只有主鍵索引）。

21.避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

22.臨時表並不是不可使用，適當地使用它們可以使某些常式更有效，例如，當需要重複引用大型表或常用表中的某個數據集時。但是，對於一次性事件，最好使用導出表。

23.在新建臨時表時，如果一次性插入數據量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然後insert。

24.如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

25.盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那麼就應該考慮改寫。

26.使用基於游標的方法或臨時表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。

27.與臨時表一樣，游標並不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括「合計」的常式通常要比使用游標執行的速度快。如果開發時間允許，基於游標的方法和基於集的方法都可以嘗試一下，看哪一種方法的效果更好。

28.在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ，在結束時設置 SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句後向客戶端發送 DONE_IN_PROC 消息。

29.盡量避免大事務操作，提高系統並發能力。

30.盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。

具體的SQL語句在很多情況下需要結合實際的應用情況來寫，這裡不作敘述。

–Windows 2003支援4G記憶體

[boot loader]

timeout=30

default=multi(0)disk(0)rdisk(0)partition(2)/WINDOWS

[operating systems]

multi(0)disk(0)rdisk(0)partition(2)/WINDOWS="Windows Server 2003, Standard" /fastdetect /3GB

將Boot.ini文件加好參數:

/fastdetect /3GB

對資料庫中一些數據量較大的表（如T_Voucher,T_VoucherEntry,T_Balance,

IcStockBill,IcStockBillEntry等）可以在SQL SERVER中製作一個作業在系統空閑時定時進行重建索引，例如「dbcc dbreindex('icstockbill')；dbcc dbreindex('icstockbillEntry')」2個sql進行出入庫單據表的專門索引優化。