原文地址

http://www.cnblogs.com/zhycyq/articles/2636748.html

50種方法優(yōu)化SQL Server數(shù)據(jù)庫(kù)查詢

查詢速度慢的原因很多，常見(jiàn)如下幾種：

　　1、沒(méi)有索引或者沒(méi)有用到索引(這是查詢慢最常見(jiàn)的問(wèn)題，是程序設(shè)計(jì)的缺陷)

　　2、I/O吞吐量小，形成了瓶頸效應(yīng)。

　　3、沒(méi)有創(chuàng)建計(jì)算列導(dǎo)致查詢不優(yōu)化。

　　4、內(nèi)存不足

　　5、網(wǎng)絡(luò)速度慢

　　6、查詢出的數(shù)據(jù)量過(guò)大（可以采用多次查詢，其他的方法降低數(shù)據(jù)量）

　　7、鎖或者死鎖(這也是查詢慢最常見(jiàn)的問(wèn)題，是程序設(shè)計(jì)的缺陷)

　　8、sp_lock,sp_who,活動(dòng)的用戶查看,原因是讀寫競(jìng)爭(zhēng)資源。

　　9、返回了不必要的行和列

　　10、查詢語(yǔ)句不好，沒(méi)有優(yōu)化

　　可以通過(guò)如下方法來(lái)優(yōu)化查詢 :

　　1、把數(shù)據(jù)、日志、索引放到不同的I/O設(shè)備上，增加讀取速度，以前可以將Tempdb應(yīng)放在RAID0上，SQL2000不在支持。數(shù)據(jù)量（尺寸）越大，提高I/O越重要.

　　2、縱向、橫向分割表，減少表的尺寸(sp_spaceuse)

　　3、升級(jí)硬件

　　4、根據(jù)查詢條件,建立索引,優(yōu)化索引、優(yōu)化訪問(wèn)方式，限制結(jié)果集的數(shù)據(jù)量。注意填充因子要適當(dāng)（最好是使用默認(rèn)值0）。索引應(yīng)該盡量小，使用字節(jié)數(shù)小的列建索引好（參照索引的創(chuàng)建）,不要對(duì)有限的幾個(gè)值的字段建單一索引如性別字段

　　5、提高網(wǎng)速;

　　6、擴(kuò)大服務(wù)器的內(nèi)存,Windows 2000和SQL server 2000能支持4-8G的內(nèi)存。配置虛擬內(nèi)存：虛擬內(nèi)存大小應(yīng)基于計(jì)算機(jī)上并發(fā)運(yùn)行的服務(wù)進(jìn)行配置。運(yùn)行 Microsoft SQL Server? 2000 時(shí)，可考慮將虛擬內(nèi)存大小設(shè)置為計(jì)算機(jī)中安裝的物理內(nèi)存的 1.5 倍。如果另外安裝了全文檢索功能，并打算運(yùn)行 Microsoft 搜索服務(wù)以便執(zhí)行全文索引和查詢，可考慮：將虛擬內(nèi)存大小配置為至少是計(jì)算機(jī)中安裝的物理內(nèi)存的 3 倍。將 SQL Server max server memory 服務(wù)器配置選項(xiàng)配置為物理內(nèi)存的 1.5 倍（虛擬內(nèi)存大小設(shè)置的一半）。

　　7、增加服務(wù)器 CPU個(gè)數(shù);但是必須明白并行處理串行處理更需要資源例如內(nèi)存。使用并行還是串行程是MsSQL自動(dòng)評(píng)估選擇的。單個(gè)任務(wù)分解成多個(gè)任務(wù)，就可以在處理器 上運(yùn)行。例如耽擱查詢的排序、連接、掃描和GROUP BY字句同時(shí)執(zhí)行，SQL SERVER根據(jù)系統(tǒng)的負(fù)載情況決定最優(yōu)的并行等級(jí)，復(fù)雜的需要消耗大量的CPU的查詢最適合并行處理。但是更新操作Update,Insert， Delete還不能并行處理。

　　8、如果是使用like進(jìn)行查詢的話，簡(jiǎn)單的使用index是不行的，但是全文索引，耗空間。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查詢時(shí)，查詢耗時(shí)和字段值總長(zhǎng)度成正比,所以不能用CHAR類型，而是VARCHAR。對(duì)于字段的值很長(zhǎng)的建全文索引。

　　9、DB Server 和APPLication Server 分離；OLTP和OLAP分離

　　10、分布式分區(qū)視圖可用于實(shí)現(xiàn)數(shù)據(jù)庫(kù)服務(wù)器聯(lián)合體。聯(lián)合體是一組分開(kāi)管理的服務(wù)器，但它們相互協(xié)作分擔(dān)系統(tǒng)的處理負(fù)荷。這種通過(guò)分區(qū)數(shù)據(jù)形成數(shù)據(jù)庫(kù) 服務(wù)器聯(lián)合體的機(jī)制能夠擴(kuò)大一組服務(wù)器，以支持大型的多層 Web 站點(diǎn)的處理需要。有關(guān)更多信息，參見(jiàn)設(shè)計(jì)聯(lián)合數(shù)據(jù)庫(kù)服務(wù)器。（參照SQL幫助文件'分區(qū)視圖'）

　　a、在實(shí)現(xiàn)分區(qū)視圖之前，必須先水平分區(qū)表

　　b、在創(chuàng)建成員表后，在每個(gè)成員服務(wù)器上定義一個(gè)分布式分區(qū)視圖，并且每個(gè)視圖具有相同的名稱。這樣，引用分布式分區(qū)視圖名的查詢可以在任何一個(gè)成員 服務(wù)器上運(yùn)行。系統(tǒng)操作如同每個(gè)成員服務(wù)器上都有一個(gè)原始表的復(fù)本一樣，但其實(shí)每個(gè)服務(wù)器上只有一個(gè)成員表和一個(gè)分布式分區(qū)視圖。數(shù)據(jù)的位置對(duì)應(yīng)用程序是 透明的。

　　11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收縮數(shù)據(jù)和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 設(shè)置自動(dòng)收縮日志.對(duì)于大的數(shù)據(jù)庫(kù)不要設(shè)置數(shù)據(jù)庫(kù)自動(dòng)增長(zhǎng)，它會(huì)降低服務(wù)器的性能。在T-sql的寫法上有很大的講究，下面列出常見(jiàn)的要點(diǎn)：首先， DBMS處理查詢計(jì)劃的過(guò)程是這樣的：

　　1、 查詢語(yǔ)句的詞法、語(yǔ)法檢查

　　2、 將語(yǔ)句提交給DBMS的查詢優(yōu)化器

　　3、 優(yōu)化器做代數(shù)優(yōu)化和存取路徑的優(yōu)化

　　4、 由預(yù)編譯模塊生成查詢規(guī)劃

　　5、 然后在合適的時(shí)間提交給系統(tǒng)處理執(zhí)行

　　6、 最后將執(zhí)行結(jié)果返回給用戶其次，看一下SQL SERVER的數(shù)據(jù)存放的結(jié)構(gòu)：一個(gè)頁(yè)面的大小為8K(8060)字節(jié)，8個(gè)頁(yè)面為一個(gè)盤區(qū)，按照B樹(shù)存放。

　　12、Commit和rollback的區(qū)別 Rollback:回滾所有的事物。 Commit:提交當(dāng)前的事物. 沒(méi)有必要在動(dòng)態(tài)SQL里寫事物，如果要寫請(qǐng)寫在外面如： begin tran exec(@s) commit trans 或者將動(dòng)態(tài)SQL 寫成函數(shù)或者存儲(chǔ)過(guò)程。

　　13、在查詢Select語(yǔ)句中用Where字句限制返回的行數(shù),避免表掃描,如果返回不必要的數(shù)據(jù)，浪費(fèi)了服務(wù)器的I/O資源，加重了網(wǎng)絡(luò)的負(fù)擔(dān)降低性能。如果表很大，在表掃描的期間將表鎖住，禁止其他的聯(lián)接訪問(wèn)表,后果嚴(yán)重。

