SQL2019系統(tǒng)性能優(yōu)化解決方案共12頁文檔

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)生效率 下降的因素是多方面：硬件問題軟件問題實(shí)施問題正因?yàn)楫a(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、每個表只能有一個聚集索引，因?yàn)閿?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é)省成本。非聚集索引非聚集索引具有完全獨(dú)立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引的最低行包含非聚集索引的鍵值， 并且每個鍵值項(xiàng)都有指針指向包含該鍵值的數(shù)據(jù)行。數(shù)據(jù)行不按基于非聚集鍵的次序存儲。如果一個表只有非聚集索引，它的數(shù)據(jù)行將按無序的堆集方式存儲，非聚集索引可以建多個。 唯一索引唯一索引可以確保索引列不包含重復(fù)的值。在多列唯一索引的情況下，該索引可以確保 索引列中每個值組合都是唯一的。唯一索引既是索引也是約束。復(fù)合索引索引項(xiàng)是多個的就叫組合索引，也叫復(fù)合索引。復(fù)合索引使用時需要注意索引項(xiàng)的次序。索引對性能的作用使用索引的優(yōu)點(diǎn)通過唯一性索引（uniqu

5、e）可確保數(shù)據(jù)的唯一性加快數(shù)據(jù)的檢索速度加快表之間的連接減少分組和排序的時間使用索引的原則在需要經(jīng)常搜索的列上創(chuàng)建索引經(jīng)常用于連接的列上創(chuàng)建索引經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引經(jīng)常需要排序的列上創(chuàng)建索引經(jīng)常用于where子句的列上創(chuàng)建索引 不使用索引的原則查詢很少使用和參考的列不建索引對只有少數(shù)值的列不建索引定義為text、image、bit的列不建索引當(dāng)需要update性能遠(yuǎn)遠(yuǎn)高于select性能時不建或少建索引常用命令sp_helpindex :報告表或視圖上的索引信息dbcc showcontig :顯示指定表的數(shù)據(jù)和索引的碎片信息dbcc dbreindex :重建指定數(shù)據(jù)庫中一

6、個或多個索引dbcc indexdefrag :整理指定表或視圖的聚集索引或輔助索引的碎片創(chuàng)建索引定義索引時，可以指定每列的數(shù)據(jù)是按升序還是降序存儲。如果不指定，則默認(rèn)為升 序?yàn)樗饕付ㄌ畛湟蜃樱蓸?biāo)識填充因子來指定每個索引頁的填滿程度。索引頁上的空余空 間量很重要，因?yàn)楫?dāng)索引頁填滿時，系統(tǒng)必須花時間拆分它以便為新行騰出空間。優(yōu)化索引重建索引（dbcc dbreindex）索引優(yōu)化向?qū)д碇付ǖ谋砘蛞晥D的聚集索引和輔助索引碎片（dbcc indexefrag）問題定位時空在產(chǎn)品開發(fā)過程中遵循大開發(fā)理，共四個研發(fā)層次，第一層技術(shù)研發(fā)，由時空技術(shù)研 發(fā)部負(fù)責(zé)產(chǎn)品技術(shù)架構(gòu)，平臺工具的構(gòu)建，第二層產(chǎn)品

7、研發(fā)，由時空產(chǎn)品研發(fā)部負(fù)責(zé)應(yīng)用系統(tǒng) 搭建。第三層項(xià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)方法詳細(xì)列舉出來記錄在調(diào)整方案（見附錄）中。一、檢查數(shù)據(jù)表結(jié)構(gòu)查看在客戶化開發(fā)過程中增加的新表，字段類型是否合適，特別要關(guān)注字段長度較長字符 型字段，可以考慮更改為VARCHAR類型。檢查數(shù)據(jù)表中主鍵設(shè)置情況。明確數(shù)據(jù)

8、表在系統(tǒng) 中存在的意義以及使用情況。檢查系統(tǒng)當(dāng)中頻繁使用的數(shù)據(jù)表：maxbh,spkfk,spkfjc,hwsp,jxdjhz,jxdjmx,mchk,cwk,ywmxk,mxysyf,ywjsmxk,jsmxk, splsk，查看主鍵，索引的設(shè)置是否合理，根據(jù)客戶的實(shí)際使用情況對索引進(jìn)行調(diào)整， 對于在表中新增加的字段，一般來講應(yīng)針對該字段建單鍵索引或復(fù)合索引。把檢查情 況記錄在調(diào)整方案中。二、檢查存儲過程時空產(chǎn)品在發(fā)布時是一個通用版本，為了兼容廣大客戶的需求，在業(yè)務(wù)處理邏輯上需 要考慮方面比較多，而客戶的業(yè)務(wù)流程和需求和產(chǎn)品本身差別可能很大，導(dǎo)致一些存儲過 程改動比較大。例如：SBP_KP_

9、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)整SQL語句或者相關(guān)表的索引。三、檢查檢索方案第一.檢查方案的數(shù)據(jù)過濾條件，盡量避免使用模糊匹配，在模糊查找時進(jìn)行全表掃描，SQL 語句執(zhí)行效率低下。第二.仔細(xì)評定方案中需查詢的字段必要性，減少網(wǎng)絡(luò)流量。第三.盡

10、量減少方案中的連接子句所涉及的數(shù)據(jù)表。第四.如果執(zhí)行結(jié)果對數(shù)據(jù)實(shí)時性要求不高，或者沒有數(shù)量，金額，成本等字段，應(yīng)該使用 鎖定提示（NOLOCK）.第五.根據(jù)客戶使用習(xí)慣，拆分方案，分批獲取所需要的數(shù)據(jù)。如：銷售開票時可以先提取商品，然后再根據(jù)商品內(nèi)碼提取貨位，批號，數(shù)量等信息。第六.分析查詢方案的執(zhí)行計劃，調(diào)整SQL語句或者相關(guān)表索引。四、檢查查詢方案第一.控制查詢方案的字段個數(shù)。第二.明確查詢的過濾條件。第三.提取數(shù)據(jù)時考慮是否有可替代的表（數(shù)據(jù)量?。M量避開操作比較頻繁的數(shù)據(jù)表。第四.對于查詢數(shù)據(jù)實(shí)時性要求不高，應(yīng)該使用鎖定提示（NOLOCK）。五、優(yōu)化數(shù)據(jù)庫布局?jǐn)?shù)據(jù)文件和日志文件的位置

11、和分布對系統(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)建用戶定義文件組，文 件組提供了邏輯地將文件組合地起來的方法，以及將單個文件與主文件組分離的方法，如果 不創(chuàng)建其他文件組，在默認(rèn)情況下，所有文件都進(jìn)入主文件組。當(dāng)在含有多個數(shù)據(jù)文件的文 件組中創(chuàng)建表或索引時，SQL Server使

12、用按比例填充機(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)中一些隱含的問題，否則在實(shí)際運(yùn)行時可能不會達(dá)預(yù)期效果，關(guān)注細(xì)節(jié)只是 一個最基本工作要求。如何提高從宏觀角度去衡量系統(tǒng)所需要的素質(zhì)，首先，必需了解客戶管理理念，管理方式， 熟悉客戶的業(yè)務(wù)流程，從而確定系統(tǒng)應(yīng)該為客戶提供一個什么樣的服務(wù)。其次，了解使用人員 的業(yè)務(wù)需求及其在使用過程中所關(guān)注的信息

13、點(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)行，這時就要考慮在操作過程中使用的方案是 否有效，信息是否有意義，方案中使用的表在哪些環(huán)節(jié)經(jīng)常被使用，在使用的過程中是否被鎖 定，我們可以按照這種方法進(jìn)行橫向或縱向的比較分析，逐步去找出問題的根源。七、SQL語句跟蹤系統(tǒng)效率下降，在許多情

