難忘的經驗：建立INDEX後，讓SELECT效能變慢

1.情況說明：

朋友急call，告知我他的系統變得超慢，看了一下IO高的SQL，有大量的IO發生，查看一下執行計畫發現大量使用WorkTable(TempTable)讓效能低落。

2.溝通詢問：

Q1 詢問朋友有沒有修改程式碼?

A1 朋友說沒有

Q2 最近table schema有沒有異動

A2 朋友說沒有

Q3 table schema是除了異動欄位外，包含contact,Index都算

A3 朋友:有，Index我加了Index

3.如何解決：

記得之前看過一篇文章 ，文中有提到建立Index後影響其他語法查詢效能，再次確認這個Index是查詢報表用砍掉對業務沒有影響，就大膽的砍掉該Index，問題就瞬間解決了。

4.問題重現:

• 查詢語法

•  先建立IX_FIRST索引語法

• 執行查詢語法(IO為608)

• 建立IX_SECOND索引語法

• 再次執行查詢語法(IO為368495)

• 刪除IX_SECOND後IO又恢復為608(整體IO差了大概606倍)

筆記_如何使用SQL Profiler找出死結

一、    問題

當執行SQL指令產生1205的ERROR時，我要如何協助AP找到產生deadlock的SQL指令？

二、    解決方案

使用SQL Profiler錄製，當死結發生時，SQL Profiler會提供Deadlock Graph提供Deadlock相關資訊。

三、    執行

1.  建立測試資料

建立兩個資料表DATA01與DATA02並產生測試資料。

2.  模擬死結發生

在SESSION1建立一筆交易，先更新DATA01資料表後的 15秒後才更新DATA02的資料表。

在SESSION2建立一筆交易，先更新DATA02資料表後的 15秒後才更新DATA01的資料表。

3.  使用SQL PROFILER錄製死結。

點選Locks的Deadlock Graph等事件。

    最後死結發生，SESSION2被當作此次的犧牲者。

   此時SQL PROFILERT偵測到死結產生Deadlock Graph

4.  解讀Deadlock Graph內容

檢視Deadlock Graph做邊藍色打X的部分是被犧牲的SESSION，中間長方形的是被鎖定的Resource，可能是資料表、PAGE或是單一資料列，而左右箭頭代表Resource被取得(Hold)與要求(Request)。

  將Deadlock Graph使用XML的格式打開

  可以看到更詳細的資訊，其中最下方的inputbuf代表與死結有關的SQL指令，而deadlock    victim代表被犧牲的SESSION，有時候如果SQL指令太長(例如STORED PROCEDURE)可以使用sqlhandle加上sys.dm_exec_sql_text產出完整的SQL語法。

小結：

當死結發生時可用SQL PROFILER去錄製死結，產生出來的Deadlock Graph可透過圖形或XML的格式取得產生死結的原因，通常inputbuf會顯示產生死結的SQL指令，如果SQL指令太長可以使用sys.dm_exec_sql_text找出完整的SQL指令。

參考網址

筆記_SQL Server交易(Transaction)特性與交易隔離層級(Isolation Level)

上次討論到SQL Server使用的鎖定類型(lock types)有Shared(S)、Exclusive(X)與Update(U)，這次我們要討論這些鎖定類型與交易(Transaction)的關係，我們先討論一下甚麼是交易？交易可以看成一個工作單位，這個單位會完成多個行為，例如ATM的轉帳是一個交易單位，這個交易有兩個行為，從A帳號轉出500元給B帳號與B帳號收到500元，除此之外交易還必須符合ACID的特性(ACID為縮寫)。

1.  交易的特性摘要如下：

(1)     Atomicity：交易內的行為只有兩種結果，全成功或全失敗，SQL SERVER要達成Atomicity時需將XACT_ABORT設定為ON。

(2)     Consistency：SQL SERVER提供constraints 完成資料的完整性，例如：PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, and NOT NUL。

(3)     Isolation：針對正在進行交易的資料，其他交易是不可讀取與異動的，簡單來說就是該筆資料會與其他交易隔離。例如：A交易發生時要將王小明年齡改從19歲改為20歲時，此時B交易是看不到也摸不到王小明這筆資料。

(4)     Durability：交易完成後，資料無論如何都會留在資料庫內，SQL Server透過Write-ahead log的方式來實現Durability，也就是先將資料寫入LOG檔後再寫入資料檔，所以當發生意外時，可以靠著LOG的內容依序還原資料。

2.  常見的交易迷思：

Q1:當我們執行Insert/Update/Delete指令時沒有使用begin tran或commit等關鍵字時是不會有交易的。

ANS1：SQL SERVER預設是implicit transaction，所以是有交易的。implicit transaction在某些情境下是相當耗資源的，最常見的例子就是使用insert into table values匯入10萬筆資料，implicit transaction耗費資源的時間與log檔上都會多很多。

Q2:Select指令是沒有交易存在的？