　　14、SQL的注釋申明對(duì)執(zhí)行沒(méi)有任何影響
　　15、盡可能不使用光標(biāo)，它占用大量的資源。如果需要row-by-row地執(zhí)行，盡量采用非光標(biāo)技術(shù),如：在客戶端循環(huán)，用臨時(shí)表，Table變 量，用子查詢，用Case語(yǔ)句等等。游標(biāo)可以按照它所支持的提取選項(xiàng)進(jìn)行分類： 只進(jìn) 必須按照從第一行到最后一行的順序提取行。FETCH NEXT 是唯一允許的提取操作,也是默認(rèn)方式。可滾動(dòng)性可以在游標(biāo)中任何地方隨機(jī)提取任意行。游標(biāo)的技術(shù)在SQL2000下變得功能很強(qiáng)大，他的目的是支持循環(huán)。 有四個(gè)并發(fā)選項(xiàng) READ_ONLY：不允許通過(guò)游標(biāo)定位更新(Update)，且在組成結(jié)果集的行中沒(méi)有鎖。 OPTIMISTIC WITH valueS:樂(lè)觀并發(fā)控制是事務(wù)控制理論的一個(gè)標(biāo)準(zhǔn)部分。樂(lè)觀并發(fā)控制用于這樣的情形，即在打開(kāi)游標(biāo)及更新行的間隔中，只有很小的機(jī)會(huì)讓第二個(gè)用戶更新 某一行。當(dāng)某個(gè)游標(biāo)以此選項(xiàng)打開(kāi)時(shí)，沒(méi)有鎖控制其中的行，這將有助于最大化其處理能力。如果用戶試圖修改某一行，則此行的當(dāng)前值會(huì)與最后一次提取此行時(shí)獲 取的值進(jìn)行比較。如果任何值發(fā)生改變，則服務(wù)器就會(huì)知道其他人已更新了此行，并會(huì)返回一個(gè)錯(cuò)誤。如果值是一樣的，服務(wù)器就執(zhí)行修改。選擇這個(gè)并發(fā)選項(xiàng) OPTIMISTIC WITH ROW VERSIONING:此樂(lè)觀并發(fā)控制選項(xiàng)基于行版本控制。使用行版本控制，其中的表必須具有某種版本標(biāo)識(shí)符，服務(wù)器可用它來(lái)確定該行在讀入游標(biāo)后是否有 所更改。在 SQL Server 中，這個(gè)性能由 timestamp 數(shù)據(jù)類型提供，它是一個(gè)二進(jìn)制數(shù)字，表示數(shù)據(jù)庫(kù)中更改的相對(duì)順序。每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)全局當(dāng)前時(shí)間戳值：@@DBTS。每次以任何方式更改帶有 timestamp 列的行時(shí)，SQL Server 先在時(shí)間戳列中存儲(chǔ)當(dāng)前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某 個(gè)表具有 timestamp 列，則時(shí)間戳?xí)挥浀叫屑?jí)。服務(wù)器就可以比較某行的當(dāng)前時(shí)間戳值和上次提取時(shí)所存儲(chǔ)的時(shí)間戳值，從而確定該行是否已更新。服務(wù)器不必比較所有列的值，只需 比較 timestamp 列即可。如果應(yīng)用程序?qū)](méi)有 timestamp 列的表要求基于行版本控制的樂(lè)觀并發(fā)，則游標(biāo)默認(rèn)為基于數(shù)值的樂(lè)觀并發(fā)控制。 SCROLL LOCKS 這個(gè)選項(xiàng)實(shí)現(xiàn)悲觀并發(fā)控制。在悲觀并發(fā)控制中，在把數(shù)據(jù)庫(kù)的行讀入游標(biāo)結(jié)果集時(shí)，應(yīng)用程序?qū)⒃噲D鎖定數(shù)據(jù)庫(kù)行。在使用服務(wù)器游標(biāo)時(shí)，將行讀入游標(biāo)時(shí)會(huì)在其 上放置一個(gè)更新鎖。如果在事務(wù)內(nèi)打開(kāi)游標(biāo)，則該事務(wù)更新鎖將一直保持到事務(wù)被提交或回滾；當(dāng)提取下一行時(shí)，將除去游標(biāo)鎖。如果在事務(wù)外打開(kāi)游標(biāo)，則提取下 一行時(shí)，鎖就被丟棄。因此，每當(dāng)用戶需要完全的悲觀并發(fā)控制時(shí)，游標(biāo)都應(yīng)在事務(wù)內(nèi)打開(kāi)。更新鎖將阻止任何其它任務(wù)獲取更新鎖或排它鎖，從而阻止其它任務(wù)更 新該行。然而，更新鎖并不阻止共享鎖，所以它不會(huì)阻止其它任務(wù)讀取行，除非第二個(gè)任務(wù)也在要求帶更新鎖的讀取。滾動(dòng)鎖根據(jù)在游標(biāo)定義的 Select 語(yǔ)句中指定的鎖提示，這些游標(biāo)并發(fā)選項(xiàng)可以生成滾動(dòng)鎖。滾動(dòng)鎖在提取時(shí)在每行上獲取，并保持到下次提取或者游標(biāo)關(guān)閉，以先發(fā)生者為準(zhǔn)。下次提取時(shí)，服務(wù)器 為新提取中的行獲取滾動(dòng)鎖，并釋放上次提取中行的滾動(dòng)鎖。滾動(dòng)鎖獨(dú)立于事務(wù)鎖，并可以保持到一個(gè)提交或回滾操作之后。如果提交時(shí)關(guān)閉游標(biāo)的選項(xiàng)為關(guān)，則 COMMIT 語(yǔ)句并不關(guān)閉任何打開(kāi)的游標(biāo)，而且滾動(dòng)鎖被保留到提交之后，以維護(hù)對(duì)所提取數(shù)據(jù)的隔離。所獲取滾動(dòng)鎖的類型取決于游標(biāo)并發(fā)選項(xiàng)和游標(biāo) Select 語(yǔ)句中的鎖提示。鎖提示 只讀 樂(lè)觀數(shù)值 樂(lè)觀行版本控制 鎖定無(wú)提示 未鎖定 未鎖定 未鎖定 更新 NOLOCK 未鎖定未鎖定未鎖定 未鎖定 HOLDLOCK 共享 共享 共享 更新 UPDLOCK 錯(cuò)誤 更新 更新 更新 TABLOCKX 錯(cuò)誤 未鎖定未鎖定更新其它 未鎖定 未鎖定 未鎖定 更新 *指定 NOLOCK 提示將使指定了該提示的表在游標(biāo)內(nèi)是只讀的。

　　16、用Profiler來(lái)跟蹤查詢，得到查詢所需的時(shí)間，找出SQL的問(wèn)題所在;用索引優(yōu)化器優(yōu)化索引

　　17、注意UNion和UNion all 的區(qū)別。UNION all好

　　18、注意使用DISTINCT，在沒(méi)有必要時(shí)不要用，它同UNION一樣會(huì)使查詢變慢。重復(fù)的記錄在查詢里是沒(méi)有問(wèn)題的

　　19、查詢時(shí)不要返回不需要的行、列

　　20、用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT來(lái)限制查詢消耗的資源。當(dāng)評(píng)估查詢消耗的資源超出限制時(shí)，服務(wù)器自動(dòng)取消查詢,在查詢之前就扼殺掉。 SET LOCKTIME設(shè)置鎖的時(shí)間

　　21、用select top 100 / 10 Percent 來(lái)限制用戶返回的行數(shù)或者SET ROWCOUNT來(lái)限制操作的行

　　22、在SQL2000以前，一般不要用如下的字句: "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'"，因?yàn)樗麄儾蛔咚饕潜頀呙琛Ｒ膊灰赪here字句中的列名加函數(shù)，如Convert，substring等,如果必須用函數(shù)的時(shí)候， 創(chuàng)建計(jì)算列再創(chuàng)建索引來(lái)替代.還可以變通寫法：Where SUBSTRING(firstname,1,1) = 'm'改為Where firstname like 'm%'（索引掃描），一定要將函數(shù)和列名分開(kāi)。并且索引不能建得太多和太大。NOT IN會(huì)多次掃描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 來(lái)替代，特別是左連接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，現(xiàn)在2000的優(yōu)化器能夠處理了。相同 的是IS NULL，"NOT", "NOT EXISTS", "NOT IN"能優(yōu)化她，而"<>"等還是不能優(yōu)化，用不到索引。

　　23、使用Query Analyzer，查看SQL語(yǔ)句的查詢計(jì)劃和評(píng)估分析是否是優(yōu)化的SQL。一般的20%的代碼占據(jù)了80%的資源，我們優(yōu)化的重點(diǎn)是這些慢的地方。

　　24、如果使用了IN或者OR等時(shí)發(fā)現(xiàn)查詢沒(méi)有走索引，使用顯示申明指定索引： Select * FROM PersonMember (INDEX = IX_Title) Where processid IN ('男'，'女')

　　25、將需要查詢的結(jié)果預(yù)先計(jì)算好放在表中，查詢的時(shí)候再Select。這在SQL7.0以前是最重要的手段。例如醫(yī)院的住院費(fèi)計(jì)算。

　　26、MIN() 和 MAX()能使用到合適的索引。

　　27、數(shù)據(jù)庫(kù)有一個(gè)原則是代碼離數(shù)據(jù)越近越好，所以優(yōu)先選擇Default,依次為Rules,Triggers, Constraint（約束如外健主健CheckUNIQUE……,數(shù)據(jù)類型的最大長(zhǎng)度等等都是約束）,Procedure.這樣不僅維護(hù)工作小，編寫程 序質(zhì)量高，并且執(zhí)行的速度快。

　　28、如果要插入大的二進(jìn)制值到Image列，使用存儲(chǔ)過(guò)程，千萬(wàn)不要用內(nèi)嵌Insert來(lái)插入(不知JAVA是否)。因?yàn)檫@樣應(yīng)用程序首先將二進(jìn)制 值轉(zhuǎn)換成字符串（尺寸是它的兩倍），服務(wù)器受到字符后又將他轉(zhuǎn)換成二進(jìn)制值.存儲(chǔ)過(guò)程就沒(méi)有這些動(dòng)作: 方法：Create procedure p_insert as insert into table(Fimage) values (@image), 在前臺(tái)調(diào)用這個(gè)存儲(chǔ)過(guò)程傳入二進(jìn)制參數(shù)，這樣處理速度明顯改善。

　　29、Between在某些時(shí)候比IN 速度更快,Between能夠更快地根據(jù)索引找到范圍。用查詢優(yōu)化器可見(jiàn)到差別。 select * from chineseresume where title in ('男','女') Select * from chineseresume where between '男' and '女' 是一樣的。由于in會(huì)在比較多次，所以有時(shí)會(huì)慢些。

　　30、在必要是對(duì)全局或者局部臨時(shí)表創(chuàng)建索引，有時(shí)能夠提高速度，但不是一定會(huì)這樣，因?yàn)樗饕埠馁M(fèi)大量的資源。他的創(chuàng)建同是實(shí)際表一樣。

　　31、不要建沒(méi)有作用的事物例如產(chǎn)生報(bào)表時(shí)，浪費(fèi)資源。只有在必要使用事物時(shí)使用它。

　　32、用OR的字句可以分解成多個(gè)查詢，并且通過(guò)UNION 連接多個(gè)查詢。他們的速度只同是否使用索引有關(guān),如果查詢需要用到聯(lián)合索引，用UNION all執(zhí)行的效率更高.多個(gè)OR的字句沒(méi)有用到索引，改寫成UNION的形式再試圖與索引匹配。一個(gè)關(guān)鍵的問(wèn)題是否用到索引。

