SQL2008系統(tǒng)性能優(yōu)化解決方案

1、SQL Server 系統(tǒng)性能調(diào)優(yōu)解決方案前言近幾年 ,醫(yī)藥流通市場經(jīng)歷了激烈的震蕩 , 導(dǎo)致行業(yè)逐步成熟和企業(yè)的快速變革 ,差異化經(jīng)營成 為眾多醫(yī)藥流通的競爭選擇。時空產(chǎn)品在中國醫(yī)藥流通企業(yè)的發(fā)展過程中得到了廣泛且深入應(yīng)用 大量的客戶化開發(fā)和定制支撐了企業(yè)管理中橫向和縱向的變化，很好的適應(yīng)了企業(yè)在發(fā)展過程中不 斷變化的需求。對于數(shù)據(jù)庫管理系統(tǒng)的使用，很多用戶都面臨著一個很棘手的問題： 系統(tǒng)效率下降 。產(chǎn)生效率 下降的因素是多方面：1. 硬件問題2. 軟件問題3. 實施問題 正因為產(chǎn)生效率下降的因素很多，所以如何去查找原因成為我們首要關(guān)注的問題，時空公司也 處在積極探索過程中。時空公司在解決一

2、些客戶問題的過程中積累了一些方法和思路，歸納總結(jié)后 呈現(xiàn)給體系內(nèi)的技術(shù)人員，本方案就系統(tǒng)效率調(diào)整所必需的基礎(chǔ)知識、方法、技巧等幾個方面進(jìn)行 闡述，從而讓技術(shù)人員能夠快速定位問題，解決問題，為合作伙伴提供優(yōu)質(zhì)，快捷的服務(wù)。索引簡介索引是根據(jù)數(shù)據(jù)庫表中一個或多個列的值進(jìn)行排序的結(jié)構(gòu)。 索引提供指針以指向存儲在表中 指定列的數(shù)據(jù)值，然后根據(jù)指定的排序次序排列這些指針。 數(shù)據(jù)庫使用索引的方式與使用書的 目錄很相似，通過搜索索引找到特定的值，然后跟隨指針到達(dá)包含該值的行。索引鍵：用于創(chuàng)建索引的列。索引類型? 聚集索引：聚集索引基于數(shù)據(jù)行的鍵值在表內(nèi)排序和存儲這些數(shù)據(jù)行。由于數(shù)據(jù)行按基于聚集索引鍵 的排序

3、次序存儲，因此聚集索引對查找行很有效。每個表只能有一個聚集索引，因為數(shù)據(jù)行本 身只能按一個順序存儲。數(shù)據(jù)行本身構(gòu)成聚集索引的最低級別（葉子節(jié)點(diǎn)）。只有當(dāng)表包含聚集索引時，表內(nèi)的數(shù)據(jù)行才按排序次序存儲。如果表沒有聚集索引，則其數(shù)據(jù)行按堆集方式存儲。 聚集索引對于那些經(jīng)常要搜索范圍值的列特別有效。使用聚集索引找到包含第一個值的行后， 便可以確保包含后續(xù)索引值的行在物理相鄰。例如：如果應(yīng)用程序執(zhí)行的一個查詢經(jīng)常檢索某 一日期范圍內(nèi)的記錄，則使用聚集索引可以迅速找到包含開始日期的行，然后檢索表中所有相 鄰的行，直到到達(dá)結(jié)束日期。這樣有助于提高此類查詢的性能。同樣，如果對從表中檢索的數(shù) 據(jù)進(jìn)行排序時經(jīng)常

4、要用到某一列，則可以將該表在該列上聚集（物理排序），避免每次查詢該列時都進(jìn)行排序，從而節(jié)省成本。根節(jié)點(diǎn)EE根節(jié)點(diǎn)分支節(jié)點(diǎn)分支節(jié)點(diǎn)F葉節(jié)點(diǎn)（包括數(shù)據(jù)）數(shù)據(jù)數(shù)據(jù)數(shù)據(jù)數(shù)據(jù)F? 非聚集索引非聚集索引具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引的最低行包含非聚集索引的鍵值， 并且每個鍵值項都有指針指向包含該鍵值的數(shù)據(jù)行。數(shù)據(jù)行不按基于非聚集鍵的次序存儲。如 果一個表只有非聚集索引，它的數(shù)據(jù)行將按無序的堆集方式存儲，非聚集索引可以建多個。唯一索引唯一索引可以確保索引列不包含重復(fù)的值。在多列唯一索引的情況下，該索引可以確保索引列中每個值組合都是唯一的。唯一索引既是索引也是約束。復(fù)合索引索引項是多個的就叫組合索引，

