分類
Database

SQL Server的遺失索引統計

先說好, 這篇必須是 SQL Server 2005 以上的用戶才能用到的, 因為用到的資料是 DMV 的系統 view, 也就是 Dynamic Management Views.

這裡會用到的 DMVs 是用來查詢所謂遺失的索引, 白話一點, 就是應該要建立的索引, 而沒有建立的索引, 稱之為所謂的”遺失的索引”. 資料庫在查詢時, 若是發現有這樣的狀況, 會記錄下來, 在 DMVs 內的這幾個表:

sys.dm_db_missing_index_groups
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_details

在這些表內可以利用這篇文章 (揭露隱藏的資料以最佳化應用程式效能)的一個計算方式(當然也可以再調整), 來將影響較為嚴重而又沒有加上索引的 table 找出來, 查詢如下:

SELECT  TOP 10
        [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
        , avg_user_impact
        , TableName = statement
        , [EqualityUsage] = equality_columns
        , [InequalityUsage] = inequality_columns
        , [Include Columns] = included_columns
FROM        sys.dm_db_missing_index_groups g
INNER JOIN    sys.dm_db_missing_index_group_stats s
       ON s.group_handle = g.index_group_handle
INNER JOIN    sys.dm_db_missing_index_details d
       ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

這樣表列出來的資料簡單說明一下:
第一個Total Cost為總成本, 該作者是使用了一個計算的方式, 並以此為排序條件, 找出總成本最高的資料, 第二個 avg_user_impact 為對使用者的影響, 後面三個最重要了, [EqualityUsage]是指等於的條件, 例如 newsid=235 這種條件, 而 [InequalityUsage] 就是指不等的使用, 例如: newsid < 235, 而 [Include Columns]是指查詢時的涵蓋欄位, 也就是指 select newsid, newstitle, newsdesc …. from 前面的欄位.

再來談談有關索引欄位的建立, 上述的後三個欄位就都是要件, 基本上, Equality, Inequality 是指 where 使用的比較欄位, 而 Include Columns 是查詢出來的覆蓋欄位, 至於要如何下這個索引, 成本最粗的下法是將 Equality + Include Columns 加入或 Inequality + Include Columns 加入, 這樣就會有查詢較佳的效能, 但會不會是最好的, 也還是得看異動的頻繁度來考量, 而該索引有沒有價值, 也是必須要評估的, 這裡介紹的方法是將沒有建上的索引資料整理出來列表給管理員來參考用的, 可以節省許多追蹤上的時間. 希望對於效能管理上對各位能有所幫助!!

參考資料:
http://msdn.microsoft.com/zh-tw/magazine/cc135978.aspx

分類
Database

查詢SQL Server中Table是否有Primary Key

資料庫中的 table, 大家都知道 primary key對 table的效能影響很大, 沒有主鍵的 table, 對於一般在程式實作時會有一定的困難, (某些狀況下例外, 例如 log), 尤其是在更新列時, 沒有 primary key時的時候, 效能根本是很糟糕的, 更危險的是可能會有資料本身邏輯上的問題. 今天我們不是來探討 primary key 的重要性, 而是找出資料庫中, 沒有 primary key 的 table.

其實很簡單地可以利用 sysobjects 這個系統表來查詢, 簡單描述如下, 我們利用 sysobjects 對自已對照, 並利用 parent_obj 這個欄位來串接起來自已對應自己的查詢, sysobjects 內有 table, view, primary key (index), 等資訊, 而我們就利用這樣的方式來進行查詢, 整理好的查詢如下: