標籤彙整: index

SQL Server中的索引離散狀況

發佈日期: 作者:
1

SQL Server 資料庫中的索引是用來加速查詢效率的一種方式, 利用了空間換取時間的方法來將查詢的速度增加.

既然是利用空間換取時間的方法, 代表著該存放的空間, 資料的排列狀況就會隨著時間的過去, 有資料的新增, 刪除, 修改時, 造成的索引離散狀況, 若是索引離散時, 代表著存取這些索引的 IO 量會增加, 也代表著效率會下降的狀況會發生.

如何查詢這個資料, 可以參考這篇文章: http://sharedderrick.blogspot.com/2010/02/index-fragmentation.html 有詳細的說明, 利用該查詢指令, 若是要查找特定 table 的話, 可以這樣下(SQL Server 2005以上版本):

SELECT sch.name N’結構描述’, obj.name N’資料表’,
inx.name AS N’索引名稱’,
index_type_desc N’索引類型’,
avg_fragmentation_in_percent ‘片段(%)’,
avg_page_space_used_in_percent N’頁面飽和度(%)’,
fragment_count,
avg_fragment_size_in_pages,
page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(‘TABLE_NAME’),NULL, NULL, ‘SAMPLED’) AS phy
INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> ‘HEAP’  AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 5 DESC

利用了 OBJECT_ID() 函數, 把 table 的 object id 找出來, 便能指定查找特定 table 中的索引離散狀況.

根據微軟官方的資料: http://technet.microsoft.com/zh-tw/library/ms189858.aspx , 離散狀況在 5% 以下不需要調整, 在 5% ~ 30%之間, 可以利用 ALTER INDEX REORGNIZE 來進行重組, 而大於 30% 時, 可以使用 ALTER INDEX REBUILD (ONLINE = ON) 來進行索引重建.

不過若系統效能允許的狀況下, 其實也是可以直接使用 DROP INDEX, CREATE INDEX 的方式來重建的.

其他相關文章一併整理如下:

Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://technet.microsoft.com/en-us/library/cc966523.aspx

sys.dm_db_index_physical_stats (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms188917.aspx

ALTER INDEX (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188388.aspx

Table and Index size in SQL Server
http://stackoverflow.com/questions/316831/table-and-index-size-in-sql-server

SQL Server Indexes
http://odetocode.com/articles/70.aspx

查詢分割資料表的資料存放位置

發佈日期: 作者:
回覆

由於資料量龐大, 為能改善效能, SQL Server 2005 增加了 Partition Table 功能將 table 依 partition function 來做分塊”橫切”的方式存放, 以將常用資料及不常用資料(如歷史訂單資料), 做實體的存放位置劃分, 讓它實際上是同一個表, 但在存放上是在不同的 filegroup 上, 如此一來, 便能有效將資料表做分塊(分區橫切)管理, 若是 partition function 切割有效的話, 對於查詢應用及備份應用應該會有非常大的幫助.

接下來, 我們需要了解的是實作完成後的 partition table 內, 各分區的資料存放筆數狀況, 可以利用兩個系統表來操作取得, 分別為:

  1. sys.partitions : (http://msdn.microsoft.com/zh-tw/library/ms175012.aspx)
  2. sys.dm_db_partition_stats: (http://msdn.microsoft.com/zh-tw/library/ms187737.aspx)

若是要取得某 table 名為 tblTest 這個 table 在各 partition table 中的存放筆數, 可以利用這個查詢:


SELECT ps.partition_number
,ps.row_count, ps.index_id
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('tblUsers')

這樣取得的結果, 一般若非 partition table, 會得到一筆資料, 例如:

d1

其中三個欄位說明如下:

  • partition_number: 指對應的資料分割編號(以1為基礎), 把他想成存放的分區代碼
  • row_count: 這個就是資料筆數
  • index_id: 這個很重要, 0為堆積, 1為clustered, 2以上為non-clustered

若是有無切分 partition table , 但又有多個 index 的 table 會得到如下結果:

d2

其中, 就是 1為clustered, 而9, 11, 12為 non-clustered 的資料.

再來就是看有切 partition table 的結果:

d3

這個案例來自於這篇文章: [SQL Server]如何將現有table調整為partition table (http://bbs.diary.tw/viewtopic.php?f=13&t=464)

其中, 可以清楚地看到 index_id 為0的, 是 partition_number 為1, 2, 3的資料存放筆數狀況, 而 index_id為2的是後來利用增加 primary key non-clustered index 的方式加入的索引, 得到總筆數的一個 index 存放狀況.

簡而言之, 資料存放的方式為 parititon1 放了 2筆, partitiona2放了 3筆, partition3放了4筆資料, 這樣的查詢結果對於要查目前的 partition table內存放資料的對應 parition 狀況應該就有很清楚的結果呈現了.