5、也叫復(fù)合索引。復(fù)合索引使用時需要注意索引項的次序。索引對性能的作用? 使用索引的優(yōu)點(diǎn)1. 通過唯一性索引（unique ）可確保數(shù)據(jù)的唯一性2. 加快數(shù)據(jù)的檢索速度3. 加快表之間的連接4. 減少分組和排序的時間?使用索引的原則1. 在需要經(jīng)常搜索的列上創(chuàng)建索引2. 經(jīng)常用于連接的列上創(chuàng)建索引3. 經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引4. 經(jīng)常需要排序的列上創(chuàng)建索引5. 經(jīng)常用于where子句的列上創(chuàng)建索引?不使用索引的原則1. 查詢很少使用和參考的列不建索引2. 對只有少數(shù)值的列不建索引3. 定義為text、image、bit的列不建索引4. 當(dāng)需要update性能遠(yuǎn)遠(yuǎn)高于select性能

6、時不建或少建索引?常用命令2. dbcc showcontig : 顯示指定表的數(shù)據(jù)和索引的碎片信息3. dbcc dbreindex : 重建指定數(shù)據(jù)庫中一個或多個索引4. dbcc indexdefrag : 整理指定表或視圖的聚集索引或輔助索引的碎片? 創(chuàng)建索引1. 定義索引時，可以指定每列的數(shù)據(jù)是按升序還是降序存儲。如果不指定，則默認(rèn)為升 序2. 為索引指定填充因子，可標(biāo)識填充因子來指定每個索引頁的填滿程度。索引頁上的空余空 間量很重要，因為當(dāng)索引頁填滿時， 系統(tǒng)必須花時間拆分它以便為新行騰出空間。? 優(yōu)化索引1. 重建索引( dbcc dbreindex )2. 索引優(yōu)化向?qū)?. 整

7、理指定的表或視圖的聚集索引和輔助索引碎片 (dbcc indexefrag)問題定位時空在產(chǎn)品開發(fā)過程中遵循大開發(fā)理，共四個研發(fā)層次，第一層技術(shù)研發(fā)，由時空技術(shù)研 發(fā)部負(fù)責(zé)產(chǎn)品技術(shù)架構(gòu)，平臺工具的構(gòu)建，第二層產(chǎn)品研發(fā)，由時空產(chǎn)品研發(fā)部負(fù)責(zé)應(yīng)用系統(tǒng) 搭建。第三層項目研發(fā)，由渠道技術(shù)部負(fù)責(zé)客戶化定制，第四層客戶研發(fā)，由客戶信息中心根 據(jù)自己需求進(jìn)行產(chǎn)品的定制。隨著層次的增加，產(chǎn)品研發(fā)過程控制能力逐漸減弱，而且對系統(tǒng) 的關(guān)注角度也不同，隨著系統(tǒng)內(nèi)數(shù)據(jù)量的增加，效率問題將逐漸顯現(xiàn)出來，如何查找影響系統(tǒng) 效率的原因成為能否解決問題的關(guān)鍵。在查找問題的過程中，把可能需要改進(jìn)的程序或數(shù)據(jù)庫 對象及改進(jìn)方法詳

8、細(xì)列舉出來記錄在調(diào)整方案(見附錄 )中。檢查數(shù)據(jù)表結(jié)構(gòu)1. 查看在客戶化開發(fā)過程中增加的新表，字段類型是否合適，特別要關(guān)注字段長度較長字符 型字段，可以考慮更改為 VARCHA類型。檢查數(shù)據(jù)表中主鍵設(shè)置情況。明確數(shù)據(jù)表在系統(tǒng) 中存在的意義以及使用情況。2. 檢查系統(tǒng)當(dāng)中頻繁使用的數(shù)據(jù)表： maxbh,spkfk,spkfjc,hwsp,jxdjhz,jxdjmx,mchk,cwk,ywmxk,mxysyf,ywjsmxk,jsmxk, splsk ，查看主鍵，索引的設(shè)置是否合理，根據(jù)客戶的實際使用情況對索引進(jìn)行調(diào)整， 對于在表中新增加的字段，一般來講應(yīng)針對該字段建單鍵索引或復(fù)合索引。把檢查情

9、況記錄在調(diào)整方案中。檢查存儲過程時空產(chǎn)品在發(fā)布時是一個通用版本，為了兼容廣大客戶的需求，在業(yè)務(wù)處理邏輯上需 要考慮方面比較多，而客戶的業(yè)務(wù)流程和需求和產(chǎn)品本身差別可能很大，導(dǎo)致一些存儲過 程改動比較大。例如：SBP_KP_JS開票結(jié)算）SBP_JX_DJ進(jìn)銷單據(jù)存儲）SPU_Z_sp_account （商品帳頁登記）SBP_WD_DJ外調(diào)單據(jù)存儲） 首先，查看過程中業(yè)務(wù)處理邏輯，把不必要的語句屏蔽或刪除，以減輕系統(tǒng)壓力。其次，查看過程中 SQL語句編寫情況，在滿足需求的前提下，作進(jìn)一步優(yōu)化處理。第三，關(guān)注對大表（數(shù)據(jù)量較大）進(jìn)行操作的SQL語句，拷貝到查詢分析器中,看執(zhí)行計劃，根據(jù)計劃情況，調(diào)

