趙金鳳

文章編號(hào)：2095-6835（2017）04-0107-03

摘 要：SQL SERVER作為一種企業(yè)級(jí)的數(shù)據(jù)庫(kù)，其性能會(huì)影響軟件的增刪查改效率。而索引在數(shù)據(jù)庫(kù)表中非常關(guān)鍵，尤其是一張表只能有一個(gè)聚集索引，應(yīng)該合理利用。同時(shí)，要善于利用數(shù)據(jù)庫(kù)中的各種工具進(jìn)行有針對(duì)性的分析和性能調(diào)優(yōu)。實(shí)踐表明，SQL SERVER的性能優(yōu)化是可行的，也是多方面的。

關(guān)鍵詞：SQL SERVER；聚集索引；通信系統(tǒng)；服務(wù)器

中圖分類號(hào)：TP311.13 文獻(xiàn)標(biāo)識(shí)碼：A DOI：10.15913/j.cnki.kjycx.2017.04.107

1 軟件的背景

本文主要是基于某通信系統(tǒng)項(xiàng)目中系統(tǒng)狀態(tài)監(jiān)視軟件進(jìn)行分析的。該軟件為C/S架構(gòu)，由客戶端軟件、服務(wù)器端軟件和數(shù)據(jù)庫(kù)軟件組成。

軟件的工作流程是：各分系統(tǒng)軟件與服務(wù)器端軟件建立TCP連接，并登錄。在運(yùn)行過程中，需每秒上報(bào)一次分系統(tǒng)的狀態(tài)參數(shù)。每個(gè)分系統(tǒng)上報(bào)的狀態(tài)參數(shù)中包含各分系統(tǒng)中主要設(shè)備的工作參數(shù)和故障告警信息，服務(wù)器端軟件需接收該條狀態(tài)信息，發(fā)送至客戶端軟件顯示，并將與上一條數(shù)據(jù)相比發(fā)生變化的整條狀態(tài)數(shù)據(jù)寫入數(shù)據(jù)庫(kù)中。

在實(shí)驗(yàn)室測(cè)試中，使用3個(gè)分系統(tǒng)模擬軟件分別模擬3個(gè)分系統(tǒng)，向服務(wù)器發(fā)送分系統(tǒng)狀態(tài)。當(dāng)數(shù)據(jù)量累積到一年的數(shù)據(jù)量（約600萬行）時(shí)，在客戶端查詢一個(gè)月或更大范圍內(nèi)的狀態(tài)數(shù)據(jù)時(shí)，查詢數(shù)據(jù)庫(kù)的響應(yīng)時(shí)間明顯延長(zhǎng)，在分頁(yè)顯示中點(diǎn)擊下一頁(yè)，等待的時(shí)間過長(zhǎng)。針對(duì)這個(gè)情況，本文通過分析和測(cè)試該數(shù)據(jù)庫(kù)性能，提出了優(yōu)化方案，并為以后的數(shù)據(jù)庫(kù)設(shè)計(jì)提出了合理建議。

2 系統(tǒng)及軟件運(yùn)行環(huán)境

服務(wù)器軟件和數(shù)據(jù)庫(kù)軟件的運(yùn)行平臺(tái)分系統(tǒng)狀態(tài)監(jiān)視軟件客戶端安裝于1臺(tái)PC機(jī)，3個(gè)分系統(tǒng)軟件（分系統(tǒng)1、分系統(tǒng)2、分系統(tǒng)3）分別部署于3臺(tái)PC機(jī)。服務(wù)器軟件分別部署于2臺(tái)服務(wù)器，同時(shí)，有一臺(tái)主服務(wù)器工作，另一臺(tái)為備機(jī)。

2.1 性能原因分析

SQL SERVER調(diào)整優(yōu)化主要涉及軟件和硬件兩部分。在工作過程中，就是要調(diào)整計(jì)算機(jī)系統(tǒng)硬件各主要組成部分，因?yàn)樗鼈兪窃斐善款i的主要因素。而在SQL SERVER系統(tǒng)中，比較常見的問題出現(xiàn)在CPU、內(nèi)存和磁盤IO等部件中。但在數(shù)據(jù)庫(kù)設(shè)計(jì)中，為數(shù)據(jù)表建立索引是得到所需數(shù)據(jù)的有效方法。對(duì)于每一個(gè)查詢優(yōu)化器，要確定是否有相關(guān)的索引可以用于訪問數(shù)據(jù)。一個(gè)利用索引的訪問與全表掃描相比，可以大大縮短查詢時(shí)間。因此，本文主要從CPU、內(nèi)存、磁盤IO和為數(shù)據(jù)表建立不同索引方面進(jìn)行相關(guān)測(cè)試分析。

