難忘的經驗：建立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 Audit測試

使用SQL Audit，測試版本為2008 SP3

--1.建立稽核並啟用

USE [master]

GO

/****** Object:  Audit [Audit-DEMO-0311]    Script Date: 03/15/2012 00:03:37 ******/

CREATE SERVER AUDIT [Audit-DEMO-0311]

TO FILE

(         FILEPATH = N'D:\TEMP\'

           ,MAXSIZE = 0 MB

           ,MAX_ROLLOVER_FILES = 2147483647

           ,RESERVE_DISK_SPACE = OFF

)

WITH

(         QUEUE_DELAY = 1000

           ,ON_FAILURE = CONTINUE

           ,AUDIT_GUID = '0f9b0a5b-478d-4b3b-90cf-2b68b8657e81'

)

GO

執行完成後，會在安全性中看到一個新的稽核(Audit-DEMO-0311)，接著啟用它。

--2.建立資料庫稽核規格

USE [北風貿易]

GO

CREATE DATABASE AUDIT SPECIFICATION [北風貿易]

FOR SERVER AUDIT [Audit-DEMO-0311]

ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),

ADD (DELETE ON OBJECT::[dbo].[AduitTable] BY [dbo]),

ADD (EXECUTE ON OBJECT::[dbo].[AduitTable] BY [dbo]),

ADD (INSERT ON OBJECT::[dbo].[AduitTable] BY [dbo]),

ADD (SELECT ON OBJECT::[dbo].[AduitTable] BY [dbo]),

ADD (UPDATE ON OBJECT::[dbo].[AduitTable] BY [dbo])

WITH (STATE = ON)

GO

執行完成後，會在北風貿易資料庫的安全性è資料庫稽核規格會看到一個名稱為北風貿易的稽核規格，接著啟用它。

--3.執行測試指令並觀看稽核結果

SELECT * FROM dbo.AduitTable

WHERE Column1='TEST2'

發現稽核的語法變成

SELECT * FROM [dbo].[AduitTable] WHERE [Column1]=@1

實際的值TEST2則是看不到。

上網查了一下，發現這是一個Bug，要上Service Pack才能解決這個問題，於是我將SQL SERVER安裝了SP3，安裝完成後，發現之前的問題解決了。

 接著測試宣告變數

--4.執行宣告變數的SQL指令，並觀看結果

DECLARE @Column1 VARCHAR(10)

SET @Column1='2'

SELECT * FROM AduitTable

WHERE Column1=@Column1

稽核結果只有變數名稱，但沒有實際資料值。

後來在討論區看到這個問題在sql server 2008尚未解決，結果只好自己摸摸鼻子寫trigger或用cdc了，真希望微軟能快快解決這個問題。

Ps：SQL SERVER 2008 R2 SP1的版本好像也是一樣。

SQL Server Transactional Replication報告心得

今天demo了Transactional Replication ，把今天demo的心得與同事提出的幾個問題記下來後找尋解答。
1.如果在一個大table(約5千萬筆資料)，如果要增加一個欄位，那請問要怎麼辦？
我的想法是因為sql server使用alter table add column很慢，所以還是先件立一個空的table，把舊的table rename後，把資料匯入新的table再把新的table的名稱改回原來的table，然後重新建立snapshot後同步，因為snapshot的同步相當快，所以這種做法比直接alter table add column同步要來的快。
2.以後投影片的圖要製作標示流程要編號，這樣聽眾才不會搞混。
3.報告的時後不是很熟的不要貼在ppt，譬如：oracle的同步與Merge Replication ，熟了話才講出來。
4.如果聽眾當場有疑問而討論起來，請他們報告完後在私下討論。
5.Transactional Replication同步的table如果不勾選某些欄位，則那些欄位不會出現在被同部的db。
6. 如果遇到不會的，說之後會研究並告知其資訓。
7.直接在大table增加欄位，informix與oracle的效能是相當快的。
8. Transactional Replication在的distribution database在同步資料與刪除資料一定會有一個機制，要知道同步資料與刪除資料這兩個動作為何不會有衝突。

解決寫多次WITH(NOLOCK)的方法

問題：日前同事有一個報表會造成其他SQL程式BLOCK，所以同事使用了WITH(NOLOCK)，範例程式碼如下：

SELECT * FROM dbo.tabCheckDataCount WITH(NOLOCK)

但是問題來了，那支報表有JOIN到十幾個TABLE，所以他要一個一個的在TABLE後面加上WITH(NOLOCK)，而其他幾個也要如此，算一算總共要加N次。

解法：在SQL語法的最前面加上SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED，如此就不必寫很多次WITH(NOLOCK)。

範例程式碼如下：

--不必寫WITH NOLOCK

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM dbo.tabCheckDataCount

結論：WITH(NOLOCK)可以用在SQL語法讓部分TABLE在READ UNCOMMITED狀態，而SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED則是讓所有讀取的TABLE都為READ UNCOMMITTED。