ANS2：Ｓelect是有交易的喔，這部份會在後面的筆記說明。

Q3:如果使用NOLOCK等HINT是沒有交易的

ANS3：交易還是有的，只是我們將isolation層級降為read uncommitted。

3.  Isolation Level

每個交易都有自己的Isolation Level，SQL SERVER有4種悲觀（Pessimistic）的isolation level，如下表：

Isolation層級	Share Locks 行為	Table Hint
Read uncommitted,	不需要Share locks	NoLock
read committed,	Share locks在Commit完後釋放	READ committed
repeatable read	Share locks在交易結束後釋放	repeatableread
serializable	Share Locks的範圍變大(RANGE locks)，且直到交易結束才釋放。	HOLDLOCK

除了Read uncommitted之外，每個Isolation Level在資料寫入時會鎖住(BLOCK)讀取行為。此外不論Isolation Level為何，資料異動時的互斥鎖(exclusive lock)都會在持續到交易結束，所以上述四種Isolation Level的差異主要是在於Share Locks的行為。

Read uncommitted：沒有shared locks，所以當其他還沒有COMMIT交易在修改資料時，其他交易是可以讀取該筆資料的，所以當A交易異動資料且尚未commit時，B交易是可以讀取A交易異動過的資料。

Read committed：shared locks會在讀取完資料後就釋放，當A交易讀取資料時，B交易是無法修改被A交易資料的正在讀取的資料，但是一旦讀取完成shared locks 就會釋放(此時A交易尚未完成)，B交易就可以修改A交易之前讀取的資料。

Repeatable read：shared locks交易結束後才會釋放，所以只要A交易讀取資料時，除非到整個交易結束，否則B交易無法法修改被讀取的資料。

Serializable ：shared locks會持續到交易結束，而且shared locks範圍會變大。所以只要A交易讀取資料時，除非整個交易結束，否則B交易無法修改讀取的資料與被讀取資料的特定範圍是無法進行新增的行為。

我們可以使用 set transaction isolation level等指令去設定交易的Isolation Level，例如：範例語法，如果要在TABLE設定交易的isolation level的話，可以在資料表後面使用TABLE HINT，例如範例語法2

範例語法1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION Transaction2

SELECT *

 FROM TestTable

 WHERE Value='C'

 GO

COMMIT

範例語法2

SELECT *  FROM TestTable WITH (HOLDLOCK)

WHERE Value='C'

4.結論：

交易的Isolation Level其實是很容易理解的，我們只要專注在不同Level上shared locks的差異即可，此外我們目前只討論悲觀(Pessimistic)的 Isolation Level，至於SQL SERVER的2種樂觀(optimisitic)的Isolation Level：Snapshot and read committed snapshot又是另一個故事了。

參考：

Locking in Microsoft SQL Server (Part 2 – Locks and transaction isolation levels)
[SQL Server] 了解ACID
Compare Repeatable Read and Serializable SQL Server Transaction Isolation Levels

筆記_鎖定類型

一、    鎖定類型Lock types

1.   Shared lock (S)：執行select指令時發生，相容於Shared與Update等lock。

2.   Exclusive lock (X)：執行insert、update與delete時發生，不會相容於任何一個lock。

3.   Update lock (U)：執行類似updtae tablea set col1=’A’ where ID=2的時令時，sql server會先搜尋符合條件的資料，此時就會產生Update lock，而當找到並更新內容時會轉換成Exclusive lock。

4.   Intent lock (IS, IX, IU, etc)：當row有Exclusive lock時，在page、table與database層級就會產生IX lock，用來增加檢查lock的相容性效率。

二、    Update lock與Intent lock說明

1.   Update lock的功能：預防死結，當有兩個session語法要更新同一筆資料時，例如：updtae tablea set col1=’A’ where ID=2 ，

l   若沒有Update lock的情況如下：

(1) 第一個session執行SQL時，因為要搜尋資料，故會放置Shared lock。

(2) 接著第二個session執行SQL也會放置Shared lock，因為Shared lock是相容的。

(3) 當第一個session要更新資料時，會將Shared locks轉成Exclusive lock，但因為第二個session有Shared locks，故會等待第二個釋放lock。

(4) 當第二個session要更新資料時，也會將Shared lock轉成Exclusive lock，但因為第一個session有Shared lock，故會等待第一個釋放lock。

(5) 兩個session會無限的等待，就會產生死結(deadlock)。

l   若有Update lock的情況如下：

(1) 第一個session執行SQL時，因為要搜尋資料，故會放置Update lock。

(2) 接著第二個session執行SQL也會放置Update lock，但因為Update lock是不相容的，故第二個session會被blocking，無法放置lock。

(3) 第一個session要更新資料將Shared lock成功轉成Exclusive lock

(4) 第一個session完成交易，釋放Exclusive lock。

(5) 第二個session將Shared lock成功轉成Exclusive lock並也完成交易。