14、況下，產(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語句占用了最長時間，或者哪些語句使用頻率較高。SQL Profiler所提供的預(yù)定義的跟蹤模板，在許多情況下組織和功能都非常優(yōu)秀，可以根據(jù)特性需 求修改這些跟蹤模板，并將這些修改后的跟蹤模板保存為新模板，這樣可以減少大量工作。這

15、 些預(yù)定義跟蹤模板如下所示：Standard(SQLServerProfilerStandard.tdf)提供所執(zhí)行的 SQL 語句和所完成的 SQL 批處理的詳細(xì)息Stored Procedure Counts(SQLServerProfilerSP_Counts.tdf)記錄已經(jīng)執(zhí)行的存儲過 程以及這些存儲過程運(yùn)行頻率的數(shù)據(jù)，了解不同的存儲過程運(yùn)行的次數(shù)將有助于確定 哪個存儲過程是 最好的調(diào)整對象。一個執(zhí)行頻率較高，但效率低下的存儲過程是一 個需要調(diào)整的好對象，在這個跟蹤中，增加SP:Completed事件和Duration數(shù)據(jù)是非 常有用的。TSQL(SQLServerProfilerT

16、SQL.tdf)按照SQL語句的提交順序搜集SQL語句，可以使用 這些信息來查看系統(tǒng)的活動?？梢詫⑦@些活動與系統(tǒng)的其它的事件相關(guān)聯(lián)例如，死鎖 或其它系統(tǒng)問題TSQL By Duration(SQLServerProfilerTSQL_Duration.tdf) 顯示已經(jīng)執(zhí)行的 SQL 語 句以及執(zhí)行這些SQL語句所需要的時間。TSQL Grouped(SQLServerProfilerTSQL_Grouped.tdf)提供已經(jīng)執(zhí)行的 SQL 語句的詳 細(xì)信息并且是根據(jù)應(yīng)用程序名稱，WINDOWS NT用戶名稱以及進(jìn)程ID進(jìn)行分組。這個 信息對于查找特定用戶報告的問題非常有用，例如少數(shù)用戶正在經(jīng)

17、歷死鎖。通過檢查 SQL批處理開始的時間戳，可以清楚地知道應(yīng)用程序中每一步執(zhí)行所花費(fèi)的時間。TSQL Stored Procedures(SQLServerProfilerTSQL_SPs.tdf)顯示存儲過程和存儲過程 內(nèi)部的SQL命令。結(jié)果按照時間順序進(jìn)行排序，對于那些調(diào)用存儲過程的過程意義較 大。應(yīng)用示例：查找運(yùn)行時間較長的SQL語句查找長時間運(yùn)行的查詢的最好方法是使用下面的事件，并按Duration(時間的)持續(xù)) 數(shù)據(jù)列分組.TSQL,SQL: BatchComplete SQL批處理完成執(zhí)行所花費(fèi)的時間.根據(jù)Duration數(shù)據(jù)列的順序進(jìn)行分組，將使跟蹤的結(jié)果按照每一個語句執(zhí)行所需

18、的時 間進(jìn)行排序，在跟蹤數(shù)據(jù)窗口的底部列出了運(yùn)行時間最長的SQL語句，這可能是調(diào)整 系統(tǒng)性能的最好地方。查找資源消耗型作業(yè)這種跟蹤類型查看消耗了 CPU和I/O資源的SQL語句。最佳方法是選擇以下事件進(jìn)行 監(jiān)視，并按照CPU,Reads或者Writes列進(jìn)行分組，這取決于你更關(guān)心I/O資源還是 CPU資源的使用。.TSQL,SQL: BatchComplete SQL 批處理完成執(zhí)行所需的時間 CPU,Reads, Writes 數(shù)據(jù)列將顯示由該事件使用的資源。檢測死鎖產(chǎn)生死鎖現(xiàn)象，對于系統(tǒng)來講是一個非常嚴(yán)重問題，尤其對在線事務(wù)處理（OLTP）影 響非常大，那么查找死鎖產(chǎn)生的原也就等同于改善系