10、整SQL語句或者相關(guān)表的索引。三、檢查檢索方案SQL第一檢查方案的數(shù)據(jù)過濾條件 , 盡量避免使用模糊匹配，在模糊查找時進(jìn)行全表掃描，語句執(zhí)行效率低下。第二仔細(xì)評定方案中需查詢的字段必要性，減少網(wǎng)絡(luò)流量。 第三盡量減少方案中的連接子句所涉及的數(shù)據(jù)表。 第四如果執(zhí)行結(jié)果對數(shù)據(jù)實時性要求不高，或者沒有數(shù)量，金額，成本等字段，應(yīng)該使用鎖定提示（ NOLOCK ） . 第五根據(jù)客戶使用習(xí)慣，拆分方案，分批獲取所需要的數(shù)據(jù)。如：銷售開票時可以先提取商品，然后再根據(jù)商品內(nèi)碼提取貨位，批號，數(shù)量等信息。第六分析查詢方案的執(zhí)行計劃，調(diào)整 SQL語句或者相關(guān)表索引。四、檢查查詢方案第一控制查詢方案的字段個數(shù)。第二

11、明確查詢的過濾條件。第三提取數(shù)據(jù)時考慮是否有可替代的表（數(shù)據(jù)量?。?，盡量避開操作比較頻繁的數(shù)據(jù)表。第四對于查詢數(shù)據(jù)實時性要求不高，應(yīng)該使用鎖定提示（ NOLOCK ）。五、優(yōu)化數(shù)據(jù)庫布局?jǐn)?shù)據(jù)文件和日志文件的位置和分布對系統(tǒng)的性能來說非常重要。數(shù)據(jù)庫布局的兩個關(guān)鍵性指導(dǎo)原則： 第一將連續(xù)訪問的文件分布在專用磁盤上一般情況下日志文件需要單獨(dú)分配一個磁盤第二當(dāng)布置數(shù)據(jù)文件時，應(yīng)該將數(shù)據(jù)文件分布盡可能多的磁盤驅(qū)動器上，從而允許更多的并行磁盤訪問。我們可以多創(chuàng)建一些附屬數(shù)據(jù)文件，把數(shù)據(jù)量較大的業(yè)務(wù)表單獨(dú)放在一個磁 盤上，為了明確地將數(shù)據(jù)庫表和索引放在特定的磁盤驅(qū)動上，必須創(chuàng)建用戶定義文件組，文 件組提

12、供了邏輯地將文件組合地起來的方法，以及將單個文件與主文件組分離的方法，如果 不創(chuàng)建其他文件組，在默認(rèn)情況下，所有文件都進(jìn)入主文件組。當(dāng)在含有多個數(shù)據(jù)文件的文 件組中創(chuàng)建表或索引時，SQL Server使用按比例填充機(jī)在文件之間分布數(shù)據(jù)。使用這種機(jī)制SQL Server按數(shù)據(jù)文件的大小成比例地填充每個數(shù)據(jù)文件。六、整體業(yè)務(wù)控制提高系統(tǒng)運(yùn)行效率，是綜合多方面，多環(huán)節(jié)調(diào)整結(jié)果的最終體現(xiàn)，我們要求的是整體最優(yōu)， 而不是局部最優(yōu)。要從全局的角度去衡量系統(tǒng)，而不是把目光只盯在某一個環(huán)節(jié)上，只有這樣 才能查找到系統(tǒng)當(dāng)中一些隱含的問題，否則在實際運(yùn)行時可能不會達(dá)預(yù)期效果，關(guān)注細(xì)節(jié)只是 一個最基本工作要求。如何

13、提高從宏觀角度去衡量系統(tǒng)所需要的素質(zhì)，首先，必需了解客戶管理理念，管理方式， 熟悉客戶的業(yè)務(wù)流程，從而確定系統(tǒng)應(yīng)該為客戶提供一個什么樣的服務(wù)。其次，了解使用人員 的業(yè)務(wù)需求及其在使用過程中所關(guān)注的信息點(diǎn)。第三，技術(shù)人員要非常熟悉時空的產(chǎn)品，掌握 每一個功能模塊的存在的價值和意義，以及業(yè)務(wù)處理的方法和邏輯。具備了上述幾種技能，才 能在思考的過程把整個系統(tǒng)包融在自己思維中，才能跳出系統(tǒng)本身去透視產(chǎn)品運(yùn)作流程，感受 產(chǎn)品的使用方法，應(yīng)用價值。銷售開票，是系統(tǒng)的一個基本的應(yīng)用，選擇商品，填寫批號，數(shù)量等信息，但是使用人員 發(fā)現(xiàn)檢索數(shù)據(jù)的速度比較慢，影響業(yè)務(wù)的快速進(jìn)行，這時就要考慮在操作過程中使用的方案

