檢查資料庫中每張資料表的使用狀況

通常在系統上線後，隨著User日積月累地去操作系統的次數，
你會發現有些資料其實不需要經常被User存取到，或者是根本User也不需要這些資料了，
此時為了增進系統的效能，應該要著手進行資料搬移或刪除等動作，俗稱HouseKepping的作業。

舉例來說:
1.User有可能不需要經常的存取到上一季的資料，那麼可以寫個排程去定期將上一季的資料，
搬至另一個表格存檔封存，類似E-Mail封存的概念，而原本這張表格只需要保留本季的資料即可。
2.User可能再也不需要存取一年前的舊資料，那麼即可寫個排程去定期刪除超過一年的舊資料。

因此定期查看每張資料表的使用情況就變的重要了，此類查詢SQL指令如下:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    t.modify_date AS ModifyDate,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB    
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, t.modify_date, p.Rows
ORDER BY 
    t.Name

由上圖可看出那些表格擁有比較多筆資料和所佔的容量，以及他的表格中資料最後的修改日期，
那麼就可以先針對最近修改日期而且有數萬筆以上資料的表格，進行舊資料的搬移或刪除。

[SQL Server]Index管理和調教

曾經提到如何使用DMV找出Top 10 IO/CPU Query的SQL指令，

通常會針對前幾名消耗硬體資源較大的SQL指令進行索引調教，

首先先介紹查詢Index使用情況的DMV:sys.dm_db_index_usage_stats

--使用語法如下
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       USER_SEEKS, 
       USER_SCANS, 
       USER_LOOKUPS, 
       USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
       INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
       AND S.database_id = DB_ID()

由以上的查詢結果得知，每各Index被讀取(USER_SEEKS+USER_SCANS+USER_LOOKUPS)的次數以及

被更新(USER_UPDATES)的次數，因為Index如果沒有經常被讀取，那代表他是比較沒有效益的，

另外無用的索引建太多，會影響Insert/Update/Delete的效率，因此索引不是建的愈多愈好，

例如:在OLTP系統經常有交易在進行的情況下，反而容易造成鎖定的產生，進而拉長應用程式等待交易的時間!!

[SQL Server]SQL Profiler和SQL Trace的介紹

曾經有人問過我，要如何錄製他的程式對SQL Server所下的SQL指令，

筆者回他用SQL Profiler就可以錄到了，但要切記一點:必須要主動過濾錄製的範圍，

因為如果是正式環境，上面會有大量的指令在進行中，一旦沒有去設定自己要錄的條件，

會錄到許多自己不想要的內容。

在實務上通常會以HostName(主機名稱)、LoginName(登入帳號)、TextData(對SQL Server下的指令)，

這三個欄位來進行過濾，實作步驟如下:

進入SQL Profiler->檔案->新增追蹤
因為只要錄製特定帳號User1下的指令，Events只要選擇TSQL即可

接著再點選資料行篩選，在左邊清單找到LoginName，在右邊類似欄位中輸入%user1%後，
便可以開始執行錄製了!

在SSMS下用user1帳號登入後，輸入以下指令進行查詢

執行後，立刻去SQL Profiler查看，果然己經錄製下來!