識(shí)別SQL Server 性能殺手

性能優(yōu)化的重點(diǎn)在于識(shí)別定位問(wèn)題，預(yù)先了解主要的性能殺手，能夠更快的定位到問(wèn)題并將工作集中在可能的原因之上。

SQL SERVER性能殺手主要集中在如下幾類：

1.1 低質(zhì)量的索引

低質(zhì)量的索引通常是SQL SERVER最大的性能殺手，對(duì)于一個(gè)缺乏索引的查詢，SQL SERVER 需要處理大量的讀取和計(jì)算；這樣導(dǎo)致磁盤(pán)、內(nèi)存、CUP上有很大的開(kāi)銷，并且會(huì)顯著的增加了查詢執(zhí)行時(shí)間。

1.2 不精確的統(tǒng)計(jì)信息

統(tǒng)計(jì)信息是謂詞引用的列中的數(shù)據(jù)分布，其存儲(chǔ)的方式為柱狀圖；柱狀圖是顯示數(shù)據(jù)分布于不同分類中頻度的一種統(tǒng)計(jì)結(jié)構(gòu)。索引的有效性完全取決于索引列的統(tǒng)計(jì)信息，如有沒(méi)有統(tǒng)計(jì)信息，SQL SERVER 內(nèi)建的查詢優(yōu)化器就不能精確的估計(jì)查詢影響的行數(shù)，此時(shí)查詢優(yōu)化器就非常的低效。

1.3 過(guò)多的阻塞與死鎖

SQL SERVER 完全兼用于原子性、一致性、隔離性、永久性，所以數(shù)據(jù)庫(kù)引擎會(huì)確保并發(fā)事務(wù)被正確的互相隔離。默認(rèn)情況下，一個(gè)事務(wù)所看見(jiàn)的數(shù)據(jù)是另一個(gè)事務(wù)修改之前或者修改之后的狀態(tài)—它不會(huì)看到中間狀態(tài)。

    因?yàn)檫@種隔離性，當(dāng)多個(gè)事務(wù)以一種兼容的方法并發(fā)訪問(wèn)公用資源時(shí)，數(shù)據(jù)庫(kù)中會(huì)發(fā)生阻塞。當(dāng)兩個(gè)資源嘗試升級(jí)或擴(kuò)展加鎖的資源并且與另一個(gè)沖突時(shí)，就會(huì)發(fā)生死鎖。查詢引擎確定回滾開(kāi)銷最低的進(jìn)程并選擇其為死鎖犧牲品。犧牲品需要再次提交請(qǐng)求才能正常執(zhí)行完成。這就導(dǎo)致開(kāi)銷時(shí)間較長(zhǎng)。

1.4 不基于數(shù)據(jù)集的操作

T-SQL是一種數(shù)據(jù)集的腳本語(yǔ)言，操作數(shù)據(jù)是在數(shù)據(jù)集上進(jìn)行。這需要我們從數(shù)據(jù)列上考慮問(wèn)題而不是從數(shù)據(jù)行上思考問(wèn)題。避免在操作中使用游標(biāo)和循環(huán)，而是需要多使用連接于子查詢。

1.5 低質(zhì)量的查詢?cè)O(shè)計(jì)

索引的有效性取決于編寫(xiě)的SQL 查詢語(yǔ)句；如果SQL從一個(gè)表中讀取了過(guò)多的行或者指定的過(guò)濾條件返回了超過(guò)所需要的大結(jié)果集，都將使索引變得無(wú)效。為了能更好的使用索引，必須編寫(xiě)高質(zhì)量的SQL查詢語(yǔ)句并做到按需取數(shù)。

1.6 低質(zhì)量的數(shù)據(jù)庫(kù)設(shè)計(jì)

    數(shù)據(jù)庫(kù)應(yīng)該合理的規(guī)范化以增進(jìn)數(shù)據(jù)庫(kù)檢索的性能并減少阻塞。一個(gè)不合理的設(shè)計(jì)會(huì)導(dǎo)致數(shù)據(jù)的重復(fù)存儲(chǔ)，一個(gè)過(guò)渡規(guī)范化的數(shù)據(jù)庫(kù)會(huì)導(dǎo)致讀取數(shù)據(jù)所需的連接非常多；一個(gè)合理規(guī)范化的數(shù)據(jù)庫(kù)是高質(zhì)量查詢的基石。

1.7 過(guò)多的碎片

    數(shù)據(jù)存儲(chǔ)的基本單位是頁(yè)，由于頻繁的頁(yè)分割使得頁(yè)中包括了無(wú)法存儲(chǔ)數(shù)據(jù)的

空白區(qū)域稱為碎片；碎片會(huì)引起讀操作次數(shù)的增加而影響性能（一次讀取的是一個(gè)頁(yè)）。

1.1 不可重用的執(zhí)行計(jì)劃

為了有效的執(zhí)行查詢，SQL SERVER 會(huì)在編譯的時(shí)候生成一個(gè)優(yōu)化的執(zhí)行計(jì)劃，該執(zhí)行計(jì)劃會(huì)緩存在內(nèi)存中，因而其可以重用。但是如果該查詢?cè)O(shè)計(jì)為不能插入變量值，相同的查詢以不同的變量值重新提交時(shí)，SQL SERVER會(huì)重新生成新的執(zhí)行計(jì)劃，這個(gè)過(guò)程會(huì)耗掉一線性能。因此SQL SERVER 緩存或重用執(zhí)行計(jì)劃的方式提交SQL查詢會(huì)對(duì)性能有一定的優(yōu)化。

1.2 低質(zhì)量的執(zhí)行計(jì)劃

    一個(gè)不好的執(zhí)行計(jì)劃有時(shí)可能是一個(gè)真正的殺手，不好的計(jì)劃常常是由被稱為參數(shù)嗅探的進(jìn)程造成的，這個(gè)進(jìn)程來(lái)自于查詢優(yōu)化器用于根據(jù)統(tǒng)計(jì)確定最佳計(jì)劃的過(guò)程。理解統(tǒng)計(jì)信息是很重要的。

1.3 頻繁重編譯計(jì)劃

存儲(chǔ)過(guò)程的重新編譯會(huì)導(dǎo)致執(zhí)行計(jì)劃的重復(fù)生成，該過(guò)程很耗費(fèi)性能，所以一般情況下不要將存儲(chǔ)過(guò)程設(shè)計(jì)為重編譯。

1.4 游標(biāo)的錯(cuò)誤使用

     游標(biāo)是一種非集合的操作，會(huì)給SQL SERVER 增加大量的開(kāi)銷；盡可能使用基于數(shù)據(jù)集的操作。

1.5 錯(cuò)誤配置數(shù)據(jù)庫(kù)日志

     為了達(dá)到最佳的性能，SQL SERVER 很大程度上依賴于對(duì)數(shù)據(jù)庫(kù)日志的高效訪問(wèn)。因此合理的日志配置方法也很重要。

1.6 過(guò)多使用或者錯(cuò)誤配置TEMPDB

     每一個(gè)SQL SERVER 實(shí)例都只有一個(gè)tempdb,因?yàn)樯婕坝脩魧?duì)象（如臨時(shí)表與表變量）、系統(tǒng)對(duì)象（如游標(biāo)或用于連接的hash表）的操作，以及排序和行版本控制等操作都使用tempdb數(shù)據(jù)庫(kù)，所以tempdb有時(shí)候也可能成為一個(gè)瓶頸。所有這些操作和其他可能使用的操作都可能導(dǎo)致tempdb中的空間、I/0和爭(zhēng)用問(wèn)題。因此tempdb的正確配置對(duì)于查詢性能也有較大的影響。