14、是 否有效，信息是否有意義，方案中使用的表在哪些環(huán)節(jié)經(jīng)常被使用，在使用的過程中是否被鎖 定，我們可以按照這種方法進(jìn)行橫向或縱向的比較分析，逐步去找出問題的根源 。七、SQL語句跟蹤系統(tǒng)效率下降，在許多情況下，產(chǎn)生問題的根本原因是效率低下的SQL語句，SQL事件探查器(SQL Profiler )將幫助技術(shù)人員確定是哪一個語句出現(xiàn)問題，當(dāng)查找需要調(diào)整的SQL語句時，從使用資源最多或者運(yùn)行時間最長或者最經(jīng)常執(zhí)行的SQL語句入手，調(diào)整一條或幾條使用大量系統(tǒng)資源的 SQL語句將對系統(tǒng)性能有顯著影響。通過跟蹤SQL SERVER勺活動，可以區(qū)分哪個應(yīng)用程序，存儲過程和SQL語句占用了最長時間，或者哪些語

15、句使用頻率較高。SQLProfiler 所提供的預(yù)定義的跟蹤模板，在許多情況下組織和功能都非常優(yōu)秀，可以根據(jù)特性需 求修改這些跟蹤模板，并將這些修改后的跟蹤模板保存為新模板，這樣可以減少大量工作。這 些預(yù)定義跟蹤模板如下所示：1. Sta ndard(SQLServerProfilerSta ndard.tdf)提供所執(zhí)行的 SQL 語句和所完成的SQL批處理的詳細(xì)息2. Stored Procedure Counts(SQLServerProfilerSP_Counts.tdf)記錄已經(jīng)執(zhí)行的存儲過程以及這些存儲過程運(yùn)行頻率的數(shù)據(jù)，了解不同的存儲過程運(yùn)行的次數(shù)將有助于確定哪個存儲過程是最好的

16、調(diào)整對象。一個執(zhí)行頻率較高，但效率低下的存儲過程是一個需要調(diào)整的好對象，在這個跟蹤中，增加SP:Completed事件和Duration數(shù)據(jù)是非常有用的。3. TSQL(SQLServerProfilerTSQL.tdf) 按照SQL語句的提交順序搜集 SQL語句，可以使用 這些信息來查看系統(tǒng)的活動?？梢詫⑦@些活動與系統(tǒng)的其它的事件相關(guān)聯(lián)例如，死鎖或其它系統(tǒng)問題4. TSQL By Duratio n(SQLServerProfilerTSQL_Durati on .tdf)顯示已經(jīng)執(zhí)行的 SQL 語句以及執(zhí)行這些SQL語句所需要的時間。5. TSQL Grouped(SQLServerPro

17、filerTSQL_Grouped.tdf)提供已經(jīng)執(zhí)行的 SQL 語句的詳細(xì)信息并且是根據(jù)應(yīng)用程序名稱，WINDOWS N用戶名稱以及進(jìn)程ID進(jìn)行分組。這個信息對于查找特定用戶報告的問題非常有用，例如少數(shù)用戶正在經(jīng)歷死鎖。通過檢查SQL批處理開始的時間戳，可以清楚地知道應(yīng)用程序中每一步執(zhí)行所花費(fèi)的時間。6. TSQLStored Procedures(SQLServerProfilerTSQL_SPs.tdf)顯示存儲過程和存儲過程內(nèi)部的SQL命令。結(jié)果按照時間順序進(jìn)行排序，對于那些調(diào)用存儲過程的過程意義較大。應(yīng)用示例：1. 查找運(yùn)行時間較長的 SQL語句查找長時間運(yùn)行的查詢的最好方法是使用

18、下面的事件，并按Duration (時間的)持續(xù)) 數(shù)據(jù)列分組.TSQL,SQL: BatchCompleteSQL批處理完成執(zhí)行所花費(fèi)的時間.根據(jù)Duration數(shù)據(jù)列的順序進(jìn)行分組，將使跟蹤的結(jié)果按照每一個語句執(zhí)行所需的時 間進(jìn)行排序，在跟蹤數(shù)據(jù)窗口的底部列出了運(yùn)行時間最長的SQL語句，這可能是調(diào)整系統(tǒng)性能的最好地方。2. 查找資源消耗型作業(yè)這種跟蹤類型查看消耗了CPU和I/O資源的SQL語句。最佳方法是選擇以下事件進(jìn)行監(jiān)視，并按照 CPU,Reads或者 Writes列進(jìn)行分組，這取決于你更關(guān)心I/O資源還是CPU資源的使用。.TSQL,SQL: BatchCompleteSQL批處理完

