SQL Server優化50法匯總。本站提示廣大學習愛好者：（SQL Server優化50法匯總）文章只能為提供參考，不一定能成為您想要的結果。以下是SQL Server優化50法匯總正文
查詢速度慢的緣由許多，罕見以下幾種：
1.沒有索引或許沒有效到索引(這是查詢慢最多見的成績，是法式設計的缺點)
2.I/O吞吐量小，構成了瓶頸效應。
3.沒有創立盤算列招致查詢不優化。
4.內存缺乏
5.收集速度慢
6.查詢出的數據量過年夜(可以采取屢次查詢，其他的辦法下降數據量)
7.鎖或許逝世鎖(這也是查詢慢最多見的成績，是法式設計的缺點)
8.sp_lock, sp_who, 運動的用戶檢查，緣由是讀寫競爭資本。
9.前往了不用要的行和列
10.查詢語句欠好，沒有優化
可以經由過程以下辦法來優化查詢 :
1.把數據、日記、索引放到分歧的I/O裝備上，增長讀取速度，之前可以將Tempdb應放在RAID0上，SQL2000不再支撐。數據量(尺寸)越年夜，進步I/O越主要。
2.縱向、橫向朋分表，削減表的尺寸(sp_spaceuse)
3.進級硬件
4.依據查詢前提,樹立索引、優化索引、優化拜訪方法，限制成果集的數據量。留意填充因子要恰當(最好是應用默許值0)。索引應當盡可能小，應用字節數小的列建索引好(參照索引的創立)，不要對無限的幾個值的字段建單一索引如性別字段
5.進步網速
6.擴展辦事器的內存，Windows 2000和SQL server 2000能支撐4-8G的內存。設置裝備擺設虛擬內存：虛擬內存年夜小應基於盤算機上並發運轉的辦事停止設置裝備擺設。運轉 Microsoft SQL Server 2000 時，可斟酌將虛擬內存年夜小設置為盤算機中裝置的物理內存的 1.5 倍。假如別的裝置了全文檢索功效，並盤算運轉 Microsoft 搜刮辦事以便履行全文索引和查詢，可斟酌：將虛擬內存年夜小設置裝備擺設為至多是盤算機中裝置的物理內存的 3 倍。將 SQL Server max server memory 辦事器設置裝備擺設選項設置裝備擺設為物理內存的 1.5 倍(虛擬內存年夜小設置的一半)。
7.增長辦事器CPU個數，然則必需明確並行處置比串行處置更須要資本例如內存。應用並行照樣串行程是MsSQL主動評價選擇的。單個義務分化成多個義務，便可以在處置器上運轉。例如耽誤查詢的排序、銜接、掃描和GROUP BY字句同時履行，SQL SERVER依據體系的負載情形決議最優的並行品級，龐雜的須要消費年夜量的CPU的查詢最合適並行處置。然則更新操作UPDATE,INSERT，DELETE還不克不及並行處置。
8.假如是應用like停止查詢的話，簡略的應用index是不可的，然則全文索引耗空間。 like 'a%' 應用索引，like '%a' 不應用索引。用 like '%a%' 查詢時，查詢耗時和字段值總長度成反比，所以不克不及用CHAR類型，而是VARCHAR。關於字段的值很長的建全文索引。
9.DB Server 和 Application Server 分別; OLTP和OLAP分別
10.散布式分區視圖可用於完成數據庫辦事器結合體。結合體是一組離開治理的辦事器，但它們互相協作分管體系的處置負荷。這類經由過程分區數據構成數據庫辦事器結合體的機制可以或許擴展一組辦事器，以支撐年夜型的多層 Web 站點的處置須要。
1.在完成分區視圖之前，必需先程度分區表
2.在創立成員表後，在每一個成員辦事器上界說一個散布式分區視圖，而且每一個視圖具有雷同的稱號。如許，援用散布式分區視圖名的查詢可以在任何一個成員辦事器上運轉。體系操作好像每一個成員辦事器上都有一個原始表的復本一樣，但其實每一個辦事器上只要一個成員表和一個散布式分區視圖。數據的地位對運用法式是通明的。
11.重建索引DBCC REINDEX, DBCC INDEXDEFRAG, 壓縮數據和日記DBCC SHRINKDB, DBCC SHRINKFILE. 設置主動壓縮日記。關於年夜的數據庫不要設置數據庫主動增加，它會下降辦事器的機能。 在T-SQL的寫法上有很年夜的講求，上面列出罕見的要點：
起首，DBMS處置查詢籌劃的進程是如許的：
1. 查詢語句的詞法、語法檢討
2. 將語句提交給DBMS的查詢優化器
3. 優化器做代數優化和存取途徑的優化
4. 由預編譯模塊生成查詢計劃
5. 然後在適合的時光提交給體系處置履行
6. 最初將履行成果前往給用戶其次，看一下SQL SERVER的數據寄存的構造：一個頁面的年夜小為8K(8060)字節，8個頁面為一個盤區，依照B樹寄存。
12.Commit和rollback的差別。Rollback:回滾一切的事務；Commit:提交以後的事務。沒有需要在靜態SQL裡寫事務，假如要寫請寫在裡面，如： begin tran exec(@s) commit trans 或許將靜態SQL 寫成函數或許存儲進程。
13.在查詢Select語句頂用Where字句限制前往的行數，防止表掃描，假如前往不用要的數據，糟蹋了辦事器的I/O資本，減輕了收集的累贅下降機能。假如表很年夜，在表掃描的時代將表鎖住，制止其他的聯接拜訪表，不然效果嚴重。
14.SQL的正文聲名對履行沒有任何影響。
15.盡量不應用光標，它占用年夜量的資本。假如須要row-by-row地履行，盡可能采取非光標技巧,如：在客戶端輪回，用暫時表，Table變量，用子查詢，用Case語句等等。
游標可以依照它所支撐的提取選項停止分類：
1.只進必需依照從第一行到最初一行的次序提取行。FETCH NEXT 是獨一許可的提取操作，也是默許方法。
2.可轉動性可以在游標中任何處所隨機提取隨意率性行。
3.游標的技巧在SQL2000下變得功效很壯大，他的目標是支撐輪回。有四個並發選項:
1.READ_ONLY：不許可經由過程游標定位更新(Update)，且在構成成果集的行中沒有鎖。
2.OPTIMISTIC WITH valueS: 悲觀並發掌握是事務掌握實際的一個尺度部門。悲觀並發掌握用於如許的情況，即在翻開游標及更新行的距離中，只要很小的機遇讓第二個用戶更新某一行。當某個游標以此選項翻開時，沒有鎖掌握個中的行，這將有助於最年夜化其處置才能。假如用戶試圖修正某一行，則此行確當前值會與最初一次提取此行時獲得的值停止比擬。假如任何值產生轉變，則辦事器就會曉得其別人已更新了此行，並會前往一個毛病。假如值是一樣的，辦事器就履行修正，選擇這個並發選項.
3.OPTIMISTIC WITH ROW VERSIONING: 此悲觀並發掌握選項基於行版本掌握。應用行版本掌握，個中的表必需具有某種版本標識符，辦事器可用它來肯定該行在讀入游標後能否有所更改。在 SQL Server 中，這特性能由 timestamp 數據類型供給，它是一個二進制數字，表現數據庫中更改的絕對次序。每一個數據庫都有一個全局以後時光戳值：@@DBTS。每次以任何方法更改帶有 timestamp 列的行時，SQL Server 先在時光戳列中存儲以後的 @@DBTS 值，然後增長 @@DBTS 的值。假如某個表具有 timestamp 列，則時光戳會被記到行級。辦事器便可以比擬某行確當前時光戳值和前次提取時所存儲的時光戳值，從而肯定該行能否已更新。辦事器不用比擬一切列的值，只需比擬 timestamp 列便可。假如運用法式對沒有 timestamp 列的表請求基於行版本掌握的悲觀並發，則游標默許為基於數值的悲觀並發掌握。
4.SCROLL LOCKS這個選項完成消極並發掌握。在消極並發掌握中，在把數據庫的行讀入游標成果集時，運用法式將試圖鎖定命據庫行。在應用辦事器游標時，將行讀入游標時會在其上放置一個更新鎖。假如在事務內翻開游標，則該事務更新鎖將一向堅持到事務被提交或回滾;當提取下一行時，將除去游標鎖。假如在事務外翻開游標，則提取下一行時，鎖就被拋棄。是以，每當用戶須要完整的消極並發掌握時，游標都應在事務內翻開。更新鎖將阻攔任何其它義務獲得更新鎖或排它鎖，從而阻攔其它義務更新該行。但是，更新鎖其實不阻攔同享鎖，所以它不會阻攔其它義務讀取行，除非第二個義務也在請求帶更新鎖的讀取。轉動鎖依據在游標界說的 SELECT 語句中指定的鎖提醒，這些游標並發選項可以生成轉動鎖。轉動鎖在提取時在每行上獲得，並堅持到下次提取或許游標封閉，以先產生者為准。下次提取時，辦事器為新提取中的行獲得轉動鎖，並釋放前次提取中行的轉動鎖。轉動鎖自力於事務鎖，並可以堅持到一個提交或回滾操作以後。假如提交時封閉游標的選項為關，則 COMMIT 語句其實不封閉任何翻開的游標，並且轉動鎖被保存到提交以後，以保護對所提取數據的隔離。所獲得轉動鎖的類型取決於游標並發選項和游標。
16.用Profiler來跟蹤查詢，獲得查詢所需的時光，找出SQL的成績地點;用索引優化器優化索引.
17.留意UNion和UNion all 的差別：UNION ALL好
18.留意應用DISTINCT，在沒有需要時不要用，它同UNION一樣會使查詢變慢。反復的記載在查詢裡是沒有成績的
19.查詢時不要前往不須要的行、列
20.用sp_configure 'query governor cost limit'或許SET QUERY_GOVERNOR_COST_LIMIT來限制查詢消費的資本。當評價查詢消費的資本超越限制時，辦事器主動撤消查詢,在查詢之前就抹殺失落。SET LOCKTIME設置鎖的時光
21.用select top 100 / 10 Percent 來限制用戶前往的行數或許SET ROWCOUNT來限制操作的行
22.在SQL 2000之前，普通不要用以下的字句: "IS NULL", "", "!=", "!", "!", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'"，由於他們不走索引滿是表掃描。也不要在where字句中的列名加函數，如convert，substring等，假如必需用函數的時刻，創立盤算列再創立索引來替換。還可以變通寫法：WHERE SUBSTRING(firstname,1,1) = 'm' 改成 WHERE firstname like 'm%'(索引掃描)，必定要將函數和列名離開。而且索引不克不及建得太多和太年夜。NOT IN會屢次掃描表，應用EXISTS, NOT EXISTS, IN, LEFT OUTER JOIN 來替換，特殊是左銜接，而Exists比IN更快，最慢的是NOT操作。假如列的值含有空，之前它的索引不起感化，如今2000的優化器可以或許處置了。雷同的是"IS NULL", "NOT", "NOT EXISTS", "NOT IN"能優化她，而""等照樣不克不及優化，用不到索引。
23.應用Query Analyzer，檢查SQL語句的查詢籌劃和評價剖析能否是優化的SQL。普通的20%的代碼占領了80%的資本，我們優化的重點是這些慢的處所。
24.假如應用了IN或許OR等時發明查詢沒有走索引，應用顯示聲名指定索引： SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN ('男'，'女')
25.將須要查詢的成果事後盤算好放在表中，查詢的時刻再SELECT。這在SQL7.0之前是最主要的手腕。例如病院的住院費盤算。
26.MIN() 和 MAX()能應用到適合的索引。
27.數據庫有一個准繩是代碼離數據越近越好，所以優先選擇Default，順次為Rules, Triggers, Constraint(束縛如外健主健CheckUNIQUE……，數據類型的最年夜長度等等都是束縛), Procedure。如許不只保護任務小，編寫法式質量高，而且履行的速度快。
28.假如要拔出年夜的二進制值到Image列，應用存儲進程，萬萬不要用內嵌insert來拔出(不知JAVA能否)。由於如許運用法式起首將二進制值轉換成字符串(尺寸是它的兩倍)，辦事器遭到字符後又將他轉換成二進制值。存儲進程就沒有這些舉措: 辦法：Create procedure p_insert as insert into table(Fimage) values (@image), 在前台挪用這個存儲進程傳入二進制參數，如許處置速度顯著改良。
29.Between在某些時刻比IN速度更快，Between可以或許更快地依據索引找到規模。用查詢優化器可見赴任別。 select * from chineseresume where title in ('男','女') Select * from chineseresume where between '男' and '女' 是一樣的。因為in會在比擬屢次，所以有時會慢些。
30.在需要時對全局或許部分暫時表創立索引，有時可以或許進步速度，但不是必定會如許，由於索引也消耗年夜量的資本。他的創立同是現實表一樣。
31.不要建沒有感化的事務例如發生報表時，糟蹋資本。只要在需要應用事務時應用它。
32.用OR的字句可以分化成多個查詢，而且經由過程UNION 銜接多個查詢。他們的速度只同能否應用索引有關，假如查詢須要用到結合索引，用UNION ALL履行的效力更高。多個OR的字句沒有效到索引，改寫成UNION的情勢再試圖與索引婚配。一個症結的成績能否用到索引。
33.盡可能罕用視圖，它的效力低。對視圖操作比直接對表操作慢，可以用stored procedure來取代她。特殊的是不要用視圖嵌套，嵌套視圖增長了尋覓原始材料的難度。我們看視圖的實質：它是寄存在辦事器上的被優化好了的曾經發生了查詢計劃的SQL。對單個表檢索數據時，不要應用指向多個表的視圖，直接從表檢索或許僅僅包括這個表的視圖上讀，不然增長了不用要的開支，查詢遭到攪擾。為了加速視圖的查詢，MsSQL增長了視圖索引的功效。
34.沒有需要時不要用DISTINCT和ORDER BY，這些舉措可以改在客戶端履行。它們增長了額定的開支。這同UNION 和UNION ALL一樣的事理。
35.在IN前面值的列表中，將湧現最頻仍的值放在最後面，湧現得起碼的放在最初面，削減斷定的次數。
36.當用SELECT INTO時，它會鎖住體系表(sysobjects，sysindexes等等)，壅塞其他的銜接的存取。創立暫時表時用顯示聲名語句，而不是select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit，在另外一個銜接中SELECT * from sysobjects可以看到 SELECT INTO 會鎖住體系表，Create table 也會鎖體系表(不論是暫時表照樣體系表)。所以萬萬不要在事務內應用它!!!如許的話假如是常常要用的暫時表請應用實表，或許暫時表變量。
37.普通在GROUP BY 和HAVING字句之前就可以剔除過剩的行，所以盡可能不要用它們來做剔除行的任務。他們的履行次序應當以下最優：select 的Where字句選擇一切適合的行，Group By用來分組個統計行，Having字句用來剔除過剩的分組。如許Group By 個Having的開支小，查詢快。關於年夜的數據行停止分組和Having非常消費資本。假如Group BY的目標不包含盤算，只是分組，那末用Distinct更快
38.一次更新多筆記錄比分屢次更新每次一條快，就是說批處置好
39.罕用暫時表，盡可能用成果集和Table類型的變量來取代它，Table 類型的變量比暫時表好。
40.在SQL2000下，盤算字段是可以索引的，須要知足的前提以下：
1.盤算字段的表達是肯定的
2.不克不及用在text, ntext, Image數據類型
3.必需配制以下選項 ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….
41.盡可能將數據的處置任務放在辦事器上，削減收集的開支，如應用存儲進程。存儲進程是編譯好、優化過、而且被組織到一個履行計劃裡、且存儲在數據庫中的SQL語句，是掌握流說話的聚集，速度固然快。重復履行的靜態SQL, 可使用暫時存儲進程，該進程(暫時表)被放在Tempdb中。之前因為SQL SERVER對龐雜的數學盤算不支撐，所以不能不將這個任務放在其他的層上而增長收集的開支。SQL2000支撐UDFs, 如今支撐龐雜的數學盤算，函數的前往值不要太年夜，如許的開支很年夜。用戶自界說函數象光標一樣履行的消費年夜量的資本，假如前往年夜的成果采取存儲進程。
42.不要在一句話裡再三的應用雷同的函數，糟蹋資本，將成果放在變量裡再挪用更快。
43.SELECT COUNT(*)的效力較低，盡可能變通他的寫法，而EXISTS快。同時請留意差別： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的前往值是分歧的!!!
44.當辦事器的內存夠多時，配制線程數目 = 最年夜銜接數+5，如許能施展最年夜的效力;不然應用“配制線程數目最年夜銜接數啟用SQL SERVER的線程池”來處理，假如照樣數目 = 最年夜銜接數+5，嚴重的傷害辦事器的機能。
45.依照必定的順序來拜訪你的表。假如你先鎖住表A，再鎖住表B，那末在一切的存儲進程中都要依照這個次序來鎖定它們。假如你(不經意的)某個存儲進程中先鎖定表B，再鎖定表A，這能夠就會招致一個逝世鎖。假如鎖定次序沒有被事後具體的設計好，逝世鎖很難被發明。
46.經由過程SQL Server Performance Monitor監督響應硬件的負載Memory: Page Faults / sec計數器假如該值偶然走高，注解其時有線程競爭內存。假如連續很高，則內存能夠是瓶頸。
1.Process:
1.% DPC Time 指在典范距離時代處置器用在緩延法式挪用(DPC) 吸收和供給辦事的百分比。(DPC 正在運轉的為比尺度距離優先權低的距離)。 因為 DPC 是以特權形式履行的，DPC 時光的百分比為特權時光 百分比的一部門。這些時光零丁盤算而且不屬於距離盤算總數的一部 分。這個總數顯示了作為實例時光百分比的均勻忙時。
2.%Processor Time計數器 假如該參數值連續跨越95%，注解瓶頸是CPU。可以斟酌增長一個處置器或換一個更快的處置器。
3.% Privileged Time 指非閒置處置器時光用於特權形式的百分比。(特權形式是為操作體系組件和把持硬件驅動法式而設計的一種處置形式。它許可直接拜訪硬件和一切內存。另外一種形式為用戶形式，它是一種為運用法式、情況分體系和整數分體系設計的一種無限處置形式。操作體系將運用法式線程轉換成特權形式以拜訪操作體系辦事)。 特權時光的 % 包含為連續和 DPC 供給辦事的時光。特權時光比率高能夠是因為掉敗裝備發生的年夜數目的距離而惹起的。這個計數器將均勻忙時作為樣本時光的一部門顯示。
4.% User Time表現消耗CPU的數據庫操作，如排序，履行aggregate functions等。假如該值很高，可斟酌增長索引，盡可能應用簡略的表聯接，程度朋分年夜表格等辦法來下降該值。 Physical Disk: Curretn Disk Queue Length計數器該值應不跨越磁盤數的1.5~2倍。要進步機能，可增長磁盤。 SQLServer:Cache Hit Ratio計數器該值越高越好。假如連續低於80%，應斟酌增長內存。 留意該參數值是從SQL Server啟動後，就一向累加記數，所以運轉經由一段時光後，該值將不克不及反應體系以後值。
47.剖析select emp_name form. employee where salary 3000 在此語句中若salary是Float類型的，則優化器對其停止優化為Convert(float,3000)，由於3000是個整數，我們應在編程時應用3000.0而不要等運轉時讓DBMS停止轉化。異樣字符和整型數據的轉換。
48.查詢的聯系關系同寫的次序
select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B ,B = '號碼'）
select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B ,B = '號碼'， A = '號碼'）
select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '號碼'， A = '號碼'）
49.
1.IF 沒有輸出擔任人代碼 THEN code1=0 code2=9999 ELSE code1=code2=擔任人代碼 END IF 履行SQL語句為: SELECT 擔任人名 FROM P2000 WHERE 擔任人代碼=:code1 AND擔任人代碼 =:code2
2.IF 沒有輸出擔任人代碼 THEN SELECT 擔任人名 FROM P2000 ELSE code= 擔任人代碼 SELECT 擔任人代碼 FROM P2000 WHERE 擔任人代碼=:code END IF 第一種辦法只用了一條SQL語句,第二種辦法用了兩條SQL語句。在沒有輸出擔任人代碼時，第二種辦法明顯比第一種辦法履行效力高，由於它沒無限制前提; 在輸出了擔任人代碼時，第二種辦法依然比第一種辦法效力高，不只是少了一個限制前提，還因相等運算是最快的查詢運算。我們寫法式不要怕費事。
50.關於JOBCN如今查詢分頁的新辦法（以下），用機能優化器剖析機能的瓶頸，假如在I/O或許收集的速度上，以下的辦法優化實在有用，假如在CPU或許內存上，用如今的辦法更好。請辨別以下的辦法，解釋索引越小越好。
DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))
insert into @local_variable (ReferenceID)
select top 100000 ReferenceID from chineseresume order by ReferenceID
select * from @local_variable where Fid > 40 and fid <= 60
end 和
begin
DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))
insert into @local_variable (ReferenceID)
select top 100000 ReferenceID from chineseresume order by updatedate
select * from @local_variable where Fid > 40 and fid <= 60
end 的分歧
begin
create table #temp (FID int identity(1,1),ReferenceID varchar(20))
insert into #temp (ReferenceID)
select top 100000 ReferenceID from chineseresume order by updatedate
select * from #temp where Fid > 40 and fid <= 60 drop table #temp
end