　　 33、盡量少用視圖，它的效率低。對(duì)視圖操作比直接對(duì)表操作慢,可以用stored procedure來(lái)代替她。特別的是不要用視圖嵌套,嵌套視圖增加了尋找原始資料的難度。我們看視圖的本質(zhì)：它是存放在服務(wù)器上的被優(yōu)化好了的已經(jīng)產(chǎn)生 了查詢規(guī)劃的SQL。對(duì)單個(gè)表檢索數(shù)據(jù)時(shí)，不要使用指向多個(gè)表的視圖，直接從表檢索或者僅僅包含這個(gè)表的視圖上讀，否則增加了不必要的開(kāi)銷,查詢受到干 擾.為了加快視圖的查詢，MsSQL增加了視圖索引的功能。

　　34、沒(méi)有必要時(shí)不要用DISTINCT和ORDER BY，這些動(dòng)作可以改在客戶端執(zhí)行。它們?cè)黾恿祟~外的開(kāi)銷。這同UNION 和UNION ALL一樣的道理。

　　select?top?20?ad.companyname,comid,position,ad.referenceid,worklocation,?convert(varchar(10),ad.postDate,120)?as?postDate1,workyear,degreedescription?FROM?jobcn_query.dbo.COMPANYAD_query?ad?where?referenceID?in('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345',
　　'JCNAD00333138','JCNAD00303570','JCNAD00303569',
　　'JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933',
　　'JCNAD00254567','JCNAD00254585','JCNAD00254608',
　　'JCNAD00254607','JCNAD00258524','JCNAD00332133','JCNAD00268618',
　　'JCNAD00279196','JCNAD00268613')?order?by?postdate?desc

　　35、在IN后面值的列表中，將出現(xiàn)最頻繁的值放在最前面，出現(xiàn)得最少的放在最后面，減少判斷的次數(shù)。

　　36、當(dāng)用Select INTO時(shí)，它會(huì)鎖住系統(tǒng)表(sysobjects，sysindexes等等)，阻塞其他的連接的存取。創(chuàng)建臨時(shí)表時(shí)用顯示申明語(yǔ)句，而不是 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit 在另一個(gè)連接中Select * from sysobjects可以看到 Select INTO 會(huì)鎖住系統(tǒng)表，Create table 也會(huì)鎖系統(tǒng)表(不管是臨時(shí)表還是系統(tǒng)表)。所以千萬(wàn)不要在事物內(nèi)使用它！！！這樣的話如果是經(jīng)常要用的臨時(shí)表請(qǐng)使用實(shí)表，或者臨時(shí)表變量。

　　37、一般在GROUP BY 個(gè)HAVING字句之前就能剔除多余的行，所以盡量不要用它們來(lái)做剔除行的工作。他們的執(zhí)行順序應(yīng)該如下最優(yōu)：select 的Where字句選擇所有合適的行，Group By用來(lái)分組個(gè)統(tǒng)計(jì)行，Having字句用來(lái)剔除多余的分組。這樣Group By 個(gè)Having的開(kāi)銷小，查詢快.對(duì)于大的數(shù)據(jù)行進(jìn)行分組和Having十分消耗資源。如果Group BY的目的不包括計(jì)算，只是分組，那么用Distinct更快

　　38、一次更新多條記錄比分多次更新每次一條快,就是說(shuō)批處理好

　　39、少用臨時(shí)表，盡量用結(jié)果集和Table類性的變量來(lái)代替它,Table 類型的變量比臨時(shí)表好

　　40、在SQL2000下，計(jì)算字段是可以索引的，需要滿足的條件如下：

　　a、計(jì)算字段的表達(dá)是確定的

　　b、不能用在TEXT,Ntext，Image數(shù)據(jù)類型

　　c、必須配制如下選項(xiàng) ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….

　　41、盡量將數(shù)據(jù)的處理工作放在服務(wù)器上，減少網(wǎng)絡(luò)的開(kāi)銷，如使用存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程是編譯好、優(yōu)化過(guò)、并且被組織到一個(gè)執(zhí)行規(guī)劃里、且存儲(chǔ)在數(shù)據(jù)庫(kù) 中的SQL語(yǔ)句，是控制流語(yǔ)言的集合，速度當(dāng)然快。反復(fù)執(zhí)行的動(dòng)態(tài)SQL,可以使用臨時(shí)存儲(chǔ)過(guò)程，該過(guò)程（臨時(shí)表）被放在Tempdb中。以前由于SQL SERVER對(duì)復(fù)雜的數(shù)學(xué)計(jì)算不支持，所以不得不將這個(gè)工作放在其他的層上而增加網(wǎng)絡(luò)的開(kāi)銷。SQL2000支持UDFs,現(xiàn)在支持復(fù)雜的數(shù)學(xué)計(jì)算，函數(shù) 的返回值不要太大，這樣的開(kāi)銷很大。用戶自定義函數(shù)象光標(biāo)一樣執(zhí)行的消耗大量的資源，如果返回大的結(jié)果采用存儲(chǔ)過(guò)程

　　42、不要在一句話里再三的使用相同的函數(shù)，浪費(fèi)資源,將結(jié)果放在變量里再調(diào)用更快

　　43、Select COUNT(*)的效率教低，盡量變通他的寫法，而EXISTS快.同時(shí)請(qǐng)注意區(qū)別： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的！！！

　　44、當(dāng)服務(wù)器的內(nèi)存夠多時(shí)，配制線程數(shù)量 = 最大連接數(shù)+5，這樣能發(fā)揮最大的效率；否則使用 配制線程數(shù)量<最大連接數(shù)啟用SQL SERVER的線程池來(lái)解決,如果還是數(shù)量 = 最大連接數(shù)+5，嚴(yán)重的損害服務(wù)器的性能。

　　45、按照一定的次序來(lái)訪問(wèn)你的表。如果你先鎖住表A，再鎖住表B，那么在所有的存儲(chǔ)過(guò)程中都要按照這個(gè)順序來(lái)鎖定它們。如果你（不經(jīng)意的）某個(gè)存儲(chǔ)過(guò)程中先鎖定表B，再鎖定表A，這可能就會(huì)導(dǎo)致一個(gè)死鎖。如果鎖定順序沒(méi)有被預(yù)先詳細(xì)的設(shè)計(jì)好，死鎖很難被發(fā)現(xiàn)

　　46、通過(guò)SQL Server Performance Monitor監(jiān)視相應(yīng)硬件的負(fù)載 Memory: Page Faults / sec計(jì)數(shù)器如果該值偶爾走高，表明當(dāng)時(shí)有線程競(jìng)爭(zhēng)內(nèi)存。如果持續(xù)很高，則內(nèi)存可能是瓶頸。
　　Process:

　　1、% DPC Time 指在范例間隔期間處理器用在緩延程序調(diào)用(DPC)接收和提供服務(wù)的百分比。(DPC 正在運(yùn)行的為比標(biāo)準(zhǔn)間隔優(yōu)先權(quán)低的間隔)。 由于 DPC 是以特權(quán)模式執(zhí)行的，DPC 時(shí)間的百分比為特權(quán)時(shí)間百分比的一部分。這些時(shí)間單獨(dú)計(jì)算并且不屬于間隔計(jì)算總數(shù)的一部 分。這個(gè)總數(shù)顯示了作為實(shí)例時(shí)間百分比的平均忙時(shí)。

　　2、%Processor Time計(jì)數(shù)器　如果該參數(shù)值持續(xù)超過(guò)95%，表明瓶頸是CPU。可以考慮增加一個(gè)處理器或換一個(gè)更快的處理器。

　　3、% Privileged Time 指非閑置處理器時(shí)間用于特權(quán)模式的百分比。(特權(quán)模式是為操作系統(tǒng)組件和操縱硬件驅(qū)動(dòng)程序而設(shè)計(jì)的一種處理模式。它允許直接訪問(wèn)硬件和所有內(nèi)存。另一種模 式為用戶模式，它是一種為應(yīng)用程序、環(huán)境分系統(tǒng)和整數(shù)分系統(tǒng)設(shè)計(jì)的一種有限處理模式。操作系統(tǒng)將應(yīng)用程序線程轉(zhuǎn)換成特權(quán)模式以訪問(wèn)操作系統(tǒng)服務(wù))。特權(quán)時(shí) 間的 % 包括為間斷和 DPC 提供服務(wù)的時(shí)間。特權(quán)時(shí)間比率高可能是由于失敗設(shè)備產(chǎn)生的大數(shù)量的間隔而引起的。這個(gè)計(jì)數(shù)器將平均忙時(shí)作為樣本時(shí)間的一部分顯示。

　　4、% User Time表示耗費(fèi)CPU的數(shù)據(jù)庫(kù)操作，如排序，執(zhí)行aggregate functions等。如果該值很高，可考慮增加索引，盡量使用簡(jiǎn)單的表聯(lián)接，水平分割大表格等方法來(lái)降低該值。 Physical Disk: Curretn Disk Queue Length計(jì)數(shù)器該值應(yīng)不超過(guò)磁盤數(shù)的1.5~2倍。要提高性能，可增加磁盤。 SQLServer:Cache Hit Ratio計(jì)數(shù)器該值越高越好。如果持續(xù)低于80%，應(yīng)考慮增加內(nèi)存。 注意該參數(shù)值是從SQL Server啟動(dòng)后，就一直累加記數(shù)，所以運(yùn)行經(jīng)過(guò)一段時(shí)間后，該值將不能反映系統(tǒng)當(dāng)前值。

　　47、分析select emp_name form employee where salary > 3000 在此語(yǔ)句中若salary是Float類型的，則優(yōu)化器對(duì)其進(jìn)行優(yōu)化為Convert(float,3000)，因?yàn)?000是個(gè)整數(shù)，我們應(yīng)在編程時(shí)使 用3000.0而不要等運(yùn)行時(shí)讓DBMS進(jìn)行轉(zhuǎn)化。同樣字符和整型數(shù)據(jù)的轉(zhuǎn)換。

　　48、查詢的關(guān)聯(lián)同寫的順序

　　select?a.personMemberID,?*?from?chineseresume?a,personmember?b?where?personMemberID?=?b.referenceid?and?a.personMemberID?=?'JCNPRH39681'?（A?=?B?,B?=?'號(hào)碼'）

　　select?a.personMemberID,?*?from?chineseresume?a,personmember?b?where?a.personMemberID?=?b.referenceid?and?a.personMemberID?=?'JCNPRH39681'?and?b.referenceid?=?'JCNPRH39681'?（A?=?B?,B?=?'號(hào)碼'，?A?=?'號(hào)碼'）

　　select?a.personMemberID,?*?from?chineseresume?a,personmember?b?where?b.referenceid?=?'JCNPRH39681'?and?a.personMemberID?=?'JCNPRH39681'?（B?=?'號(hào)碼'，?A?=?'號(hào)碼'）

　　49、

　　(1)IF 沒(méi)有輸入負(fù)責(zé)人代碼 THEN code1=0 code2=9999 ELSE code1=code2=負(fù)責(zé)人代碼 END IF 執(zhí)行SQL語(yǔ)句為: Select 負(fù)責(zé)人名 FROM P2000 Where 負(fù)責(zé)人代碼>=:code1 AND負(fù)責(zé)人代碼 <=:code2

　　(2)IF 沒(méi)有輸入負(fù)責(zé)人代碼 THEN 　Select 負(fù)責(zé)人名 FROM P2000 ELSE code= 負(fù)責(zé)人代碼 Select 負(fù)責(zé)人代碼 FROM P2000 Where 負(fù)責(zé)人代碼=:code END IF 第一種方法只用了一條SQL語(yǔ)句,第二種方法用了兩條SQL語(yǔ)句。在沒(méi)有輸入負(fù)責(zé)人代碼時(shí),第二種方法顯然比第一種方法執(zhí)行效率高,因?yàn)樗鼪](méi)有限制條件; 在輸入了負(fù)責(zé)人代碼時(shí),第二種方法仍然比第一種方法效率高,不僅是少了一個(gè)限制條件,還因相等運(yùn)算是最快的查詢運(yùn)算。我們寫程序不要怕麻煩

　　50、關(guān)于JOBCN現(xiàn)在查詢分頁(yè)的新方法（如下），用性能優(yōu)化器分析性能的瓶頸，如果在I/O或者網(wǎng)絡(luò)的速度上，如下的方法優(yōu)化切實(shí)有效，如果在CPU或者內(nèi)存上，用現(xiàn)在的方法更好。請(qǐng)區(qū)分如下的方法，說(shuō)明索引越小越好。

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?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

?

存儲(chǔ)過(guò)程編寫經(jīng)驗(yàn)和優(yōu)化措施 ?
From：網(wǎng)頁(yè)教學(xué)網(wǎng)

　　一、適合讀者對(duì)象：數(shù)據(jù)庫(kù)開(kāi)發(fā)程序員，數(shù)據(jù)庫(kù)的數(shù)據(jù)量很多，涉及到對(duì)SP（存儲(chǔ)過(guò)程）的優(yōu)化的項(xiàng)目開(kāi)發(fā)人員，對(duì)數(shù)據(jù)庫(kù)有濃厚興趣的人。 　

　　二、介紹：在數(shù)據(jù)庫(kù)的開(kāi)發(fā)過(guò)程中，經(jīng)常會(huì)遇到復(fù)雜的業(yè)務(wù)邏輯和對(duì)數(shù)據(jù)庫(kù)的操作，這個(gè)時(shí)候就會(huì)用SP來(lái)封裝數(shù)據(jù)庫(kù)操作。如果項(xiàng)目的SP較多，書寫 又沒(méi)有一定的規(guī)范，將會(huì)影響以后的系統(tǒng)維護(hù)困難和大SP邏輯的難以理解，另外如果數(shù)據(jù)庫(kù)的數(shù)據(jù)量大或者項(xiàng)目對(duì)SP 的性能要求很，就會(huì)遇到優(yōu)化的問(wèn)題，否則速度有可能很慢，經(jīng)過(guò)親身經(jīng)驗(yàn)，一個(gè)經(jīng)過(guò)優(yōu)化過(guò)的SP要比一個(gè)性能差的SP的效率甚至高幾百倍。 　

　　三、內(nèi)容： 　

　　1、開(kāi)發(fā)人員如果用到其他庫(kù)的Table或View，務(wù)必在當(dāng)前庫(kù)中建立View來(lái)實(shí)現(xiàn)跨庫(kù)操作，最好不要直接使用“databse.dbo.table_name”，因?yàn)閟p_depends不能顯示出該SP所使用的跨庫(kù)table或view，不方便校驗(yàn)。　　

　　2、開(kāi)發(fā)人員在提交SP前，必須已經(jīng)使用set showplan on分析過(guò)查詢計(jì)劃，做過(guò)自身的查詢優(yōu)化檢查。 　

　　3、高程序運(yùn)行效率，優(yōu)化應(yīng)用程序，在SP編寫過(guò)程中應(yīng)該注意以下幾點(diǎn)： 　　

　　a)SQL的使用規(guī)范：

　　　i.　盡量避免大事務(wù)操作，慎用holdlock子句，提高系統(tǒng)并發(fā)能力。

　　　ii.　盡量避免反復(fù)訪問(wèn)同一張或幾張表，尤其是數(shù)據(jù)量較大的表，可以考慮先根據(jù)條件提取數(shù)據(jù)到臨時(shí)表中，然后再做連接。

　　　iii.　盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該改寫；如果使用了游標(biāo)，就要盡量避免在游標(biāo)循環(huán)中再進(jìn)行表連接的操作。

　　　iv.　注意where字句寫法，必須考慮語(yǔ)句順序，應(yīng)該根據(jù)索引順序、范圍大小來(lái)確定條件子句的前后順序，盡可能的讓字段順序與索引順序相一致，范圍從大到小。

　　　v.　不要在where子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無(wú)法正確使用索引。

　　　vi.　盡量使用exists代替select count(1)來(lái)判斷是否存在記錄，count函數(shù)只有在統(tǒng)計(jì)表中所有行數(shù)時(shí)使用，而且count(1)比count(*)更有效率。

　　　vii.　盡量使用“>=”，不要使用“>”。

　　　viii.　注意一些or子句和union子句之間的替換

　　　ix.　注意表之間連接的數(shù)據(jù)類型，避免不同類型數(shù)據(jù)之間的連接。

　　　x.　注意存儲(chǔ)過(guò)程中參數(shù)和數(shù)據(jù)類型的關(guān)系。

　　　xi.　注意insert、update操作的數(shù)據(jù)量，防止與其他應(yīng)用沖突。如果數(shù)據(jù)量超過(guò)200個(gè)數(shù)據(jù)頁(yè)面（400k），那么系統(tǒng)將會(huì)進(jìn)行鎖升級(jí)，頁(yè)級(jí)鎖會(huì)升級(jí)成表級(jí)鎖。 　　

　　b)索引的使用規(guī)范：

　　　i.　索引的創(chuàng)建要與應(yīng)用結(jié)合考慮，建議大的OLTP表不要超過(guò)6個(gè)索引。

　　　ii.　盡可能的使用索引字段作為查詢條件，尤其是聚簇索引，必要時(shí)可以通過(guò)index index_name來(lái)強(qiáng)制指定索引

　　　iii.　避免對(duì)大表查詢時(shí)進(jìn)行table scan，必要時(shí)考慮新建索引。

　　　iv.　在使用索引字段作為條件時(shí)，如果該索引是聯(lián)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用。

　　　v.　要注意索引的維護(hù)，周期性重建索引，重新編譯存儲(chǔ)過(guò)程。　　

　　c)tempdb的使用規(guī)范：

　　　i.　盡量避免使用distinct、order by、group by、having、join、cumpute，因?yàn)檫@些語(yǔ)句會(huì)加重tempdb的負(fù)擔(dān)。

　　　ii.　避免頻繁創(chuàng)建和刪除臨時(shí)表，減少系統(tǒng)表資源的消耗。

　　　iii.　在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用select into代替create table，避免log，提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，建議先create table，然后insert。

　　　iv.　如果臨時(shí)表的數(shù)據(jù)量較大，需要建立索引，那么應(yīng)該將創(chuàng)建臨時(shí)表和建立索引的過(guò)程放在單獨(dú)一個(gè)子存儲(chǔ)過(guò)程中，這樣才能保證系統(tǒng)能夠很好的使用到該臨時(shí)表的索引。

　　　 v.　如果使用到了臨時(shí)表，在存儲(chǔ)過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先truncate table，然后drop table，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定。

　　　 vi.　慎用大的臨時(shí)表與其他大表的連接查詢和修改，減低系統(tǒng)表負(fù)擔(dān)，因?yàn)檫@種操作會(huì)在一條語(yǔ)句中多次使用tempdb的系統(tǒng)表。　　

　　d)合理的算法使用： 　　

　　根據(jù)上面已提到的SQL優(yōu)化技術(shù)和ASE Tuning手冊(cè)中的SQL優(yōu)化內(nèi)容,結(jié)合實(shí)際應(yīng)用,采用多種算法進(jìn)行比較,以獲得消耗資源最少、效率最高的方法。具體可用ASE調(diào)優(yōu)命令：set statistics io on, set statistics time on , set showplan on 等。

解析：Microsoft SQL Server中的鎖模式
在SQL Server數(shù)據(jù)庫(kù)中加鎖時(shí)，除了可以對(duì)不同的資源加鎖，還可以使用不同程度的加鎖方式，即鎖有多種模式，SQL Server中鎖模式包括：