19、成執(zhí)行所需的時間。CPU,Reads, Writes數(shù)據(jù)列將顯示由該事件使用的資源。3. 檢測死鎖產(chǎn)生死鎖現(xiàn)象，對于系統(tǒng)來講是一個非常嚴(yán)重問題，尤其對在線事務(wù)處理(OLTP影響非常大，那么查找死鎖產(chǎn)生的原也就等同于改善系統(tǒng)性能。在跟蹤定義選擇下列事 件。.TSQL, SQL :BatchStarting 正運(yùn)行的 SQL批處理。.Locks,Lock :Deadlock死鎖本身事件。.Locks,Lock :Deadlockchain導(dǎo)致死鎖的一系列事件。八、查看執(zhí)行計劃SQL查詢分析器，是一個非常重要的工具，在系統(tǒng)效率調(diào)整過程具有不可替代的作 用，它允許用戶對 SQLSERVER據(jù)庫運(yùn)行特定

20、查詢，還可以提供一個查詢所消耗的系統(tǒng) 資源的信息，這些信息在分析和調(diào)整系統(tǒng)性能方面很有幫助，技術(shù)人員能夠交互式地設(shè) 計和測試SQL語句。在實際操作中，應(yīng)遵循這樣一個原則：盡量避免全表掃描，全表掃描非常消耗系統(tǒng) 資源，通過建主鍵或者調(diào)整索引的方法，使SQL語句執(zhí)行時掃描索引。九、調(diào)整業(yè)務(wù)邏輯在時空業(yè)務(wù)系統(tǒng)中存在一些大數(shù)據(jù)量的業(yè)務(wù)表，而且使用非常頻繁。對于大表數(shù)據(jù) 的檢索更新耗時較長，系統(tǒng)反應(yīng)遲鈍。尤其在客戶業(yè)務(wù)量比較大的時候，表現(xiàn)更加突出， 影響銷售進(jìn)程，并且時常會產(chǎn)生死鎖現(xiàn)象。在這種情況下，調(diào)整SQL語句，調(diào)整索引也達(dá)不到預(yù)期效果。這時我們應(yīng)該考慮借助第三方數(shù)據(jù)表來達(dá)到我們管理控制的要求。比如

21、：銷售開票時，為了避免負(fù)庫存銷售，經(jīng)常要校驗商品已開票未出庫數(shù)量，一 般的方法就是在檢索商品的時候與 jxdjmx 表關(guān)聯(lián)。 這樣的處理邏輯達(dá)到了攔截負(fù)庫存的 目的，便不是最優(yōu)的。隨著表中數(shù)據(jù)量的逐漸增長，開票的效率也逐漸下降，直至系統(tǒng) 不可用。是不是還有更好的方法呢？答案是肯定。我們的目標(biāo)是攔截負(fù)庫銷售，和這件 事有關(guān)的數(shù)據(jù)是未執(zhí)行的開票單據(jù)，并且未執(zhí)行單據(jù)數(shù)據(jù)量較小，如果把這部分?jǐn)?shù)據(jù)同 歷史數(shù)據(jù)區(qū)分開，系統(tǒng)效率將會有很大的提升，所以我們可再創(chuàng)建一個結(jié)構(gòu)同原表一樣 的臨時數(shù)據(jù)表， 在開票存盤時另存一個副本到臨時表當(dāng)中。當(dāng)需要數(shù)據(jù)校驗時我們就可 以避開原來的大表，直接從臨時表提取數(shù)據(jù)。 接下來

22、的問題是在什么時機(jī)刪除臨時表中 的數(shù)據(jù)，對于這個問題我們首先要確認(rèn)，在什么時候票據(jù)完成了它所承載的業(yè)務(wù)活動， 應(yīng)該是已執(zhí)行的票據(jù)，或者是狀態(tài)為清的票據(jù)， 那么我們可以在 jxdjmx 表中增加觸發(fā) 器當(dāng)回寫 is_zx 字段時，通過單據(jù)編號關(guān)聯(lián)刪除臨時表中數(shù)據(jù)。通過上面的例子，我們可以發(fā)現(xiàn)只是處理方法的簡單轉(zhuǎn)換，就可以達(dá)到既滿足管理 要求，系統(tǒng)又很有效率的目的，所以我們在處理客戶需求過程中需要經(jīng)常換個角度去考 慮問題，去尋找更有效的法。十、 數(shù)據(jù)封存時空公司針對業(yè)務(wù)表數(shù)據(jù)量越來越大問題，在產(chǎn)品中提供了數(shù)據(jù)封存功能。把數(shù)據(jù)從原表轉(zhuǎn)移到封存表（原表名 + _fc '）中。也可以顯著提高系統(tǒng)

23、效率。在封存過程中要注意控制數(shù)據(jù)量（ 一般不超過 30 萬條數(shù)據(jù)） 。十一、 流程重組業(yè)務(wù)流程是企業(yè)為了實現(xiàn)某一特定目標(biāo)而采取的一系列行動。一個流程包括許多項活動。流程重組就是對企業(yè)的業(yè)務(wù)流程進(jìn)行根本性的再思考和改變，從調(diào)整效率角度來看，重組主要目的把系統(tǒng)壓力進(jìn)行分?jǐn)?，從而獲得在服務(wù)和速度等方面業(yè)績的改善，使 企業(yè)能最大限度地適應(yīng)顧客、競爭、變化為特征的企業(yè)經(jīng)營環(huán)境。例如，時空產(chǎn)品中提供銷售出庫且結(jié)算功能，由于銷售出庫需要記錄商品帳頁，往 來帳頁，回寫開票單據(jù)，產(chǎn)生結(jié)算信息，所以對系統(tǒng)壓力較大，同時鎖定很多相關(guān)的業(yè) 務(wù)表，對系統(tǒng)其他業(yè)務(wù)環(huán)節(jié)影響也很大。如果把功能拆解成：開票結(jié)算一一出庫，這樣

24、可以減輕出庫環(huán)節(jié)的系統(tǒng)壓力，保證整個業(yè)務(wù)流程高效，快速運(yùn)行。在流程重組過程中要充分考慮企業(yè)管理和控制要求，同時兼顧系統(tǒng)本身運(yùn)行特點(diǎn)， 從而達(dá)到雙贏的結(jié)果。十二、 移動 tempdbTempdb在SQL SERVERS是一個臨時數(shù)據(jù)庫，它對性能的影響較大。tempdb和其他數(shù)據(jù)庫一樣可以增大，可以縮小。當(dāng)數(shù)據(jù)文件需要增長的時候，通常不能保持剩余部分的連續(xù)性。這時文件就會產(chǎn)生碎片，這種碎片會造成性能下降。這種碎片屬于外來性碎 片。要阻 止在 tempdb 中產(chǎn)生外來性碎片，必須保證有足夠的硬盤空間。一般將 tempdb 的容量放到平均使用容量。而你也應(yīng)該允許 tempdb 自動增長，比如你有個一個

25、超大的 join 操作，它建立了一個超過 tempdb 容量的時候，該查詢將失敗。你還要設(shè)置一個合 理的單位增長量。因為如果你設(shè)得太小，將會產(chǎn)生許多外來性碎片，反而會占用更多資 源。sqlserver 調(diào)優(yōu)最有效的做法之一，就是把爭奪資源的操作獨(dú)立出去。tempdb 就是一個需要獨(dú)立出去的部分，而tempdb和其他系統(tǒng)庫一樣是公用的，是存取最可能頻繁的庫，所有處理臨時表、子查詢、GROUP BY排序、DISTINCT、連接等等。它最適合放到一個具有快速讀寫能力的設(shè)備上。移動 tempdb 的方法：1. sp_helpdb 查看 tempdb 現(xiàn)在存放的位置2. alter database t

26、mpdb modify file (name='tempdev',filename='newpathnewfilename',size=500mb)3. alter database tmpdb modify file (name='templog',filename='newpathnewfilename',size=500mb)4. 關(guān)閉SQL SERVE重啟5. 刪掉舊的 tempdb 文件十三、 服務(wù)器性能監(jiān)控? 調(diào)整 CPU要監(jiān)視CPU的使用情況，必須保障對系統(tǒng)處理非常繁忙的某一天全天進(jìn)行監(jiān)視，這樣就可以對系統(tǒng)進(jìn)行配置，

27、從而能處理最繁忙的任務(wù)，可以使用System Monitor 并選擇如下的對象和計數(shù)器進(jìn)行監(jiān)視：Processor Object( 處理器對象 )， % Processor Time( 處理器時間計數(shù) ), 選擇所有實例 可以查看每個處理器的使用情況，以及所有處理器的平均使用率. 如果處理器使用率保持在80%或更高，或者經(jīng)常出現(xiàn)峰值使用率，系統(tǒng)就可能具有CPU瓶頸，可以在系統(tǒng)中添加更多或更快的處理器，這樣就可以提高系統(tǒng)性能? 調(diào)整內(nèi)存如果條件允許，最好是 SQL Server 獨(dú)立占用數(shù)據(jù)庫服務(wù)器，這就允許 SQLServer 能夠 盡可能地使用系統(tǒng)內(nèi)存，而不用與其他應(yīng)用程序一起共享系統(tǒng)內(nèi)存。

