2016年11月22日 星期二

SQL SEVER 的RID查閱(RID Lookup)


(以下係以 SQL SRVER 2012 為例)
....
之前在SQL SERVER的 RowID這篇文中曾提到;
當我們查看執行計畫時,經常會看到一個「RID查閱」,它的意思是索引中沒有完全涵蓋所需要的欄位,所以需要從索引中經由RID指向至來源TABLE(Heap)中的某筆紀錄,以取得所需欄位資料。













舉個例子來看:

首先建立一個Table(testtbl),並建立一個non clustered index(idx1)
create table testtbl(c1 int identity,c2 int,c3 varchar(1000))
go
create index idx1 on testtbl(c1)
go
接著對它新增1000筆資料……..

上面的 index (idx1),只針對 C1 這個欄位建立。值得注意的是,當來源 Table 是 Heap 時,所有 index 都會在 [keys] 之後,由系統自動增加一個 [HEAP RID] 欄位在最後,若用語法來說明,相當於:
create index idx1 on testtbl(c1,HEAP RID)   --這是示意,語法不能這樣下

我們透過dbcc來查看一下索引的分葉層頁面,可以看到在 C1(key)之後,系統自動新增了一個HEAP RID (key) 










執行select查詢
select c1,c2,c3 from testtbl where c1=453

這句查詢會走 idx1 的索引搜尋,但由於 idx1 中,只有 C1 欄位的資料,沒有 C2,C3 的資料,而 select 要求的是 C1,C2,C3;這時候系統就會透過 index 中的 HEAP RID,快速指向來源table(testtbl)的對應的 row 的實體位址,取得 C2,C3 的資料。這樣的動作即是「RID查閱」。

RID查閱,是針對 Heap Table 的。如果是 Clustered Table,不會有RID查閱,取而代之的是「索引鍵查閱」。













有關索引鍵查閱,待下一篇文章再詳談..

2016年11月20日 星期日

SQL SERVER的 RowID


用過Oracle的人,他在使用SQL SERVER的時候,常會有個抱怨:「Oracle有rowid,為什麼SQL SERVER沒有」。雖然rowid一般是不會用到,但當要刪除重覆資料而只留下一筆的時候,rowid則是簡單好用的。
當然,SQL SERVER也有自己T-SQL語法,不用rowid也可以讓我們刪除重覆資料而僅保留一筆。

當我們查看執行計畫時,經常會看到一個「RID查閱」,它的意思是索引中沒有完全涵蓋所需要的欄位,所以需要從索引中經由RID指向至來源TABLE(Heap)中的某筆紀錄,以取得所需欄位資料。從這點來看,SQL SERVER是有類似於Oracle的rowid的。













其實SQL SERVER的DB中,每個TABLE的每筆資料,都有一個唯一的識別碼(Row Identifier --RID),執行計畫的「RID查閱」及「交易紀錄的Recovery」都會用到它。
RID是由 fileID:pageID:slotID所組成,在每個DB中它是個唯一值。如果你細看它的組成份子,可以看出其實RID就是指資料的「實體存放位址」。

我們可以利用SQL SERVER的虚擬欄位「%%lockres%%」(應該是SQL 2008含以上才有),來查看或取得每筆資料的RID:































以C1=10這筆資料為例,RID為「1:174917:0」,代表這筆資料的實體存放位址在
這個DB的第1個檔案(NTUB.mdf),第174917頁,第0個slot(slot從0起算)。

我們可以再用dbcc page確認一下,果然第174917頁的slot 0就是C1=10的這筆資料。


















虚擬欄位 %%lockres%% 取得的資訊,嚴格的說,是將RID Format過後呈現的格式。真正的RID二進位值可以用「%%physloc%%」來取得,從某個角度來說%%lockres%%可以說是%%physloc%%的十進位格式。

它除了可以用來讓我們知道它的實體存放位置外,也可以用來當查詢的條件














不過,用它當where條件,是沒辦法走index的,它只能是Table Scan。

RID它對於有心去探究SQL SERVER內部的人,是方便的。
對於一般使用者來說,其實也有一個用處,就是可以用它來刪除重覆的資料僅保留一筆。
舉例來說,有個table,當初建的時後,沒有加PK或unique index,現在要對欄位C1加上unique key,但因資料已經重覆,加不上去。此時我們需要將重覆的資料刪除但保留其中一筆。


















以上圖的例子,C1其實應該是唯一值,但目前TABLE已存在許多重覆資料。

我們可以利用RID,用簡單的語法將重覆資料刪除,只保留一筆













 再執行查詢,檢查已無重覆資料

































PS:
刪除重覆資料僅保留一筆也有正規的T-SQL語法可以做到,上面的例子只是便於說明RID。日常的操作上,應儘量使用正規的T-SQL。

平常我們會使用sp_lock來觀察系統的鎖定情況