(6) 皆大歡喜。

2.   Intent lock的功能：增加檢查lock相容性的效率。

(1) 當session1刪除一筆資料，對row data放置Exclusive lock。

(2) 此時session1也會對page、table與database放置Intent Exclusive lock。

(3) 若有session2要對table放置Shared lock時，session2藉由Intent Exclusive lock知道table內已有Exclusive lock，而不用逐筆檢查每一筆row。

三、    參考：

1.   http://www.madeiradata.com/role-update-lock-sql-server/

2.   https://www.sqlpassion.at/archive/2016/05/16/why-do-we-need-intent-locks-in-sql-server/

3.   https://aboutsqlserver.com/2011/04/14/locking-in-microsoft-sql-server-part-1-lock-types/

淺談Parameter Sniffing

情況

為何執行Stored Procedure有時會一下快有時一下慢?

為何同一句SQL包在Stored Procedure有時候很慢，但直接執行SQL Command就很快?相信大家都有類似的經驗，平常執行某支Stored Procedure時都跑的好好的，但是特定在某個時間點就跑的很慢，這種情況我也遇到過，我們來看看為何吧?

發現：

於是我觀察在特定時間點的參數與執行結果，發現在月底做月報表時呼叫Stored Procedure，時間參數區間會比較長，傳回結果也較多，但執行速度卻慢很多，但是同樣參數直接使用SQL Command差異卻不大，在摸不著頭緒時我就找G大神求救了。

思考：

很快的找到答案了，原來這種靈異現象是因為Parameter Sniffing而產生，(英文爛不知道怎麼翻譯就直接貼出來了)，那甚麼是Parameter Sniffing呢?

故事就要從頭說起了，SQL Server在第一次執行Stored Procedure時都會建立一個執行計畫優化執行效能，在建立執行計畫時會藉著第一次傳入的參數為Base而建立，Stored Procedure因傳入的參數而產生執行過程就是Parameter Sniffing。Parameter Sniffing所帶來的影響就是沒有效率的執行計畫進而讓直行效率下滑。

測試：

我們來實際測試一下，測試情境為使用不同的參數查詢資料筆數為1000000的資料表。

1.      建立測試資料表

2.      建立測試SP

3.      第一次測試SP

4.      第一次測試SP結果

測試結果得到資料筆數分別為109484與885，執行計畫兩者都是使用Index Scan(參數為1999-01-01到1999-12-31的執行計畫)，消耗的邏輯讀取IO皆為3593。

執行結果

執行計畫

執行SP所消耗的IO

5.      第二次測試SP

6.      第二次測試SP結果

測試結果得到資料筆數分別為885與109484，執行計畫兩者都是使用索引搜尋加上索引鍵查閱(參數為2005-01-01到2005-01-03的執行計畫)，消耗的邏輯讀取IO分別為2725與335550。

執行結果

執行計畫

執行SP所消耗的IO

解決方案：

如上述，Parameter Sniffing是第一次執行Stored Procedure 時，SQL Server根據傳入的參數，建立以該參數為基礎的最佳的執行計畫，要特別注意的是這裡說的第一次是指在compile 或recompile時還有沒任何procedure cache(我知道這一句有比較難懂)，我們可以藉由使用下列方式來避免Parameter Sniffing的發生。

(1)   使用RECOMPILE

使用RECOMPILE的好處就是每次執行時每次都用重新建立執行計畫，如此就可以避免Parameter Sniffing的發生。

(2)   使用LOCAL參數將Parameter Sniffing停用

在Stored Procedure宣告Loacl參數，使用Loacl參數存取外界傳進來的參數，如本範例使用@StartDate與@StopDate來存取@BeginDate與@EndDate，使用Loacl參數時當作WHERE條件時，由於SQL Server不知道 Local參數的內容為何，所以會建立一個通用的執行計畫。

(3)   依據需求建立多個適當的Stored Procedures

這個方法是依程式開發的需求，例如月報表就建立一支SP，而平日的日報表也建立一支SP，利用不同的SP來區隔不同情況的執行計畫。

結論：

有人會問使用Stored Procedures的其中一個優點不就是建立執行計畫的快取，下一次執行SP時就不用再次建立執行計畫進而提升效能，如果重新RECOMPILE不就是失去這項優勢了嗎?

我的想法是當重新產生執行計畫的Overhead遠小於Parameter Sniffing所帶來的衝擊，那就RECOMPILE吧，如果程式呼叫SP的條件與回傳資料數目差異不多時就用快取的執行計畫，簡而言之視情況而定。

此外同一句SQL的WHERE條件直接給予特定內容(如下圖)，執行SQL指令時就不會有Parameter Sniffing的情況，因為每一次執行時SQL Server Engine已經知道傳入的內容，故可以依據WHERE條件產生最佳的執行計畫，這就是為何同一句SQL包在Stored Procedure有時候很慢，但直接執行很快的原因了。

參考文章