28、通過 System Monitor 可以監(jiān)視如下對象Memory Object,Avaliable Mbytes表示系統(tǒng)中可供進(jìn)程使用的內(nèi)存所分配SQL Server:Memory Manager Object,Total Server Memory(KB) SQL Server 總內(nèi)存大小如果 Avaliable Mbytes 計數(shù)器的值非常小，意味著系統(tǒng)中已經(jīng)沒有足夠的物理內(nèi)存可 供使用，必須查看其它計數(shù)器確定是否增加物理內(nèi)存。如果緩沖存儲器命中率低于90%，那么系統(tǒng)通常需要更多的物理內(nèi)存。對于數(shù)據(jù)庫內(nèi)存配置，通常要求設(shè)置為固定內(nèi)存大小，這樣可以強(qiáng)制給SQL Server 分配內(nèi)存，提高內(nèi)

29、存的使用率。? 磁盤調(diào)整一監(jiān)測磁盤 I/O使用 System Monitor 并選擇 PhysicalDisk 對象，并選用下列計數(shù)器Disk Reads/sec 所選擇磁盤每秒所執(zhí)行的讀操作數(shù)Disk Writes/sec 所選擇磁盤每秒所執(zhí)行的寫操作 二分析磁盤指標(biāo)（可參考制造商規(guī)格說書） : 平均尋道時間 （毫秒 ）= 平均尋道（讀） +平均尋道（寫） /2 磁盤旋轉(zhuǎn)等待時間（毫秒） =500/ 轉(zhuǎn)速（轉(zhuǎn) / 分） /60 磁盤最佳 I/O=1000*0.8/ 平均尋道時間 +磁盤旋轉(zhuǎn)等待時間 RAID 0 :I/0操作數(shù) =（ 讀 +寫）每個磁盤的I/0 操作數(shù) = I/0操作數(shù) /

30、磁盤數(shù)量RAID 1 :I/0操作數(shù)= 讀+（2*寫）每個磁盤的I/0 操作數(shù) = I/0操作數(shù) /2RAID 5 :I/0操作數(shù)= 讀+（4*寫）每個磁盤的I/0 操作數(shù) = I/0操作數(shù) /磁盤數(shù)量RAID 10 :I/0操作數(shù)= 讀+（2*寫）每個磁盤的I/0 操作數(shù) = I/0操作數(shù) /磁盤數(shù)量如果每個磁盤的 I/0 操作數(shù)大于磁盤最佳 I/O 數(shù)那么磁盤系統(tǒng)存在瓶頸，需要添加磁 盤：磁盤個數(shù) = I/0 操作數(shù) / 磁盤最佳 I/O數(shù)據(jù)庫調(diào)整一 備份數(shù)據(jù)庫二 用備份文件重新恢復(fù)一個測試庫三 根據(jù)調(diào)整方案記錄的問題及改進(jìn)方法，在測試庫中修改，并把實際修改結(jié)果記 錄調(diào)整方案中 .四 系統(tǒng)

31、測試五 測試成功后，調(diào)整正式數(shù)據(jù)庫。案例A 公司業(yè)務(wù)系統(tǒng)上線運(yùn)行一年后系統(tǒng)速度變慢，而且經(jīng)常出現(xiàn)死鎖現(xiàn)象。使用 SQL 事件探查器 跟蹤發(fā)現(xiàn)maxbh表被鎖死，查看表索引情況，maxbh表中沒有主鍵。調(diào)整后該表不再出現(xiàn)死鎖。通過實地觀察系統(tǒng)使用情況：銷售開票檢索商品，開票結(jié)算存盤速度較慢。查看商品檢索方案， 方案中為了 獲取商品的批號，數(shù)量等信息使用了視圖：select spid,pihao,sum(shl) shl fromsphwph group by spid,pihao 。同時為了攔截負(fù)庫存銷售，關(guān)聯(lián) jxdjhz,jxdjmx 表，沖減已開票未 執(zhí)行的商品數(shù)量，而且商品的過濾條件為模

32、糊查找。經(jīng)過分析，視圖在每次檢索商品時都要對所有 的品種分組求和，對系統(tǒng)壓力較大，如果這時有出庫業(yè)務(wù)發(fā)生，記帳回寫sphwph 表，開票將會產(chǎn)生資源等待。針對這種情況，作出如下調(diào)整：1. 取消視圖，直接在方案中關(guān)聯(lián) sphwph 表，通過商品過濾，減少分組求合的運(yùn)算量，減少 與出庫記帳沖突的概率。2. 新建一業(yè)務(wù)臨時表，在銷售開票時，另存一份明細(xì)到該表，從臨時表中檢索已開票未執(zhí) 行數(shù)據(jù)。在 jxdjmx 表中增加觸發(fā)器，當(dāng)回寫 is_zx 字段時，刪除臨時表中數(shù)據(jù)3. 同客戶技術(shù)人員協(xié)商，商品檢索采用左匹配方式 跟蹤查看開票結(jié)算存儲過程，發(fā)現(xiàn)回寫 mxysyf 語句執(zhí)行時間較長，分析客戶實際業(yè)