19、統(tǒng)性能。在跟蹤定義選擇下列事 件。.TSQL, SQL :BatchStarting 正運(yùn)行的 SQL 批處理。.Locks,Lock :Deadlock 死鎖本身事件。.Locks,Lock :Deadlockchain導(dǎo)致死鎖的一系列事件。八、查看執(zhí)行計劃SQL查詢分析器，是一個非常重要的工具，在系統(tǒng)效率調(diào)整過程具有不可替代的作 用，它允許用戶對SQL SERVER數(shù)據(jù)庫運(yùn)行特定查詢，還可以提供一個查詢所消耗的系統(tǒng) 資源的信息，這些信息在分析和調(diào)整系統(tǒng)性能方面很有幫助，技術(shù)人員能夠交互式地設(shè) 計和測試SQL語句。在實(shí)際操作中，應(yīng)遵循這樣一個原則：盡量避免全表掃描，全表掃描非常消耗系統(tǒng) 資源

20、，通過建主鍵或者調(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á)到我們管理控制的要求。比如：銷售開票時，為了避免負(fù)庫存銷售，經(jīng)常要校驗(yàn)商品已開票未出庫數(shù)量，一 般的方法就是在檢索商品的時候與jxdjmx表關(guān)聯(lián)。這樣的處理邏輯達(dá)到了攔截負(fù)庫存的 目的，便不是最優(yōu)的。隨著表中數(shù)據(jù)量的逐漸增長，開票的效率也

21、逐漸下降，直至系統(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ù)校驗(yàn)時我們就可 以避開原來的大表，直接從臨時表提取數(shù)據(jù)。接下來的問題是在什么時機(jī)刪除臨時表中 的數(shù)據(jù)，對于這個問題我們首先要確認(rèn)，在什么時候票據(jù)完成了它所承載的業(yè)務(wù)活動， 應(yīng)該是已執(zhí)行的票據(jù)，或者是狀態(tài)為清的票據(jù)，那么我們可以在jxdjmx表中增加觸發(fā) 器一一當(dāng)回寫is_zx字段時，

22、通過單據(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)效率。在封存過程中要注意控制數(shù)據(jù)量(一般不超過30萬條數(shù)據(jù))十一、流程重組業(yè)務(wù)流程是企業(yè)為了實(shí)現(xiàn)某一特定目標(biāo)而采取的一系列行動。一個流程包括許多項(xiàng) 活動。流程重組就是對企業(yè)的業(yè)務(wù)流程進(jìn)行根本性的再思考和改變，從調(diào)整效率角度來 看，重組主要目的

23、把系統(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é)算一一出庫，這樣 可以減輕出庫環(huán)節(jié)的系統(tǒng)壓力，保證整個業(yè)務(wù)流程高效，快速運(yùn)行。在流程重組過程中要充分考慮企業(yè)管理和控制要求，同時兼顧系統(tǒng)本身運(yùn)行特點(diǎn)， 從而達(dá)到雙贏的結(jié)果。十二、 移動tempdbTempdb在SQL SERVERK是一個臨時數(shù)據(jù)庫，它對性能的影響較大

24、。tempdb和其他 數(shù)據(jù)庫一樣可以增大，可以縮小。當(dāng)數(shù)據(jù)文件需要增長的時候，通常不能保持剩余部分 的連續(xù)性。這時文件就會產(chǎn)生碎片，這種碎片會造成性能下降。這種碎片屬于外來性碎 片。要阻止在tempdb中產(chǎn)生外來性碎片，必須保證有足夠的硬盤空間。一般將tempdb 的容量放到平均使用容量。而你也應(yīng)該允許tempdb自動增長，比如你有個一個超大的 join操作，它建立了一個超過tempdb容量的時候，該查詢將失敗。你還要設(shè)置一個合 理的單位增長量。因?yàn)槿绻阍O(shè)得太小，將會產(chǎn)生許多外來性碎片，反而會占用更多資 源。sqlserver調(diào)優(yōu)最有效的做法之一，就是把爭奪資源的操作獨(dú)立出去tempdb就是