1．共享鎖 SQL Server中，共享鎖用于所有的只讀數(shù)據(jù)操作。共享鎖是非獨(dú)占的，允許多個(gè)并發(fā)事務(wù)讀取其鎖定的資源。默認(rèn)情況下，數(shù)據(jù)被讀取后，SQL Server立即釋放共享鎖。例如，執(zhí)行查詢“SELECT * FROM AUTHORS”時(shí)，首先鎖定第一頁(yè)，讀取之后，釋放對(duì)第一頁(yè)的鎖定，然后鎖定第二頁(yè)。這樣，就允許在讀操作過(guò)程中，修改未被鎖定的第一頁(yè)。但是，事務(wù)隔 離級(jí)別連接選項(xiàng)設(shè)置和SELECT語(yǔ)句中的鎖定設(shè)置都可以改變SQL Server的這種默認(rèn)設(shè)置。例如，“ SELECT * FROM AUTHORS HOLDLOCK”就要求在整個(gè)查詢過(guò)程中，保持對(duì)表的鎖定，直到查詢完成才釋放鎖定。

2．更新鎖更新鎖在修改操作的初始化階段用來(lái)鎖定可能要被修改的資源，這樣可以避免使用共享鎖造成的死鎖現(xiàn)象。因?yàn)槭褂霉蚕礞i時(shí)，修改數(shù)據(jù)的操作分 為兩步，首先獲得一個(gè)共享鎖，讀取數(shù)據(jù)，然后將共享鎖升級(jí)為排它鎖，然后再執(zhí)行修改操作。這樣如果同時(shí)有兩個(gè)或多個(gè)事務(wù)同時(shí)對(duì)一個(gè)事務(wù)申請(qǐng)了共享鎖，在修 改數(shù)據(jù)的時(shí)候，這些事務(wù)都要將共享鎖升級(jí)為排它鎖。這時(shí)，這些事務(wù)都不會(huì)釋放共享鎖而是一直等待對(duì)方釋放，這樣就造成了死鎖。如果一個(gè)數(shù)據(jù)在修改前直接申 請(qǐng)更新鎖，在數(shù)據(jù)修改的時(shí)候再升級(jí)為排它鎖，就可以避免死鎖。

3．排它鎖 排它鎖是為修改數(shù)據(jù)而保留的。它所鎖定的資源，其他事務(wù)不能讀取也不能修改。

4．結(jié)構(gòu)鎖 執(zhí)行表的數(shù)據(jù)定義語(yǔ)言 (DDL) 操作（例如添加列或除去表）時(shí)使用架構(gòu)修改 (Sch-M) 鎖。當(dāng)編譯查詢時(shí)，使用架構(gòu)穩(wěn)定性 (Sch-S) 鎖。架構(gòu)穩(wěn)定性 (Sch-S) 鎖不阻塞任何事務(wù)鎖，包括排它鎖。因此在編譯查詢時(shí)，其它事務(wù)（包括在表上有排它鎖的事務(wù)）都能繼續(xù)運(yùn)行。但不能在表上執(zhí)行 DDL 操作。

5．意向鎖 意向鎖說(shuō)明SQL Server有在資源的低層獲得共享鎖或排它鎖的意向。例如，表級(jí)的共享意向鎖說(shuō)明事務(wù)意圖將排它鎖釋放到表中的頁(yè)或者行。意向鎖又可以分為共享意向鎖、 獨(dú)占意向鎖和共享式獨(dú)占意向鎖。共享意向鎖說(shuō)明事務(wù)意圖在共享意向鎖所鎖定的低層資源上放置共享鎖來(lái)讀取數(shù)據(jù)。獨(dú)占意向鎖說(shuō)明事務(wù)意圖在共享意向鎖所鎖定 的低層資源上放置排它鎖來(lái)修改數(shù)據(jù)。共享式排它鎖說(shuō)明事務(wù)允許其他事務(wù)使用共享鎖來(lái)讀取頂層資源，并意圖在該資源低層上放置排它鎖。

6．大容量更新鎖 當(dāng)將數(shù)據(jù)大容量復(fù)制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 設(shè)置了 table lock on bulk 表選項(xiàng)時(shí)，將使用大容量更新鎖。大容量更新鎖允許進(jìn)程將數(shù)據(jù)并發(fā)地大容量復(fù)制到同一表，同時(shí)防止其它不進(jìn)行大容量復(fù)制數(shù)據(jù)的進(jìn)程訪問(wèn)該表。

詳細(xì)介紹優(yōu)化SQL Server 2000的設(shè)置
　　SQL Server已經(jīng)為了優(yōu)化自己的性能而進(jìn)行了良好的配置，比今天市場(chǎng)其他的關(guān)系型數(shù)據(jù)庫(kù)都要好得多。然而，你仍然有幾項(xiàng)設(shè)置需要進(jìn)行修改，以便你的數(shù)據(jù)庫(kù) 每分鐘可以處理更多的事務(wù)(TPM)。本篇文章的目的就是討論這些設(shè)置。我們忽略那些可以通過(guò)硬件配置或者表或者索引設(shè)計(jì)提高的性能，因?yàn)檫@些內(nèi)容在本篇 文章范圍之外。

　　破碎頁(yè)面檢測(cè)

　　在我們開(kāi)始討論服務(wù)器配置開(kāi)關(guān)之前，讓我們快速瀏覽一下你的模型數(shù)據(jù)庫(kù)--或者說(shuō)用作構(gòu)建新的數(shù)據(jù)庫(kù)的基礎(chǔ)的模板。默認(rèn)情況下，你可以在數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程、函數(shù)等類似的東西，隨后他們將會(huì)被加入新創(chuàng)建的數(shù)據(jù)庫(kù)中。

　　要優(yōu)化性能，你也許想要關(guān)閉模型數(shù)據(jù)庫(kù)中的破碎頁(yè)面檢測(cè)。當(dāng)一個(gè)頁(yè)面被成功寫入磁盤的時(shí)候，破碎頁(yè)面檢測(cè)進(jìn)行識(shí)別。如果激活了的話，你可以看到 每個(gè)寫操作對(duì)性能產(chǎn)生的每個(gè)細(xì)小的影響。大多數(shù)現(xiàn)代的磁盤陣列都有板上電池，使得陣列可以在突然斷電的情況下完成所有的寫操作--引起破碎頁(yè)面的最頻繁原 因。

　　以下的步驟可以接受如何關(guān)閉破碎頁(yè)面檢測(cè):

exec?sp_dboption?'model',?'torn?page?detection',?'false'

　　這篇基礎(chǔ)知識(shí)資源可以為你提供更多有關(guān)這個(gè)設(shè)置的信息。
　　大多數(shù)的配置是通過(guò)系統(tǒng)存儲(chǔ)過(guò)程sp_configure完成的。要顯示服務(wù)器的全部設(shè)置列表以便定制，你可以輸入如下命令:

　sp_configure?'show?advanced?options',?1

　　GO

　　RECONFIGURE?WITH?OVERRIDE

?

　　你可以配置的選項(xiàng)的數(shù)量根據(jù)你的SQL Server的版本、服務(wù)包，以及位數(shù)版本(64位的SQL Server比32位的選項(xiàng)要多)而定。我將直接討論最能影響SQL Server性能優(yōu)化的選項(xiàng)。

　　Affinity mask: Affinity mask讓你可以控制SQL Server使用哪個(gè)處理器。對(duì)于大多數(shù)情況，你不應(yīng)該接觸這個(gè)設(shè)置，讓操作系統(tǒng)控制處理器關(guān)系。然而，你也許想要用這個(gè)選項(xiàng)來(lái)將某個(gè)處理器專門用于另一 個(gè)進(jìn)程(例如，MSSearch 或者 SQL Server磁盤 IO ，以及 SQL Server的平衡)。參考基礎(chǔ)知識(shí)資源獲取更多有關(guān)這個(gè)設(shè)置的信息。

　　Awe enabled: Awe的啟動(dòng)可以讓SQL Server Enterprise版本運(yùn)行在Windows 2000以及以上高級(jí)服務(wù)器上，或者Windows 2003 Enterprise以及以上的版本使用超過(guò)4GB的內(nèi)存。如果你的服務(wù)器符合這些條件的話，就激活這個(gè)設(shè)置吧。

　　并行成本極限:當(dāng)查詢需要進(jìn)行并行處理的時(shí)候，并行的成本極限就定下來(lái)了。默認(rèn)情況是五秒鐘。將這個(gè)數(shù)值改為稍低的數(shù)值，俄可以讓更多個(gè)查詢獲得并行處理，但是這也會(huì)引起CPU瓶頸。這個(gè)設(shè)置只有在多個(gè)處理器的機(jī)器上才會(huì)起作用。

　　填充因子:填充因子設(shè)置了在創(chuàng)建聚簇索引的時(shí)候用來(lái)自動(dòng)填充的因子。在頻繁插入的表中，將數(shù)值從默認(rèn)的90%設(shè)置為較低的數(shù)值，你會(huì)獲得收益。

　　輕量級(jí)緩沖池:這個(gè)設(shè)置啟動(dòng)了光纖模式。使用這個(gè)選項(xiàng)在CPU利用率很高的8路及其以上的服務(wù)器上。這可以讓光纖同時(shí)為每個(gè)線程提供服務(wù)，同時(shí)在默認(rèn)情況下運(yùn)行在每個(gè)處理器上。某些任務(wù)可以從這些光纖中獲得優(yōu)勢(shì)。

　　并行的最大程度:當(dāng)服務(wù)器可以使用并行或者不能使用并行，或者是當(dāng)某個(gè)數(shù)量的處理器可以用于并行操作的時(shí)候，這個(gè)設(shè)置就確定了。并行就是多個(gè)處理器上發(fā)生多個(gè)處理。例如，查詢的并行操作可以在不同的處理器上同時(shí)處理。