33、務(wù)，這個時機(jī)還 沒有產(chǎn)生應(yīng)收應(yīng)付信息，所以把回寫語句刪除。通過針對上述幾個問題的處理，系統(tǒng)效率得到了很大提升。過程編寫技術(shù)1. 保證在實現(xiàn)功能的基礎(chǔ)上，盡量減少對數(shù)據(jù)庫的訪問次數(shù)；通過搜索參數(shù)，盡量減少對表的訪問行數(shù) , 最小化結(jié)果集，從而減輕網(wǎng)絡(luò)負(fù)擔(dān)；能夠分開的操作盡量分開處理，提高每次的響應(yīng)速 度；、使用SQL時，盡量把使用的索引放在選擇的首列；算法的結(jié)構(gòu)盡量簡單；在查詢時，不要 過多地使用通配符，而且要用到幾列就選擇幾列，如：SELECT C1,C2 FROM T1；在可能的情況下盡量限制盡量結(jié)果集行數(shù)，如：SELECT TOP 300 C1,C2 FROM T1,因為某些情況下用戶是不

34、需要那么多的數(shù)據(jù)的，避免用!=或<> IS NULL或IS NOT NULL、IN，NOT IN等這樣的操作符，因為這會使系統(tǒng)無法使 用索引 , 而只能直接搜索表中的數(shù)據(jù)。例如 :SELECT C1 FROM T1 WHERE C1 != 'B%'2. 合理使用EXISTS,NOT EXISTS子句。如下所示：1) SELECT SUM(T1.C1)FROM T1 WHERE(SELECT COUNT(1)FROM T2 WHERE T2.C2=T1.C2)>0)2) SELECT SUM(T1.C1) FROM T1 WHERE EXISTS( SELECT

35、 1 FROM T2 WHERE T2.C2=T1.C2)兩者產(chǎn)生相同的結(jié)果，但是后者的效率顯然要高于前者。因為后者不會產(chǎn)生大量鎖定的表 掃描或是索引掃描。 如果你想校驗表里是否存在某條紀(jì)錄， 不要用 count(*) 那樣效率很低， 而且浪費(fèi)服務(wù)器資源?？梢杂肊XISTS代替。如：IF (SELECT COUNT(1) FROM table_name WHERE column_name = 'xxx')>0可以寫成：IF EXISTS (SELECT 1 FROM table_name WHERE column_name = 'xxx')3. 經(jīng)常需要寫

36、一個 T_SQL語句比較一個父結(jié)果集和子結(jié)果集，從而找到是否存在在父結(jié)果集中有而在子結(jié)果集中沒有的記錄，如：1) SELECT a.C1FROM T1 aWHERE NOT EXISTS (SELECT 1 FROM T2 b WHERE a.C1 = b.C1)2) SELECT a.C1 FROM T1 aLEFT JOIN T2 b ON a.C1 = b.C1WHERE b.C1 IS NULL3) SELECT a.C1 FROM T1 aWHERE a.C1 NOT IN (SELECT C1 FROM T2) 三種寫法都可以得到同樣正確的結(jié)果，但是效率依次降低。4. 能夠用BET

37、WEE的就不要用INSELECT * FROM T1 WHERE ID IN (10,11,12,13,14) 改成：SELECT * FROM T1 WHERE ID BETWEEN 10 AND 14因為 IN 會使系統(tǒng)無法使用索引 , 而只能直接搜索表中的數(shù)據(jù)。5. 能夠用DISTINCT的就不用GROUP BYSELECT C1 FROM T1 WHERE C2 > 10 GROUP BY C1 可改為：SELECT DISTINCT C1 FROM T1 WHERE C2 > 106. 能用 UNION ALL 就不要用 UNIONUNION ALL不執(zhí)行SELECT DISTINCT函數(shù)，這樣就會減少很多不必要的資源7. 盡量避免大事務(wù)操作，慎用LOCK子句，提高系統(tǒng)并發(fā)能力。8. 盡量避免反復(fù)訪問同一張或幾張表，尤其是數(shù)據(jù)量較大的表，可以考慮先根據(jù)條件提取數(shù)據(jù)到 臨時表中，然后再做連接。9. 盡量避免使用游標(biāo)，因為游標(biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1 萬行，那么就應(yīng)該改寫；如果使用了游標(biāo)，就要盡量避免在游標(biāo)循環(huán)中再進(jìn)行表連接的操作。10. 注意 where 字句寫法 ，必須考慮語句順序，應(yīng)該根據(jù)索引順序、范圍大小來確定條件子句的前 后