原文: SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查詢性能優(yōu)化中的作用

近段時(shí)間以來，一直在探究SQL Server查詢性能的問題，當(dāng)然也漫無目的的查找了很多資料，也從網(wǎng)上的大神們的文章中學(xué)到了很多，在這里，向各位大神致敬。正是受大神們無私奉獻(xiàn)精神的影響，所以小弟也作為回報(bào)，分享一下關(guān)于SET STATISTICS IO和SET STATISTICS TIME這兩條T_SQL命令，在查詢優(yōu)化性能中的作用。

????? 首先我想說明一下這篇文章不是關(guān)于如何優(yōu)化SQL Server查詢性能的，因?yàn)殛P(guān)于這方面的內(nèi)容太多，太復(fù)雜。另外查看很多關(guān)于性能優(yōu)化（該文章中，指的是查詢性能）的資料的過程中，發(fā)現(xiàn)幾乎所有都是用執(zhí)行時(shí)間作為優(yōu)化指標(biāo)，但是用執(zhí)行時(shí)間作為性能優(yōu)劣的指標(biāo)并不是那么合適。當(dāng)然，我們優(yōu)化查詢語句的最終目的就是減少查詢時(shí)間。

????? 引起 查詢時(shí)間不準(zhǔn)確的原因，有以下兩個(gè)方面：

?????? 1.SQL Server會(huì)隨著服務(wù)器資源的變化，而進(jìn)行自我調(diào)節(jié)。

???????因?yàn)槲覀兺ǔy(cè)試的服務(wù)器和實(shí)際服務(wù)器的環(huán)境并不完全相同。例如，我們?cè)谝慌_(tái)負(fù)載很重的服務(wù)器上進(jìn)行反復(fù)的測(cè)試。你會(huì)發(fā)現(xiàn)每次的執(zhí)行的時(shí)間，并不相同，當(dāng)然差距并不大，但是這個(gè)差距足以讓我們的性能調(diào)節(jié)變得困難許多。當(dāng)然你也可以反復(fù)執(zhí)行求平均值，但是在負(fù)載很大的服務(wù)器上，你需要一種多么科學(xué)的標(biāo)準(zhǔn)來確定執(zhí)行時(shí)間的平均值呢？

????? 2.SQL Server所要讀取的數(shù)據(jù)，有沒有在緩沖區(qū)中。

????? 因?yàn)镾QL Server 每次讀取數(shù)據(jù)都必須從數(shù)據(jù)緩沖區(qū)中讀取，這個(gè)也叫邏輯讀。如果要讀的數(shù)據(jù)沒有在數(shù)據(jù)緩沖區(qū)中，就要從物理磁盤上讀取（物理讀）。

????? 以上兩個(gè)原因都會(huì)影響執(zhí)行查詢語句所用的時(shí)間。

????? 說了那么多，那我們?cè)撚檬裁醋鳛樾阅軆?yōu)化的標(biāo)準(zhǔn)呢？

????? 1.CPU的占用時(shí)間。

?????? 當(dāng)數(shù)據(jù)庫執(zhí)行查詢語句時(shí)，會(huì)用到很多服務(wù)器的資源。其中一種資源就是CPU的占用時(shí)間，如果數(shù)據(jù)庫沒有發(fā)生任何的改變，反復(fù)的運(yùn)行同一個(gè)查詢，CPU的占用時(shí)間都是十分接近的。

?????? 2.IO操作的次數(shù)。

??????? IO操作的指標(biāo)有很多，下面會(huì)比較詳細(xì)的描述。

?????? 通過上面的描述，我們知道，一個(gè)查詢所需要的CPU、IO資源越少，性能就會(huì)越好。如果我們按照這個(gè)標(biāo)準(zhǔn)來優(yōu)化查詢，那么就會(huì)很容易的判斷出你的優(yōu)化措施是降低了性能，還是提高了性能。想到了這，那么我們?cè)鯓硬拍芸吹轿业姆?wù)器資源使用情況呢？這個(gè)時(shí)候我們就想到了SET STATISTICS IO和SET STATISTICS TIME (之前的內(nèi)容就算是我賣關(guān)子了啊，嘿嘿)