　　服務(wù)器最大內(nèi)存(MB):如果你在SQL Server上運(yùn)行了其他的處理，并且有足夠的內(nèi)存，那么你有可能想要留出512MB的內(nèi)存給操作系統(tǒng)和這些進(jìn)程。例如，你可以在MSSearch或者在本地運(yùn)行大量的代理的情況下將其設(shè)置為512。
最大工作線程:最大工作線程設(shè)置與ADO.net中的連接池有些類似。通過(guò)這個(gè)設(shè)置，任何超過(guò)限制(255個(gè)用戶)的用戶連接都可以在線程池中等待，直到 為某個(gè)連接服務(wù)的線程得到釋放，就好像是ADO.net中的連接與連接池共享。如果你有很大量的連接，并且大量的內(nèi)存，那么你就可以提高這個(gè)數(shù)值。

　　網(wǎng)絡(luò)包尺寸(B):這個(gè)設(shè)置控制了網(wǎng)絡(luò)中傳輸?shù)侥愕目蛻舳说陌某叽纭Ｔ谟袚p耗的網(wǎng)絡(luò)中(例如電話線)，你可能想要將這個(gè)參數(shù)設(shè)置為比較低的數(shù)值，墨人數(shù)值是4096。在連接良好的網(wǎng)絡(luò)中，你可以提高這個(gè)設(shè)置，特別是涉及BLOB的大型批處理操作。

　　優(yōu)先推進(jìn):這個(gè)設(shè)置為SQL Server提供了處理器的推動(dòng)。在任務(wù)管理器中，點(diǎn)擊進(jìn)程標(biāo)簽，定位SQL Server的位置，然后右擊它。選擇“設(shè)置優(yōu)先級(jí)別”。注意，SQL Server應(yīng)該運(yùn)行在正常的優(yōu)先級(jí)別上。輸入如下命令:

?

　　Sp_configure?'priority?boost',?1

　　Reconfigure?with?override

?

　　然后重新啟動(dòng)你的SQL Server。在任務(wù)管理器中察看SQL Server現(xiàn)在運(yùn)行在什么優(yōu)先級(jí)別上。它應(yīng)該是在高優(yōu)先級(jí)上。SQL Server應(yīng)該比其他的用戶進(jìn)程運(yùn)行優(yōu)先級(jí)別要高。在專用于SQL Server的服務(wù)器上使用這個(gè)設(shè)置。

總結(jié)

　　本篇討論了最常見(jiàn)的SQL Server優(yōu)化設(shè)置。在做出改變之前和之后分別在測(cè)試環(huán)境中進(jìn)行基線確定是非常重要的，可以據(jù)此來(lái)評(píng)估在典型的負(fù)載下，改變對(duì)你的系統(tǒng)的影響。
SQL Server 數(shù)據(jù)庫(kù)中關(guān)于死鎖的分析

SQL Server數(shù)據(jù)庫(kù)發(fā)生死鎖時(shí)不會(huì)像ORACLE那樣自動(dòng)生成一個(gè)跟蹤文件。有時(shí)可以在[管理]->[當(dāng)前活動(dòng)] 里看到阻塞信息(有時(shí)SQL Server企業(yè)管理器會(huì)因?yàn)殒i太多而沒(méi)有響應(yīng)).

設(shè)定跟蹤1204:

?

USE?MASTER

DBCC?TRACEON?(1204,-1)

?

顯示當(dāng)前啟用的所有跟蹤標(biāo)記的狀態(tài):

DBCC TRACESTATUS(-1)

取消跟蹤1204:

DBCC TRACEOFF (1204,-1)

在設(shè)定跟蹤1204后,會(huì)在數(shù)據(jù)庫(kù)的日志文件里顯示SQL Server數(shù)據(jù)庫(kù)死鎖時(shí)一些信息。但那些信息很難看懂,需要對(duì)照SQL Server聯(lián)機(jī)叢書仔細(xì)來(lái)看。根據(jù)PAG鎖要找到相關(guān)數(shù)據(jù)庫(kù)表的方法:

DBCC TRACEON (3604)
DBCC PAGE (db_id,file_id,page_no)
DBCC TRACEOFF (3604)

請(qǐng)參考sqlservercentral.com上更詳細(xì)的講解.但又從CSDN學(xué)到了一個(gè)找到死鎖原因的方法。我稍加修改, 去掉了游標(biāo)操作并增加了一些提示信息,寫了一個(gè)系統(tǒng)存儲(chǔ)過(guò)程sp_who_lock.sql。代碼如下：

?

if?exists?(select?*?from?dbo.sysobjects
where?id?=?object_id(N'[dbo].[sp_who_lock]')
and?OBJECTPROPERTY(id,?N'IsProcedure')?=?1)
drop?procedure?[dbo].[sp_who_lock]
GO
/********************************************************
//??創(chuàng)建?:?fengyu??郵件?:?maggiefengyu@tom.com
//??日期?:2004-04-30
//??修改?:?從http://www.csdn.net/develop/Read_Article.asp?id=26566
//??學(xué)習(xí)到并改寫
//??說(shuō)明?:?查看數(shù)據(jù)庫(kù)里阻塞和死鎖情況
********************************************************/
use?master
go
create?procedure?sp_who_lock
as
begin
declare?@spid?int,@bl?int,
@intTransactionCountOnEntry?????int,
@intRowcount?????????????int,
@intCountProperties?????????int,
@intCounter?????????????int
create?table?#tmp_lock_who?(
id?int?identity(1,1),
spid?smallint,
bl?smallint)
IF?@@ERROR<>0?RETURN?@@ERROR
insert?into?#tmp_lock_who(spid,bl)?select??0?,blocked
from?(select?*?from?sysprocesses?where??blocked>0?)?a
where?not?exists(select?*?from?(select?*?from?sysprocesses
where??blocked>0?)?b
where?a.blocked=spid)
union?select?spid,blocked?from?sysprocesses?where??blocked>0
IF?@@ERROR<>0?RETURN?@@ERROR
--?找到臨時(shí)表的記錄數(shù)
select?????@intCountProperties?=?Count(*),@intCounter?=?1
from?#tmp_lock_who
IF?@@ERROR<>0?RETURN?@@ERROR
if????@intCountProperties=0
select?'現(xiàn)在沒(méi)有阻塞和死鎖信息'?as?message
--?循環(huán)開(kāi)始
while?@intCounter?<=?@intCountProperties
begin
--?取第一條記錄
select?????@spid?=?spid,@bl?=?bl
from?#tmp_lock_who?where?Id?=?@intCounter
begin
if?@spid?=0
select?'引起數(shù)據(jù)庫(kù)死鎖的是:?'+?CAST(@bl?AS?VARCHAR(10))
+?'進(jìn)程號(hào),其執(zhí)行的SQL語(yǔ)法如下'
else
select?'進(jìn)程號(hào)SPID：'+?CAST(@spid?AS?VARCHAR(10))+?'被'
+?'進(jìn)程號(hào)SPID：'+?CAST(@bl?AS?VARCHAR(10))?+'阻塞,其當(dāng)前進(jìn)程執(zhí)行的SQL語(yǔ)法如下'
DBCC?INPUTBUFFER?(@bl?)
end
--?循環(huán)指針下移
set?@intCounter?=?@intCounter?+?1
end
drop?table?#tmp_lock_who
return?0
end

需要的時(shí)候直接調(diào)用:

sp_who_lock

就可以查出引起死鎖的進(jìn)程和SQL語(yǔ)句.

SQL Server自帶的系統(tǒng)存儲(chǔ)過(guò)程sp_who和sp_lock也可以用來(lái)查找阻塞和死鎖, 但沒(méi)有這里介紹的方法好用。如果想知道其它tracenum參數(shù)的含義,請(qǐng)看www.sqlservercentral.com文章

我們還可以設(shè)置鎖的超時(shí)時(shí)間(單位是毫秒), 來(lái)縮短死鎖可能影響的時(shí)間范圍:

例如:

?

use?master
seelct?@@lock_timeout
set?lock_timeout?900000
--?15分鐘
seelct?@@lock_timeout

?

優(yōu)化SQLServer索引的小技巧
SQL Server中有幾個(gè)可以讓你檢測(cè)、調(diào)整和優(yōu)化SQL Server性能的工具。在本文中，我將說(shuō)明如何用SQL Server的工具來(lái)優(yōu)化數(shù)據(jù)庫(kù)索引的使用，本文還涉及到有關(guān)索引的一般性知識(shí)。

關(guān)于索引的常識(shí)

影響到數(shù)據(jù)庫(kù)性能的最大因素就是索引。由于該問(wèn)題的復(fù)雜性，我只可能簡(jiǎn)單的談?wù)勥@個(gè)問(wèn)題，不過(guò)關(guān)于這方面的問(wèn)題，目前有好幾本不錯(cuò)的書籍可供你參 閱。我在這里只討論兩種SQL Server索引，即clustered索引和nonclustered索引。當(dāng)考察建立什么類型的索引時(shí)，你應(yīng)當(dāng)考慮數(shù)據(jù)類型和保存這些數(shù)據(jù)的 column。同樣，你也必須考慮數(shù)據(jù)庫(kù)可能用到的查詢類型以及使用的最為頻繁的查詢類型。

索引的類型

如果column保存了高度相關(guān)的數(shù)據(jù)，并且常常被順序訪問(wèn)時(shí)，最好使用clustered索引，這是因?yàn)槿绻褂胏lustered索引，SQL Server會(huì)在物理上按升序（默認(rèn)）或者降序重排數(shù)據(jù)列，這樣就可以迅速的找到被查詢的數(shù)據(jù)。同樣，在搜尋控制在一定范圍內(nèi)的情況下，對(duì)這些 column也最好使用clustered索引。這是因?yàn)橛捎谖锢砩现嘏艛?shù)據(jù)，每個(gè)表格上只有一個(gè)clustered索引。

與上面情況相反，如果columns包含的數(shù)據(jù)相關(guān)性較差，你可以使用nonculstered索引。你可以在一個(gè)表格中使用高達(dá)249個(gè)nonclustered索引--盡管我想象不出實(shí)際應(yīng)用場(chǎng)合會(huì)用的上這么多索引。