25、 一個需要獨(dú)立出去的部分，而tempdb和其他系統(tǒng)庫一樣是公用的，是存取最可能頻繁的 庫，所有處理臨時表、子查詢、GROUP BY、排序、DISTINCT、連接等等。它最適合放到 一個具有快速讀寫能力的設(shè)備上。移動tempdb的方法：sp_helpdb查看tempdb現(xiàn)在存放的位置alter database tmpdb modify file(name=tempdev,filename=newpathnewfilename,size=500mb)alter database tmpdb modify file (name=templog,filename=newpathnewfilename

26、,size=500mb)關(guān)閉SQL SERVER重啟刪掉舊的tempdb文件十三、服務(wù)器性能監(jiān)控調(diào)整CPU要監(jiān)視CPU的使用情況，必須保障對系統(tǒng)處理非常繁忙的某一天全天進(jìn)行監(jiān)視，這樣就 可以對系統(tǒng)進(jìn)行配置，從而能處理最繁忙的任務(wù)，可以使用System Monitor并選擇如下的對象和計數(shù)器進(jìn)行監(jiān)視：Processor Object（處理器對象），% Processor Time（處理器時間計數(shù)），選擇所有實(shí)例 可以查看每個處理器的使用情況，以及所有處理器的平均使用率.如果處理器使用率保持在 80%或更高，或者經(jīng)常出現(xiàn)峰值使用率，系統(tǒng)就可能具有CPU瓶頸，可以在系統(tǒng)中添加更多 或更快的處理器，這

27、樣就可以提高系統(tǒng)性能.調(diào)整內(nèi)存如果條件允許，最好是SQL Server獨(dú)立占用數(shù)據(jù)庫服務(wù)器，這就允許SQL Server能夠 盡可能地使用系統(tǒng)內(nèi)存，而不用與其他應(yīng)用程序一起共享系統(tǒng)內(nèi)存。通過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)存大小.SQL Server: Buffer Manager Object,Buffer Cache Hit Ratio 緩沖存

28、儲器命中率如果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)存的使用率。磁盤調(diào)整監(jiān)測磁盤I/O使用System Monitor并選擇PhysicalDisk對象，并選用下列計數(shù)器：Disk Reads/sec所選擇磁盤每秒所執(zhí)行的讀操作數(shù)Disk Writes/sec所選擇磁盤每秒所執(zhí)行的寫操作分析磁盤指標(biāo)（可參考制造商規(guī)格說書）：平均尋道時

29、間（毫秒）=平均尋道（讀）+平均尋道（寫）/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ù)/磁盤數(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)存在瓶頸，需要添加磁

30、 盤：磁盤個數(shù)二I/0操作數(shù)/磁盤最佳I/O數(shù)據(jù)庫調(diào)整備份數(shù)據(jù)庫用備份文件重新恢復(fù)一個測試庫根據(jù)調(diào)整方案記錄的問題及改進(jìn)方法，在測試庫中修改，并把實(shí)際修改結(jié)果記 錄調(diào)整方案中.系統(tǒng)測試測試成功后，調(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)死鎖。通過實(shí)地觀察系統(tǒng)使用情況：銷售開票檢索商品，開票結(jié)算存盤速度較慢。查看商品檢索方案， 方案中為了 獲取商品的批號，數(shù)量等信息使用了視圖：select spid,pihao,sum(shl) shl from

31、 sphwph group by spid,pihao。同時為了攔截負(fù)庫存銷售，關(guān)聯(lián)jxdjhz,jxdjmx表，沖減已開票未 執(zhí)行的商品數(shù)量，而且商品的過濾條件為模糊查找。經(jīng)過分析，視圖在每次檢索商品時都要對所有 的品種分組求和，對系統(tǒng)壓力較大，如果這時有出庫業(yè)務(wù)發(fā)生，記帳回寫sphwph表，開票將會產(chǎn)生 資源等待。針對這種情況，作出如下調(diào)整：取消視圖，直接在方案中關(guān)聯(lián)sphwph表，通過商品過濾，減少分組求合的運(yùn)算量，減少 與出庫記帳沖突的概率。新建一業(yè)務(wù)臨時表，在銷售開票時，另存一份明細(xì)到該表，從臨時表中檢索已開票未執(zhí) 行數(shù)據(jù)。在jxdjmx表中增加觸發(fā)器，當(dāng)回寫is_zx字段時，刪除臨