請看上圖的recsource欄位,是不是很眼熟呢?沒錯,它指的就是 %%lockres%%。我想這也是 %%lockres%%(Lock Resource) 命名的由來吧!
或許你已經想到,可以用sp_lock結果集中的resource,透過 %%lockres%% 去查詢鎖定的實際資料。

RID是SQL SERVER內部用於識別資料實體存放位址的一個指標,對於一般工作上的需求,請儘量使用正規的T-SQL去完成。

如果是Clustered Table,它的%%lockres%%,顯示出來的是key的hash值..

2016年11月13日 星期日

索引建立時,將欄位放在key中跟用include涵蓋進來有什麼差異?


這個題目,舉個例子來說
假設有一句select語法如下:
select t2,t3,t4 from testtable where t2=15189 and t3=2
我們如果要針對這句SQL,建立一個適配的index,有下列兩種建法,都可以做到全涵蓋:
1create index idx1 on testtable(t2,t3,t4)
2create index idx1 on testtable(t2,t3) include(t4)
(當然clustered index也可以做到全涵蓋,它有點類似於(1),此篇僅先探討這兩種index對上述SQL的優劣分析)

在兩者的使用時機上,國外網友的文章已經說的很清楚了。以上述的SQL語法,這位作者是建議使用(2)的方法,在這裡就不在多說了。


在此我想從內部的結構面做個闡述:

首先,只有key columns會被放在根頁面及中繼頁面之中。include的欄位值,只存在於「分葉層頁面」,在根頁面及中繼頁面是不會把include欄位值放進來的,因為include欄位並不用於搜尋。所以include的做法,可以節省根頁面及中繼頁面的數量(因為key欄位變少了)
在分葉層頁面中呢,則不論是keys columns include columns都會被存放在分葉層頁面裏。
從分葉層頁面(leaf page)來講,這兩者兩者佔用的空間是一樣的。
從非分葉層頁面(root/intermediate page)來看,上面的2使用include,它的index row會比較短,因此會降低中繼頁面的數量,從這個角度上來講2優於1)。

第二點,當把key放在 key-columns裏面時,在根頁面及中繼頁面中它必需是排序的,如1的索引,就會依t2,t3,t4的順序下去排序。而當t4放在include中,則只會依t2,t3排序,t4就如同heap不會排序(如下圖的index-paget2=15178,可以看到t4沒有排序,當新增時,如果t2,t3相同,資料永遠會被加在倒數第二筆)















12的差別在於include的欄位不加入排序(亦即無法當作搜尋欄位),它的好處是,新增資料時,速度會比keys欄位的index要快,因為它可以少一次的排序重組動作。如果是keys欄位,則三個欄位都必需按順序來排,當新增或異動資料時速度較慢。
再畫個圖來說明:


有兩個index
idx1key只有一個id,其它兩個name,cityinclude columns

idx2key包含了id,name,city,沒有include


這時候,如果再新增一筆「10001AlanNY」的資料進來,該如何存放




由於idx1,只用idkey,所以它不用考慮name,city的排序,但idx2是用3個欄位當key,所以它需考慮name,city的排序,因為idx2在新增資料的時候,會比較慢。(如果有updatekey值,也是一樣需要考慮排序)。因此從這點來看2同樣優於1


2016年11月11日 星期五

何謂 Dirty Page

SQL SERVER的IO,是以Page為單位。我們知道SQL SERVER在處理AP傳來的SQL命令時,會把需要用到的page從disk file中載入到一塊記憶體所配置的空間(稱為 buffer cache),所有的異動都將在buffer cache中完成。

dirty page是指在buffer cache中已被修改過的頁面,這些頁面由於尚未寫入disk file中,此時頁面在buffer cach跟disk file中的內容是不同的;所以形象的將此頁面稱為dirty。
dirty page它並不指是uncommitted的頁面。
PS:
容易混淆的一點是所謂「dirty read」,是否可以dirty read並不是指是否允許讀dirty page,而是指是否允許讀uncommitted的資料。有人會將uncommitted page稱為 dirty page,這是一個誤解。

我們知道當checkpoint發生時,會將所有的dirty page寫入disk file;即使是uncommitted的頁面。
底下測試,我們開啟一個交易去update資料,但不做 commit,在未commit前,手動執行一次checkpoint,將dirty page 寫入disk file。觀察dirty page的變化,便可得知uncommitted的頁面,是否仍算是dirty page。