當(dāng)表格使用主關(guān)鍵字（primary keys），默認(rèn)情況下SQL Server會(huì)自動(dòng)對(duì)包含該關(guān)鍵字的column(s)建立一個(gè)獨(dú)有的cluster索引。很顯然，對(duì)這些column(s)建立獨(dú)有索引意味著主關(guān)鍵字 的唯一性。當(dāng)建立外關(guān)鍵字（foreign key）關(guān)系時(shí)，如果你打算頻繁使用它，那么在外關(guān)鍵字cloumn上建立nonclustered索引不失為一個(gè)好的方法。如果表格有 clustered索引，那么它用一個(gè)鏈表來(lái)維護(hù)數(shù)據(jù)頁(yè)之間的關(guān)系。相反，如果表格沒(méi)有clustered索引，SQL Server將在一個(gè)堆棧中保存數(shù)據(jù)頁(yè)。

數(shù)據(jù)頁(yè)

當(dāng)索引建立起來(lái)的時(shí)候，SQLServer就建立數(shù)據(jù)頁(yè)（datapage），數(shù)據(jù)頁(yè)是用以加速搜索的指針。當(dāng)索引建立起來(lái)的時(shí)候，其對(duì)應(yīng)的填充因 子也即被設(shè)置。設(shè)置填充因子的目的是為了指示該索引中數(shù)據(jù)頁(yè)的百分比。隨著時(shí)間的推移，數(shù)據(jù)庫(kù)的更新會(huì)消耗掉已有的空閑空間，這就會(huì)導(dǎo)致頁(yè)被拆分。頁(yè)拆分 的后果是降低了索引的性能，因而使用該索引的查詢會(huì)導(dǎo)致數(shù)據(jù)存儲(chǔ)的支離破碎。當(dāng)建立一個(gè)索引時(shí)，該索引的填充因子即被設(shè)置好了，因此填充因子不能動(dòng)態(tài)維 護(hù)。

為了更新數(shù)據(jù)頁(yè)中的填充因子，我們可以停止舊有索引并重建索引，并重新設(shè)置填充因子（注意：這將影響到當(dāng)前數(shù)據(jù)庫(kù)的運(yùn)行，在重要場(chǎng)合請(qǐng)謹(jǐn)慎使用）。 DBCC INDEXDEFRAG和DBCC DBREINDEX是清除clustered和nonculstered索引碎片的兩個(gè)命令。INDEXDEFRAG是一種在線操作（也就是說(shuō)，它不會(huì)阻 塞其它表格動(dòng)作，如查詢），而DBREINDEX則在物理上重建索引。在絕大多數(shù)情況下，重建索引可以更好的消除碎片，但是這個(gè)優(yōu)點(diǎn)是以阻塞當(dāng)前發(fā)生在該 索引所在表格上其它動(dòng)作為代價(jià)換取來(lái)得。當(dāng)出現(xiàn)較大的碎片索引時(shí)，INDEXDEFRAG會(huì)花上一段比較長(zhǎng)的時(shí)間，這是因?yàn)樵撁畹倪\(yùn)行是基于小的交互塊 （transactional block）。

填充因子

當(dāng)你執(zhí)行上述措施中的任何一個(gè)，數(shù)據(jù)庫(kù)引擎可以更有效的返回編入索引的數(shù)據(jù)。關(guān)于填充因子（fillfactor）話題已經(jīng)超出了本文的范疇，不過(guò)我還是提醒你需要注意那些打算使用填充因子建立索引的表格。

在執(zhí)行查詢時(shí)，SQL Server動(dòng)態(tài)選擇使用哪個(gè)索引。為此，SQL Server根據(jù)每個(gè)索引上分布在該關(guān)鍵字上的統(tǒng)計(jì)量來(lái)決定使用哪個(gè)索引。值得注意的是，經(jīng)過(guò)日常的數(shù)據(jù)庫(kù)活動(dòng)（如插入、刪除和更新表格），SQL Server用到的這些統(tǒng)計(jì)量可能已經(jīng)“過(guò)期”了，需要更新。你可以通過(guò)執(zhí)行DBCC SHOWCONTIG來(lái)查看統(tǒng)計(jì)量的狀態(tài)。當(dāng)你認(rèn)為統(tǒng)計(jì)量已經(jīng)“過(guò)期”時(shí)，你可以執(zhí)行該表格的UPDATE STATISTICS命令，這樣SQL Server就刷新了關(guān)于該索引的信息了。

建立數(shù)據(jù)庫(kù)維護(hù)計(jì)劃

SQL Server提供了一種簡(jiǎn)化并自動(dòng)維護(hù)數(shù)據(jù)庫(kù)的工具。這個(gè)稱之為數(shù)據(jù)庫(kù)維護(hù)計(jì)劃向?qū)В―atabase Maintenance Plan Wizard ，DMPW）的工具也包括了對(duì)索引的優(yōu)化。如果你運(yùn)行這個(gè)向?qū)В銜?huì)看到關(guān)于數(shù)據(jù)庫(kù)中關(guān)于索引的統(tǒng)計(jì)量，這些統(tǒng)計(jì)量作為日志工作并定時(shí)更新，這樣就減輕了 手工重建索引所帶來(lái)的工作量。如果你不想自動(dòng)定期刷新索引統(tǒng)計(jì)量，你還可以在DMPW中選擇重新組織數(shù)據(jù)和數(shù)據(jù)頁(yè)，這將停止舊有索引并按特定的填充因子重 建索引。
Sybase SQL Server索引的使用和優(yōu)化 ?
　　在應(yīng)用系統(tǒng)中,尤其在聯(lián)機(jī)事務(wù)處理系統(tǒng)中,對(duì)數(shù)據(jù)查詢及處理速度已成為衡 量應(yīng)用系統(tǒng)成敗的標(biāo)準(zhǔn)。而采用索引來(lái)加快數(shù)據(jù)處理速度也成為廣大數(shù)據(jù)庫(kù)用戶所 接受的優(yōu)化方法。

　　在良好的數(shù)據(jù)庫(kù)設(shè)計(jì)基礎(chǔ)上，能有效地使用索引是SQL Server取得高性能的基礎(chǔ)，SQL Server采用基于代價(jià)的優(yōu)化模型，它對(duì)每一個(gè)提交的有關(guān)表的查詢，決定是否使用索引或用哪一個(gè)索引。因?yàn)椴樵儓?zhí)行的大部分開(kāi)銷是磁盤I/O，使用索引 提高性能的一個(gè)主要目標(biāo)是避免全表掃描，因?yàn)槿頀呙栊枰獜拇疟P上讀表的每一個(gè)數(shù)據(jù)頁(yè)，如果有索引指向數(shù)據(jù)值，則查詢只需讀幾次磁盤就可以了。所以如果建 立了合理的索引，優(yōu)化器就能利用索引加速數(shù)據(jù)的查詢過(guò)程。但是，索引并不總是提高系統(tǒng)的性能，在增、刪、改操作中索引的存在會(huì)增加一定的工作量，因此，在 適當(dāng)?shù)牡胤皆黾舆m當(dāng)?shù)乃饕牟缓侠淼牡胤絼h除次優(yōu)的索引，將有助于優(yōu)化那些性能較差的SQL Server應(yīng)用。實(shí)踐表明，合理的索引設(shè)計(jì)是建立在對(duì)各種查詢的分析和預(yù)測(cè)上的，只有正確地使索引與程序結(jié)合起來(lái),才能產(chǎn)生最佳的優(yōu)化方案。本文就 SQL Server索引的性能問(wèn)題進(jìn)行了一些分析和實(shí)踐。

　　一、聚簇索引(clustered indexes)的使用

　　聚簇索引是一種對(duì)磁盤上實(shí)際數(shù)據(jù)重新組織以按指定的一個(gè)或多個(gè)列的值排序。由于聚簇索引的索引頁(yè)面指針指向數(shù)據(jù)頁(yè)面，所以使用聚簇索引查找數(shù)據(jù) 幾乎總是比使用非聚簇索引快。每張表只能建一個(gè)聚簇索引，并且建聚簇索引需要至少相當(dāng)該表120%的附加空間，以存放該表的副本和索引中間頁(yè)。建立聚簇索 引的思想是：

　　1、 大多數(shù)表都應(yīng)該有聚簇索引或使用分區(qū)來(lái)降低對(duì)表尾頁(yè)的競(jìng)爭(zhēng)，在一個(gè)高事務(wù)的環(huán)境中，對(duì)最后一頁(yè)的封鎖嚴(yán)重影響系統(tǒng)的吞吐量。

　　2、在聚簇索引下，數(shù)據(jù)在物理上按順序排在數(shù)據(jù)頁(yè)上，重復(fù)值也排在一起，因而在那些包含范圍檢查(between、<、<=、& gt;、> =)或使用group by或order by的查詢時(shí)，一旦找到具有范圍中第一個(gè)鍵值的行，具有后續(xù)索引值的行保證物理上毗連在一起而不必進(jìn)一步搜索，避免了大范圍掃描，可以大大提高查詢速度。

　　3、 在一個(gè)頻繁發(fā)生插入操作的表上建立聚簇索引時(shí)，不要建在具有單調(diào)上升值的列(如IDENTITY)上，否則會(huì)經(jīng)常引起封鎖沖突。

　　4、 在聚簇索引中不要包含經(jīng)常修改的列，因?yàn)榇a值修改后，數(shù)據(jù)行必須移動(dòng)到新的位置。

　　5、 選擇聚簇索引應(yīng)基于where子句和連接操作的類型。聚簇索引的侯選列是：

　　● 主鍵列,該列在where子句中使用并且插入是隨機(jī)的。

　　● 按范圍存取的列，如pri_order > 100 and pri_order < 200 。

　　● 在group by或order by中使用的列。

　　● 不經(jīng)常修改的列。

　　● 在連接操作中使用的列。