2.2 使用性能監(jiān)視器監(jiān)視系統(tǒng)性能

2.2.1 性能監(jiān)視器的使用

性能監(jiān)視器是Windows 2003的一個(gè)工具，可以同時(shí)監(jiān)視Windows 2003和SQL SERVER 2000的性能表現(xiàn)，是一個(gè)很好的性能分析工具。雖然性能監(jiān)視器提供了充足的計(jì)數(shù)器，但因?yàn)镾QL SERVER 2000的性能極大地依賴于Windows 2003的性能情況，所以，在大多數(shù)情況下，也只需要監(jiān)視關(guān)鍵的幾個(gè)計(jì)數(shù)器，比如System：processor queue length，Processor：%processor time，Processor：%privileged time PhysicalDisk：Avg.Disk Queue Length，PhysicalDisk：%Disk Time，Memory：Pages/Sec，SQL SERVER緩沖管理器，緩存命中率等。只有在特殊情況下，才需要選擇其他計(jì)數(shù)器來進(jìn)一步監(jiān)視。

2.2.2 監(jiān)視數(shù)據(jù)的分析

使用性能監(jiān)視器監(jiān)視主服務(wù)器24 h的性能數(shù)據(jù)，記錄了服務(wù)器在連續(xù)運(yùn)行8 h的過程中不同計(jì)數(shù)器顯示的數(shù)值，而且在軟件運(yùn)行過程中，測(cè)試人員不分時(shí)段的進(jìn)行查詢測(cè)試。由此可以推斷，該主用服務(wù)器硬件配置已經(jīng)滿足了該服務(wù)器軟件和數(shù)據(jù)庫(kù)軟件的運(yùn)行。

2.3 SQL SERVER數(shù)據(jù)庫(kù)配置

SQL SERVER 2000數(shù)據(jù)庫(kù)提供了可視化配置界面。在數(shù)據(jù)配置過程中，可配置數(shù)據(jù)庫(kù)可以使用的CPU數(shù)量、可使用的內(nèi)存大小、限制用戶連接數(shù)、用戶身份驗(yàn)證方式、用戶查詢超時(shí)時(shí)間的設(shè)置等。當(dāng)然，除了窗口界面的配置外，SQL SERVER也提供了數(shù)據(jù)庫(kù)控制臺(tái)命令（DBCC）來顯示、配置不同的數(shù)據(jù)庫(kù)參數(shù)，優(yōu)化數(shù)據(jù)庫(kù)性能。

設(shè)置SQL SERVER CPU使用不同數(shù)量的CPU和設(shè)置不同的內(nèi)存使用量對(duì)比查詢時(shí)間。在企業(yè)管理器中，右擊“（LOCAL）（WINDOWS NT）”屬性，點(diǎn)擊處理器頁(yè)，指定當(dāng)前SQL SERVER要使用的處理器，最大工作線程選擇32（最小值）。點(diǎn)擊內(nèi)存，設(shè)置允許SQL SERVER使用的內(nèi)存量。在工作過程中，可以使用相應(yīng)的查詢語句查詢數(shù)據(jù)量（5 437 149行），即SELECT * FROM T_DSBSTATUS WHERE dsb_timestamp BETWEEN‘2015-4-10AND‘2015-5-20ORDER BY dsb_no.

由于備服務(wù)器只有2 G內(nèi)存，當(dāng)加載進(jìn)2張數(shù)據(jù)表，查詢第3張數(shù)據(jù)表，且只查詢1條返回?cái)?shù)據(jù)時(shí)，時(shí)間超過了20 s。這個(gè)現(xiàn)象反映出備服務(wù)器內(nèi)存不足的問題，導(dǎo)致數(shù)據(jù)表從磁盤加載數(shù)據(jù)進(jìn)內(nèi)存時(shí)，磁盤IO嚴(yán)重影響查詢時(shí)間。而在主服務(wù)器進(jìn)行相同查詢時(shí)，內(nèi)存使用量已經(jīng)超過了2 G，且查詢分析器顯示執(zhí)行時(shí)間為0 ms。因此，應(yīng)判斷為該數(shù)據(jù)庫(kù)當(dāng)前的數(shù)據(jù)量需要至少大于2 G的內(nèi)存才能完成查詢工作。

2.4 數(shù)據(jù)表建立索引

2.4.1 聚集索引

聚集索引（聚簇索引）是一種指明表數(shù)據(jù)物理存儲(chǔ)順序的索引。表數(shù)據(jù)按照指定作為聚集索引的一個(gè)或多個(gè)鍵列排序并存儲(chǔ)。因?yàn)閿?shù)據(jù)按照一個(gè)指定的順序物理地存儲(chǔ)，所以，一個(gè)表只能創(chuàng)建一個(gè)聚集索引。

2.4.2 非聚集索引

非聚集索引（非聚簇索引）的葉子節(jié)點(diǎn)含有索引行，用于存儲(chǔ)確定行的位置信息。位置信息取決于表上是否存在聚集索引。如果該表有聚集索引，對(duì)于每行，該位置信息就是聚集索引的鍵值，可以作為該行的定位器。如果聚集索引不是一個(gè)唯一索引，SQL SERVER就自動(dòng)地為所有相同的索引鍵值分配一個(gè)內(nèi)部值以非聚集索引方式使用。如果表上沒有聚集索引，則位置信息是一個(gè)行ID，而不是聚集索引的一個(gè)鍵值。行ID是一個(gè)指針，由文件ID、頁(yè)號(hào)和行ID組成。這個(gè)指針能夠精確地指示在哪里可以找到行。SQL SERVER允許一個(gè)表上最多可以有249個(gè)非聚集索引。

2.5 索引查詢測(cè)試

查詢主要基于T_DSBSTATUS表。由于查詢一般基于時(shí)間范圍且按照序號(hào)排序，所以，索引分別在dsb_timestamp和dsb_no列建立進(jìn)行查詢。查詢之前，使用SET STATISTICSSQL TIME ON命令打開查詢時(shí)間顯示。每次查詢之前，要先停止SQL SERVER服務(wù)器，使用Windows查看任務(wù)管理器顯示當(dāng)前內(nèi)存使用量，發(fā)現(xiàn)內(nèi)存使用量明顯下降后，再重啟SQL SERVER服務(wù)器進(jìn)行查詢，這樣可避免緩存中已存入相同查詢數(shù)據(jù)，影響查詢結(jié)果。

索引測(cè)試共進(jìn)行了3次查詢，主要包括以下內(nèi)容。

2.5.1 相同數(shù)據(jù)不同索引的查詢

在T_DSBSTATUS表中，分別在dsb_no列和dsb_timestamp列單獨(dú)建立聚集索引和非聚集索引，使用相同的查詢語句查詢（數(shù)據(jù)量39 628行），且在查詢語句中分別使用dsb_no排序和不使用dsb_no排序比對(duì)查詢時(shí)間。

在dsb_timestamp列上建立索引時(shí)，可大幅提高查詢效率。建立聚集索引時(shí)，查詢效率更高，而在dsb_no列上建立聚集索引時(shí)，會(huì)影響查詢效率。由此可見，在查詢范圍的列上建立索引，可以有效提高查詢效率，聚集索引的查詢效率提高更明顯。

2.5.2 不同數(shù)據(jù)的不同索引查詢

在T_DSBSTATUS表中，在dsb_timestamp列分別建立聚集索引和非聚集索引。基于dsb_no列和dsb_timestamp列建立非聚集索引時(shí)，可使用查詢語句進(jìn)行查詢。

在查詢語句中，對(duì)dsb_no排序會(huì)延長(zhǎng)查詢時(shí)間。不論如何增加索引，使用排序的語句總是花費(fèi)時(shí)間最長(zhǎng)的查詢。