32、時表中數(shù)據(jù)同客戶技術(shù)人員協(xié)商，商品檢索采用左匹配方式跟蹤查看開票結(jié)算存儲過程，發(fā)現(xiàn)回寫mxysyf語句執(zhí)行時間較長，分析客戶實(shí)際業(yè)務(wù)，這個時機(jī)還 沒有產(chǎn)生應(yīng)收應(yīng)付信息，所以把回寫語句刪除。通過針對上述幾個問題的處理，系統(tǒng)效率得到了很大提升。過程編寫技術(shù)保證在實(shí)現(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；在

33、可能的情況下盡量限制盡量結(jié)果集行數(shù)，如：SELECT TOP 300 C1,C2 FROM T1,因?yàn)槟承┣闆r下用戶是不需要那么多的數(shù)據(jù)的，避免 用!二或0)SELECT SUM(T1.C1) FROM T1 WHERE EXISTS( SELECT 1 FROM T2 WHERE T2.C2=T1.C2) 兩者產(chǎn)生相同的結(jié)果，但是后者的效率顯然要高于前者。因?yàn)楹笳卟粫a(chǎn)生大量鎖定的表 掃描或是索引掃描。如果你想校驗(yàn)表里是否存在某條紀(jì)錄，不要用count(*)那樣效率很低，而且浪費(fèi)服務(wù)器資源。可以用EXISTS代替。如：IF (SELECT COUNT(1) FROM table_name W

34、HERE column_name = xxx)0 可以寫成：IF EXISTS (SELECT 1 FROM table_name WHERE column_name = xxx)經(jīng)常需要寫一個T_SQL語句比較一個父結(jié)果集和子結(jié)果集，從而找到是否存在在父結(jié)果集中有 而在子結(jié)果集中沒有的記錄，如：SELECT a.C1 FROM T1 aWHERE NOT EXISTS (SELECT 1 FROM T2 b WHERE a.C1 = b.C1)SELECT a.C1 FROM T1 aLEFT JOIN T2 b ON a.C1 = b.C1 WHERE b.C1 IS NULLSELECT

35、 a.C1 FROM T1 aWHERE a.C1 NOT IN (SELECT C1 FROM T2)三種寫法都可以得到同樣正確的結(jié)果，但是效率依次降低。能夠用BETWEEN的就不要用INSELECT * FROM T1 WHERE ID IN (10,11,12,13,14)改成：SELECT * FROM T1 WHERE ID BETWEEN 10 AND 14因?yàn)镮N會使系統(tǒng)無法使用索引，而只能直接搜索表中的數(shù)據(jù)。能夠用DISTINCT的就不用GROUP BYSELECT C1 FROM T1 WHERE C2 10 GROUP BY C1可改為：SELECT DISTINCT C1 FROM T1 WHERE C2 10能用UNION ALL就不要用UNIONUNION ALL不執(zhí)行SELECT DISTINCT函數(shù)，這樣就會減少很多不必要的資源盡量避免大事務(wù)操作，慎用LOCK子句，提高系統(tǒng)并發(fā)能力。盡量避免反復(fù)訪問同一張或幾張表，尤其是數(shù)據(jù)量較大的表，可以考慮先根據(jù)條件提取數(shù)據(jù)到 臨時表中，然后再做連接。盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該改寫; 如果使用了游標(biāo)，就要盡量避免在游標(biāo)循環(huán)中再進(jìn)行表連接的操作。注意where字句寫法，必須考慮