　　二、非聚簇索引(nonclustered indexes)的使用

　　SQL Server缺省情況下建立的索引是非聚簇索引，由于非聚簇索引不重新組織表中的數(shù)據(jù)，而是對(duì)每一行存儲(chǔ)索引列值并用一個(gè)指針指向數(shù)據(jù)所在的頁(yè)面。換句話 說(shuō)非聚簇索引具有在索引結(jié)構(gòu)和數(shù)據(jù)本身之間的一個(gè)額外級(jí)。一個(gè)表如果沒(méi)有聚簇索引時(shí),可有250個(gè)非聚簇索引。每個(gè)非聚簇索引提供訪問(wèn)數(shù)據(jù)的不同排序順 序。在建立非聚簇索引時(shí)，要權(quán)衡索引對(duì)查詢速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問(wèn)題：

　　● 索引需要使用多少空間。

　　● 合適的列是否穩(wěn)定。

　　● 索引鍵是如何選擇的，掃描效果是否更佳。

　　● 是否有許多重復(fù)值。

　　對(duì)更新頻繁的表來(lái)說(shuō)，表上的非聚簇索引比聚簇索引和根本沒(méi)有索引需要更多的額外開(kāi)銷。對(duì)移到新頁(yè)的每一行而言，指向該數(shù)據(jù)的每個(gè)非聚簇索引的頁(yè) 級(jí)行也必須更新，有時(shí)可能還需要索引頁(yè)的分理。從一個(gè)頁(yè)面刪除數(shù)據(jù)的進(jìn)程也會(huì)有類似的開(kāi)銷，另外，刪除進(jìn)程還必須把數(shù)據(jù)移到頁(yè)面上部，以保證數(shù)據(jù)的連續(xù) 性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情況:

　　● 某列常用于集合函數(shù)(如Sum,....)。

　　● 某列常用于join,order by,group by。

　　● 查尋出的數(shù)據(jù)不超過(guò)表中數(shù)據(jù)量的20%。

　　三、覆蓋索引(covering indexes)的使用

　　覆蓋索引是指那些索引項(xiàng)中包含查尋所需要的全部信息的非聚簇索引，這 種索引之所以比較快也正是因?yàn)樗饕?yè)中包含了查尋所必須的數(shù)據(jù),不需去訪 問(wèn)數(shù)據(jù)頁(yè)。 如果非聚簇索引中包含結(jié)果數(shù)據(jù),那么它的查詢速度將快于聚簇索引。

　　但是由于覆蓋索引的索引項(xiàng)比較多,要占用比較大的空間。而且update 操 作會(huì)引起索引值改變。所以如果潛在的覆蓋查詢并不常用或不太關(guān)鍵，則覆蓋索引的增加反而會(huì)降低性能。

　　四、索引的選擇技術(shù)

　　p_detail是住房公積金管理系統(tǒng)中記錄個(gè)人明細(xì)的表，有890000行，觀察在不同索引下的查詢運(yùn)行效果，測(cè)試在C/S環(huán)境下進(jìn)行，客戶 機(jī)是 IBM PII350(內(nèi)存64M),服務(wù)器是DEC Alpha1000A(內(nèi)存128M),數(shù)據(jù)庫(kù)為SYBASE11.0.3。

　　1、 select count(*) from p_detail where op_date>’19990101’ and op_date<’19991231’ and pri_surplus1>300

　　2、 select count(*),sum(pri_surplus1) from p_detail where op_date>’19990101’ and pay_month between ‘199908’ and ’199912’

　　不建任何索引 查詢1 1分15秒

　　查詢2 1分7秒

　　在op_date上建非聚簇索引 查詢1 57秒

　　查詢2 57秒

　　在op_date上建聚簇索引 查詢1 <1秒

　　查詢2 52秒

　　在pay_month、op_date、pri_surplus1上建索引 查詢1 34秒

　　查詢2 <1秒

　　在op_date、pay_month、pri_surplus1上建索引 查詢1 <1秒

　　查詢2 <1秒

　　從以上查詢效果分析，索引的有無(wú)，建立方式的不同將會(huì)導(dǎo)致不同的查詢效果，選擇什么樣的索引基于用戶對(duì)數(shù)據(jù)的查詢條件,這些條件體現(xiàn)于where從句和join表達(dá)式中。一般來(lái)說(shuō)建立索引的思路是：

　　(1)、主鍵時(shí)常作為where子句的條件，應(yīng)在表的主鍵列上建立聚簇索引，尤其當(dāng)經(jīng)常用它作為連接的時(shí)候。

　　(2)、有大量重復(fù)值且經(jīng)常有范圍查詢和排序、分組發(fā)生的列，或者非常頻繁地被訪問(wèn)的列，可考慮建立聚簇索引。

　　(3)、經(jīng)常同時(shí)存取多列，且每列都含有重復(fù)值可考慮建立復(fù)合索引來(lái)覆蓋一個(gè)或一組查詢，并把查詢引用最頻繁的列作為前導(dǎo)列，如果可能盡量使關(guān)鍵查詢形成覆蓋查詢。

　　(4)、如果知道索引鍵的所有值都是唯一的，那么確保把索引定義成唯一索引。

　　(5)、在一個(gè)經(jīng)常做插入操作的表上建索引時(shí)，使用fillfactor(填充因子)來(lái)減少頁(yè)分裂，同時(shí)提高并發(fā)度降低死鎖的發(fā)生。如果在只讀表上建索引，則可以把fillfactor置為100。

　　(6)、在選擇索引鍵時(shí)，設(shè)法選擇那些采用小數(shù)據(jù)類型的列作為鍵以使每個(gè)索

　　引頁(yè)能夠容納盡可能多的索引鍵和指針，通過(guò)這種方式，可使一個(gè)查詢必須遍歷的索引頁(yè)面降到最小。此外，盡可能地使用整數(shù)為鍵值，因?yàn)樗軌蛱峁┍热魏螖?shù)據(jù)類型都快的訪問(wèn)速度。

　　五、索引的維護(hù)

　　上面講到,某些不合適的索引影響到SQL Server的性能,隨著應(yīng)用系統(tǒng)的運(yùn)行,數(shù)據(jù)不斷地發(fā)生變化,當(dāng)數(shù)據(jù)變化達(dá)到某一個(gè)程度時(shí)將 會(huì)影響到索引的使用。這時(shí) 需要用戶自己來(lái)維護(hù)索引。索引的維護(hù)包括：

　　1、重建索引

　　隨著數(shù)據(jù)行的插入、刪除和數(shù)據(jù)頁(yè)的分裂，有些索引頁(yè)可能只包含幾頁(yè)數(shù)據(jù)，另外應(yīng)用在執(zhí)行大塊I/O的時(shí)候，重建非聚簇索引可以降低分片，維護(hù)大塊I/O的效率。重建索引實(shí)際上是重新組織B-樹(shù)空間。在下面情況下需要重建索引：

　　(1)、數(shù)據(jù)和使用模式大幅度變化。

　　(2)、排序的順序發(fā)生改變。

　　(3)、要進(jìn)行大量插入操作或已經(jīng)完成。

　　(4)、使用大塊I/O的查詢的磁盤讀次數(shù)比預(yù)料的要多。

　　(5)、由于大量數(shù)據(jù)修改，使得數(shù)據(jù)頁(yè)和索引頁(yè)沒(méi)有充分使用而導(dǎo)致空間的使用超出估算。

　　(6)、dbcc檢查出索引有問(wèn)題。

　　當(dāng)重建聚簇索引時(shí),這張表的所有非聚簇索引將被重

　　建.

　　2、索引統(tǒng)計(jì)信息的更新

　　當(dāng)在一個(gè)包含數(shù)據(jù)的表上創(chuàng)建索引的時(shí)候，SQL Server會(huì)創(chuàng)建分布數(shù)據(jù)頁(yè)來(lái)存放有關(guān)索引的兩種統(tǒng)計(jì)信息：分布表和密度表。優(yōu)化器利用這個(gè)頁(yè)來(lái)判斷該索引對(duì)某個(gè)特定查詢是否有用。但這個(gè)統(tǒng)計(jì)信息并不 動(dòng)態(tài)地重新計(jì)算。這意味著，當(dāng)表的數(shù)據(jù)改變之后，統(tǒng)計(jì)信息有可能是過(guò)時(shí)的，從而影響優(yōu)化器追求最有工作的目標(biāo)。因此，在下面情況下應(yīng)該運(yùn)行update statistics命令：

　　(1)、數(shù)據(jù)行的插入和刪除修改了數(shù)據(jù)的分布。

　　(2)、對(duì)用truncate table刪除數(shù)據(jù)的表上增加數(shù)據(jù)行。

　　(3)、修改索引列的值。

　　六、結(jié)束語(yǔ)

　　實(shí)踐表明，不恰當(dāng)?shù)乃饕坏谑聼o(wú)補(bǔ)，反而會(huì)降低系統(tǒng)的執(zhí)行性能。因?yàn)榇罅康乃饕诓迦搿⑿薷暮蛣h除操作時(shí)比沒(méi)有索引花費(fèi)更多的系統(tǒng)時(shí)間。例如下面情況下建立的索引是不恰當(dāng)?shù)模?

　　● 在查詢中很少或從不引用的列不會(huì)受益于索引，因?yàn)樗饕苌倩驈膩?lái)不必搜索基于這些列的行。

　　● 只有兩個(gè)或三個(gè)值的列，如男性和女性(是或否)，從不會(huì)從索引中得到好處。

　　另外，鑒于索引加快了查詢速度，但減慢了數(shù)據(jù)更新速度的特點(diǎn)。可通過(guò)在一個(gè)段上建表，而在另一個(gè)段上建其非聚簇索引，而這兩段分別在單獨(dú)的物理設(shè)備上來(lái)改善操作性能

50種方法優(yōu)化SQL Server數(shù)據(jù)庫(kù)查詢