插入/刪除/更新的不同索引測(cè)試在dsb_timestamp列上分別建立聚集索引和非聚集索引進(jìn)行比較，使用表語句進(jìn)行操作：執(zhí)行插入操作，有索引時(shí)反而可能會(huì)降低速度。這是因?yàn)樵黾恿怂饕僮鳎潞蛣h除操作首先是要按條件檢索數(shù)據(jù)，然后進(jìn)行更新操作。對(duì)于一個(gè)海量數(shù)據(jù)表來說，檢索是主要的消耗，因此，這種情況下的更新和刪除操作在有索引時(shí)性能有很大的提升。

2.6 臨時(shí)數(shù)據(jù)庫(kù)的有效使用

臨時(shí)數(shù)據(jù)庫(kù)Tempdb主要用于排序、建立工作表、格式化等內(nèi)部處理和存儲(chǔ)用戶建立的臨時(shí)表、索引，它是可被多用戶共享的數(shù)據(jù)庫(kù)。

為了驗(yàn)證查詢語句是否會(huì)使用tempdb數(shù)據(jù)庫(kù)，做了以下測(cè)試，即，設(shè)置限制，tempdb數(shù)據(jù)文件限制為9 M，日志文件限制為2 M。然后查詢相應(yīng)的語句，即SELECT * FROM T_DSBSTATUS WHERE dsb_timestamp between‘2015-5-10 AND ‘2015-6-20ORDER BY dsb_no desc.查詢結(jié)果提示，“未能為數(shù)據(jù)庫(kù)‘TEMPDB中的對(duì)象‘（SYSTEM table id：-664682958）分配空間，因?yàn)槲募M‘DEFAULT已滿”。

由此可以看出，在查詢語句的查詢過程中使用了臨時(shí)數(shù)據(jù)庫(kù)，并且默認(rèn)空間已經(jīng)不能滿足需要。取消tempdb數(shù)據(jù)庫(kù)文件的增長(zhǎng)限制后，可正常查詢，并且tempdb數(shù)據(jù)庫(kù)文件會(huì)增大。

至于查詢語句何時(shí)會(huì)使用臨時(shí)數(shù)據(jù)庫(kù)，SQL SERVER指導(dǎo)手冊(cè)中給出了解釋：數(shù)據(jù)庫(kù)關(guān)系引擎有時(shí)也需要生成一個(gè)臨時(shí)工作表執(zhí)行SQL語句中指定的邏輯操作。一般為某些GROUP BY、ORDER BY或UNION查詢生成工作表。例如，如果ORDER BY子句引用的列不為任何索引所覆蓋，則關(guān)系引擎可能需要生成一個(gè)工作表，將結(jié)果集按所請(qǐng)求的順序排列。而臨時(shí)工作表在TEMPDB中生成，并在語句執(zhí)行完成后被自動(dòng)除去。

因此，臨時(shí)數(shù)據(jù)庫(kù)的設(shè)置也是會(huì)影響SQL SERVER性能的因素之一。為了驗(yàn)證臨時(shí)數(shù)據(jù)庫(kù)對(duì)查詢性能的影響，做了以下2個(gè)測(cè)試。

設(shè)置tempdb數(shù)據(jù)庫(kù)文件，按百分比自動(dòng)增長(zhǎng)。在企業(yè)管理器中查看tempdb的tempdev文件初始分配空間為8 M，設(shè)置文件按10%自動(dòng)增長(zhǎng)。查看templog文件的初始分配空間為2 M，設(shè)置文件按10%自動(dòng)增長(zhǎng)。清理SQL SERVER緩存，進(jìn)行如下查詢，即：

SET STATISTICS TIME ON

SELECT * FROM T_DSBSTATUS WHERE dsb_timestamp between ‘2015-3-10 8：00：00 AND ‘2015-6-10 17：00：00ORDER BY dsb_no asc

數(shù)據(jù)文件tempdev增長(zhǎng)至718 M，日志文件templog增長(zhǎng)至32 M。查詢到6 560 065行數(shù)據(jù)，耗時(shí)148 710 ms。

初始設(shè)置時(shí)，給tempdb數(shù)據(jù)庫(kù)文件比較大的空間。重啟SQL SERVER服務(wù)器，清理tempdb文件空間，設(shè)置tempdev文件為800 M，設(shè)置templog分配的空間為50 M，清理緩存，執(zhí)行之前提到的查詢步驟。此操作共查詢到6 560 065行數(shù)據(jù)，耗時(shí)119 187 ms，比第一個(gè)查詢快了近30 s。