??????? SET STATISTIC IO和SET STATISTIC TIME像很多T_SQL語句那樣屬于開關(guān)命令（自己起得名字，就是用ON和OFF打開和關(guān)閉）。缺省狀態(tài)下是關(guān)閉的。接下來我們就開始使用這兩個(gè)命令了，好期待。

??????? 在這個(gè)例子中，我們使用之前建好的Test數(shù)據(jù)庫,使用Person表

??????? （一）首先我們使用SET STATISTICS TIME

???????? 1.首先，為了使每次的執(zhí)行都在同一個(gè)起點(diǎn)上，我們使用下面的兩條命令，來清除SQL Server的數(shù)據(jù)和過程緩沖區(qū)，否則執(zhí)行的查詢結(jié)果就沒有可比性了。???

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

????????? 2.執(zhí)行SET STATISTIC TIME ON 打開CUP統(tǒng)計(jì)報(bào)表

???????? 這些準(zhǔn)備工作完成后，我們可以執(zhí)行下面查詢：

????????? select? * from Person where ID=50000

????????? 執(zhí)行完上述命令之后，你可以在消息選項(xiàng)卡中得到下面信息

SQL Server 分析和編譯時(shí)間: ?? CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 20 毫秒。

SQL Server 分析和編譯時(shí)間: ?? CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。

(1 行受影響)

?SQL Server 執(zhí)行時(shí)間: ?? CPU 時(shí)間 = 235 毫秒，占用時(shí)間 = 1508 毫秒。

下面我們來詳細(xì)的分析這些時(shí)間信息的含義。

?第一個(gè)“SQL Server 分析和編譯時(shí)間”指的是解析“select? * from Person where ID=50000”這條查詢語句，并將解析的結(jié)果放到過程緩沖區(qū)中，SQL Server使用的CPU運(yùn)行時(shí)間和總的時(shí)間。

第二個(gè)“SQL Server 分析和編譯時(shí)間”指的是從過程緩沖區(qū)中取出解析結(jié)果，并且執(zhí)行的時(shí)間。這個(gè)時(shí)間會(huì)很快。

如果接下來不清空緩沖區(qū)而直接運(yùn)行“select? * from Person where ID=50000”，你會(huì)發(fā)現(xiàn)SQL Server 分析和編譯時(shí)間都為0，因?yàn)镾QL Server這時(shí)，會(huì)直接使用緩沖區(qū)中的解析結(jié)果，因此就不需要編譯時(shí)間。

第三個(gè)“SQL Server執(zhí)行時(shí)間”將會(huì)是我們最感興趣的時(shí)間，這個(gè)時(shí)間是執(zhí)行這次查詢使用了多少CPU運(yùn)行時(shí)間和運(yùn)行查詢使用了多少時(shí)間。CPU運(yùn)行時(shí)間是對(duì)運(yùn)行查詢所需要的CPU資源的一種相對(duì)穩(wěn)定的測(cè)量方法，與CPU的忙閑程度沒有關(guān)系。但是，每次運(yùn)行查詢時(shí)這一數(shù)字也會(huì)有所不同，只是變化的范圍很小。總時(shí)間是對(duì)查詢執(zhí)行所需要的時(shí)間（不計(jì)算阻塞或讀數(shù)據(jù)的時(shí)間），由于服務(wù)器上的負(fù)載是在不斷變化的，因此這一數(shù)據(jù)的變化范圍有時(shí)會(huì)相當(dāng)?shù)卮蟆?

?由于CPU占用時(shí)間是相對(duì)穩(wěn)定的，所以你可以使用這一數(shù)據(jù)作為衡量你的優(yōu)化措施是提高了查詢性能，還是降低了查詢性能。

（二）接下來我們使用SET STATISTICS IO

和上面的準(zhǔn)備工作一樣，當(dāng)我們執(zhí)行完“select? * from Person where ID=50000”時(shí)，我們會(huì)在消息選項(xiàng)卡中看到以下信息：

(1 行受影響)
表 'Person'。掃描計(jì)數(shù) 5，邏輯讀取 10418 次，物理讀取 105 次，預(yù)讀 10418 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次。

這里面的一些信息是非常重要的，另一部分則可以不去考慮。