以下列指令檢查目前DB中,有沒有任何的 dirty page
SELECT
database_name = d.name,
OBJECT_NAME =
CASE au.TYPE
WHEN 1 THEN o1.name
WHEN 2 THEN o2.name
WHEN 3 THEN o1.name
END,
OBJECT_ID =
CASE au.TYPE
WHEN 1 THEN p1.OBJECT_ID
WHEN 2 THEN p2.OBJECT_ID
WHEN 3 THEN p1.OBJECT_ID
END,
index_id =
CASE au.TYPE
WHEN 1 THEN p1.index_id
WHEN 2 THEN p2.index_id
WHEN 3 THEN p1.index_id
END,
bd.FILE_ID,
bd.page_id,
bd.page_type,
bd.page_level
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.databases d
ON bd.database_id = d.database_id
INNER JOIN sys.allocation_units au
ON bd.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions p1
ON au.container_id = p1.hobt_id
LEFT JOIN sys.partitions p2
ON au.container_id = p2.partition_id
LEFT JOIN sys.objects o1
ON p1.OBJECT_ID = o1.OBJECT_ID
LEFT JOIN sys.objects o2
ON p2.OBJECT_ID = o2.OBJECT_ID
WHERE is_modified = 1
AND d.name = 'DirtyPagesDB'
AND
(
o1.name = 't1'
OR o2.name = 't1'
);


目前DB中,沒有任何的 dirty page







先暫停自動checkoint,然後update Table某筆資料


再檢查DB是否有 dirty page,可以看到已經有一頁dirty page了















接著不做commit,手動做checkpoint,接著再查,如下圖,執行完checkpoint後,已經沒有dirty page了













如果這時我再做rollback,那麼buffer cache的資料會被改回原來樣子,跟disk file的頁面資料又變得不一致,它又會變成dirty page了。

Dirty Pages:
Data that has been modified and Stored in the buffer cache and has not yet been written in to the hard disk.

DMV中的時間值欄位為負數



有時候我們會發現,一些系統DMV中的時間值(單位亳秒)欄位為負數,如下圖





















以 sys.dm_exec_requests 的 total_elapsed_time 為例,它是一個int
範圍是:–2,147,483,648 到 2,147,483,647

正常情況下,它是一個正整數值,從0 ~ 2147483647
二進位表示為 00000000 00000000 00000000 00000000 ~ 01111111 11111111 11111111 11111111

但如果一個query執行時間,超過 2147483647 ,再往上加1,就會變成
10000000 00000000 00000000 00000000
上面這個值,在int的型態中,就表示「負數」,依照「二補數」的算法,其值為 -2147483648(第一位為1,表示負數)

所以一旦它的執行時間超過了2147483647 ,就會開始從-2147483648遞增至 0.

因此,像是這類型態的欄位值,其數字週期,如下所示:
0 → 2147483647 → -2147483648 → 0 → 循環往復

查詢這類型態的欄位值,如果不加轉換,直接取其值的話,就有可能會得到上面那張圖所看到的負值。
像這樣的值,可以用下列的語法來查詢
case 
when [欄位名] < 0 
then 2147483647+(2147483649+[欄位名]) 
else [欄位名] 
end as [欄位別名]

像上面的數字週期所示,從0開始累積最後再到0,大約是48天。但如果它的值真的夠大,超過了48天,又進入循環往復了。那麼就查不出其真正的值了。







還沒 commit 的資料,會不會紀錄在 transaction log.



昨天有位同事問,程式insert一批資料,但最後還沒有commit,程式就當掉了。這些insert的紀錄,會不會寫進transaction?

以下做個測試,首先執行下列指令,開啟一個交易,新增兩筆資料,但不commit.
begin tran
insert into test0804 values('a3','aaa')

insert into test0804 values('b3','bbb')

接著觀察transaction log,如下圖,我們可以看最後,有兩筆insert的交易,被紀錄在了transaction log中..









由上面的簡單測試,可以知道,尚未commit的資訊,會被寫入transaction log中。
接著我模擬程式掛掉後,DB執行rollback,看看transaction log中的變化






我們可以看到,當一個insert指令被rollback時,其實系統是執行delete,將它刪除,最後再紀錄一筆 abort transaction,用來表明這個交易最後是rollback。

上面的測試,也同時解釋了,當一個異動的SQL執行一段時間後被中斷了。系統在做rollback時也需要相當的時間。




設定 Log Shipping 的資料庫,還需要做 Log 的備份嗎



LogShipping 其實就是定時做  backup log,然後傳到目地端做restore log。
因此若在一個資料庫中啟用了Log Shipping,則這個資料庫就不應該再有backup log的備份.否則,會造成logshipping的失敗..


2016年11月10日 星期四

Delete 很久,原來是foreign key搞的鬼.


有個Table,有Primary Key,這個Table約有2仟多萬筆資料.
以Primary Key為where條件,去Select資料,速度很快...
但同樣以Primary Key為條件,去Delete卻非常的久.

查看執行計畫,發現除了這個Table本身之外,還有其它的Table有table-scan或index-scan.
剛開始有點奇怪,Delete怎麼會去scan其它的Table!?
後來才發現這個table有被其它Table reference到,
也就是這個Table的Primary Key,是其它table的Froeign key.

所以當Delete的時候,系統會去它的外鍵Table檢查是否符合Foreign key的限制。

解決方法:
1. exec sp_fkeys @pktable_name = N'your_table_name'     ,查詢這個Table的Foreign keys
2. 外鍵Table的Foreign key,建立index,加快外鍵的搜尋速度.