由此可見，在默認(rèn)情況下，當(dāng)SQL SERVER運(yùn)行時(shí)，tempdb數(shù)據(jù)庫(kù)會(huì)根據(jù)需要自動(dòng)增長(zhǎng)。與其他數(shù)據(jù)庫(kù)不同的是，每次啟動(dòng)數(shù)據(jù)庫(kù)引擎時(shí)，它會(huì)重置為其初始大小。如果為tempdb數(shù)據(jù)庫(kù)定義的大小較小，則每次重新啟動(dòng)SQL SERVER 時(shí)，將tempdb數(shù)據(jù)庫(kù)的大小自動(dòng)增加到支持工作負(fù)荷所需的大小，那么，這一工作可能會(huì)成為系統(tǒng)處理負(fù)荷的一部分。為了避免這種開銷，可以在企業(yè)管理器中配置或者使用ALTER DATABASE命令增加 tempdb 數(shù)據(jù)庫(kù)的大小。

3 結(jié)論

由于客戶端主要基于時(shí)間段查詢，對(duì)于應(yīng)用者來說，更新和刪除的操作比較少，索引又會(huì)增加插入的時(shí)間，所以，建議數(shù)據(jù)庫(kù)在時(shí)間列上建立非聚集索引。這樣做，能適當(dāng)提高查詢效率，不會(huì)嚴(yán)重影響插入操作的效率。

實(shí)驗(yàn)室數(shù)據(jù)庫(kù)數(shù)據(jù)表累積了一年的數(shù)據(jù)量，在查詢時(shí)，會(huì)占用大量?jī)?nèi)存，所以，建議將服務(wù)器內(nèi)存配置為4 G左右，避免內(nèi)存不夠成為限制數(shù)據(jù)庫(kù)效率的瓶頸，并將常查詢的數(shù)據(jù)表設(shè)置為駐留內(nèi)存，避免從磁盤讀取數(shù)據(jù)影響查詢速度。

通過對(duì)臨時(shí)數(shù)據(jù)表的測(cè)試，建議在實(shí)際應(yīng)用過程中一次性為臨時(shí)數(shù)據(jù)庫(kù)配置800 M空間容量，避免在臨時(shí)數(shù)據(jù)庫(kù)自增長(zhǎng)過程中影響數(shù)據(jù)庫(kù)的查詢性能。

4 意見建議

4.1 建立合理的索引

從索引的結(jié)構(gòu)和原理中可以看出，雖然SQL SERVER可以通過索引訪問避免對(duì)表的掃描，減少大量的I/O操作，提高系統(tǒng)的性能，但同時(shí)也會(huì)增加系統(tǒng)的負(fù)擔(dān)，主要包括以下內(nèi)容：①創(chuàng)建和維護(hù)索引要耗費(fèi)系統(tǒng)時(shí)間；②存儲(chǔ)索引需要額外的物理空間；③增加、刪除和修改表中數(shù)據(jù)時(shí)，要?jiǎng)討B(tài)地維護(hù)索引，這就減慢了數(shù)據(jù)的維護(hù)速度。

4.2 維護(hù)數(shù)據(jù)庫(kù)注意事項(xiàng)

數(shù)據(jù)庫(kù)經(jīng)過長(zhǎng)期運(yùn)行，臨時(shí)數(shù)據(jù)庫(kù)文件增加至一定程度后，有可能會(huì)占滿磁盤空間。當(dāng)需再次查詢時(shí)，臨時(shí)數(shù)據(jù)庫(kù)沒有更多的空間增長(zhǎng)，數(shù)據(jù)庫(kù)便會(huì)報(bào)錯(cuò)。因此，為了避免出現(xiàn)這種問題，在不停止數(shù)據(jù)庫(kù)服務(wù)器運(yùn)行的情況下，數(shù)據(jù)庫(kù)維護(hù)人員可以使用DBCC SHRINKDATABASE語句收縮臨時(shí)數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)日志文件。

對(duì)于需要長(zhǎng)期操作的表，可使用控制臺(tái)命令DBCC PINTABLE將表駐留內(nèi)存。這樣可以避免從磁盤和高速緩存中不斷切換該表。但是，這個(gè)操作會(huì)占用內(nèi)存，且不會(huì)響應(yīng)其他程序從內(nèi)存中換出，所以，應(yīng)在確保內(nèi)存使用量充足的情況下進(jìn)行該操作。