掃描次數(shù)：在查詢中涉及到的表被訪問的次數(shù)。在我們的例子中，Person表只被訪問了5次，由于查詢中不包括連接命令，這一信息并不是十分有用，但如果查詢中包含有一個(gè)或多個(gè)連接，則這一信息是十分有用的。

邏輯讀取：這是最有用的數(shù)據(jù)。我們知道，SQL Server在對(duì)任何數(shù)據(jù)進(jìn)行操作前，必須先把數(shù)據(jù)讀取到數(shù)據(jù)緩沖區(qū)中。此外，我們也知道SQL Server何時(shí)會(huì)從數(shù)據(jù)緩沖區(qū)中讀取數(shù)據(jù)，并把數(shù)據(jù)讀取到大小為8k字節(jié)的頁中。邏輯讀取得意思就是指SQL Server為得到查詢結(jié)果，而必須從數(shù)據(jù)緩沖區(qū)中讀取的頁數(shù)。

SQL Server在執(zhí)行邏輯讀的時(shí)候，不會(huì)讀取比實(shí)際結(jié)果多或者少的數(shù)據(jù)，因此在相同的數(shù)據(jù)集中，執(zhí)行同一個(gè)查詢，得到的邏輯讀的結(jié)果總是相同的。因此，在進(jìn)行查詢優(yōu)化時(shí)邏輯讀的值就是來衡量你的優(yōu)化措施是否可行的一個(gè)很好的標(biāo)準(zhǔn)。（在查詢時(shí)邏輯讀越少，其效率就越高，查詢速度就越快，反之，就慢）

物理讀取：在執(zhí)行真正的查詢操作前，SQL Server必須從磁盤上向數(shù)據(jù)緩沖區(qū)中讀取他所需要的數(shù)據(jù)。在SQL Server開始執(zhí)行查詢前，當(dāng)它發(fā)現(xiàn)要讀的數(shù)據(jù)不再數(shù)據(jù)緩沖區(qū)中時(shí)，它會(huì)首先把它需要的數(shù)據(jù)讀到數(shù)據(jù)緩沖區(qū)中。物理讀取的意思就是指SQL Server把所需數(shù)據(jù)讀到數(shù)據(jù)緩沖區(qū)中時(shí)，從物理磁盤上讀取的數(shù)據(jù)頁數(shù)。

遺憾的是，在我們進(jìn)行查詢優(yōu)化時(shí)，是不需要考慮物理讀的。盡管物理讀要比邏輯讀可能需要更多的服務(wù)器資源。因?yàn)镾QL Server在執(zhí)行查詢時(shí)，是不可能通過性能調(diào)節(jié)而減少物理讀的次數(shù)的。減少物理讀是一項(xiàng)很復(fù)雜并且重要的工作，它涉及到的是整個(gè)服務(wù)器的性能調(diào)節(jié)，而不僅僅是查詢性能的調(diào)節(jié)。在進(jìn)行查詢性能調(diào)節(jié)時(shí)，我們是不能控制數(shù)據(jù)緩沖區(qū)大小或服務(wù)器的忙碌程度，以及完成查詢所需要的數(shù)據(jù)是在數(shù)據(jù)緩沖區(qū)還是在磁盤上，唯一我們可以控制的就是得到查詢結(jié)果多需要執(zhí)行的邏輯讀的次數(shù)。因此在進(jìn)行查詢優(yōu)化時(shí)，我們大可不必在意物理讀的數(shù)據(jù)。

預(yù)讀：指的是SQL Server在進(jìn)行查詢優(yōu)化前，預(yù)測(cè)要讀取的數(shù)據(jù)頁，根據(jù)預(yù)讀的準(zhǔn)確程度，預(yù)讀可能有用也可能沒用。和物理讀一樣，在我們進(jìn)行查詢優(yōu)化時(shí)是不需要考慮的。

剩下的幾個(gè)“l(fā)ob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預(yù)讀 0 次”意思和前面說的差不多，只是在進(jìn)行增、刪、改的時(shí)候IO資源的操作情況。

所以呢，我們?cè)谧霾樵儍?yōu)化的時(shí)候，使用SET STATISTICS TIME 和SET STATISTICS IO 是個(gè)不錯(cuò)的選擇。????

?

SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查詢性能優(yōu)化中的作用