5 結(jié)束語

本文列出了一些SQL SERVER數(shù)據(jù)庫(kù)優(yōu)化方法，實(shí)際上，因?yàn)橛绊懖樵冃阅艿囊蛩睾芏啵?，不可能找到一個(gè)通用的優(yōu)化方法。在開發(fā)和設(shè)計(jì)過程中，針對(duì)數(shù)據(jù)庫(kù)的運(yùn)行情況，有針對(duì)性地調(diào)整，以最小的開支和良好的執(zhí)行性能訪問數(shù)據(jù)庫(kù)為目標(biāo)，根據(jù)具體的開發(fā)環(huán)境和應(yīng)用需要，綜合考慮各方面因素，正確評(píng)價(jià)各種方法的優(yōu)點(diǎn)和缺點(diǎn)，從中選擇最佳方案。

參考文獻(xiàn)

[1]王利.SQL SERVER數(shù)據(jù)庫(kù)性能調(diào)整與優(yōu)化[D].成都：電子科技大學(xué)，2007.

[2]周序，管麗娜，白海波，等.SQL Server 2000中文版入門與提高[M].北京：清華大學(xué)出版社，2001.

[3]何益斌，高景昌，楊亞紅，等.Microsoft SQL Server的索引結(jié)構(gòu)及其優(yōu)化[J].吉林大學(xué)學(xué)報(bào)（信息科學(xué)版），2001，19（3）.

[4]Ryan K.S tephens，Ronald R.Pl ew.數(shù)據(jù)庫(kù)設(shè)計(jì)[M].何玉潔，譯.北京：機(jī)械工業(yè)出版社，2001.

[5]Robert Vi eira.SQL Server 2005高級(jí)程序設(shè)計(jì)[M].董明，譯.北京：人民郵電出版社，2008.

[6]Microsoft.SQL SERVER 2000企業(yè)版的安裝、配置和管理[M].北京：高等教育出版社，2003.

[7]許平格.數(shù)據(jù)庫(kù)管理系統(tǒng)中查詢優(yōu)化的設(shè)計(jì)和實(shí)現(xiàn)[D].杭州：浙江大學(xué)，2005.

[8]杜軍平，黃杰.SQL Server 2000數(shù)據(jù)庫(kù)開發(fā)[M].北京：機(jī)械工業(yè)出版社，2001.

[9]莊成三，洪玫，楊秋輝.數(shù)據(jù)庫(kù)系統(tǒng)原理及其應(yīng)用[M].北京：電子工業(yè)出版社，2000.

[10]Jeffrey D.Ullman，Jennifer Widom.數(shù)據(jù)庫(kù)系統(tǒng)基礎(chǔ)教程[M].北京：清華大學(xué)出版社，1999.

[11]楊正洪，鄭齊健，鄭齊心.SQL SERVER 7 關(guān)系數(shù)據(jù)庫(kù)系統(tǒng)管理與開發(fā)指南[M].北京：機(jī)械工業(yè)出版社，2000.

[12]宋靜靜，賈智平.一種嵌入式實(shí)時(shí)數(shù)據(jù)庫(kù)系統(tǒng)查詢優(yōu)化算法[J].計(jì)算機(jī)工程，2007，33（11）.

[13]趙松濤.SQL Server 2005系統(tǒng)管理實(shí)錄[M].北京：電子工業(yè)出版社，2006.

[14]沈兆陽(yáng).SQL Server 2000 OLAP解決方案[M].北京：清華大學(xué)出版社，2001.

[15]朱德利.SQL Server 2005數(shù)據(jù)挖掘與商業(yè)智能完全解決方案[M].北京：電子工業(yè)出版社，2007.

[16]W.H.Inmon.數(shù)據(jù)倉(cāng)庫(kù)[M].北京：機(jī)械工業(yè)出版社，2003.

[17]馬宏鵬，趙新，李明，等.數(shù)據(jù)倉(cāng)庫(kù)原形系統(tǒng)設(shè)計(jì)[J].計(jì)算機(jī)工程與應(yīng)用，2000，36（11）.

本文部分參考文獻(xiàn)因著錄項(xiàng)目不全被刪除。

〔編輯：白潔〕