Check DB Statistics

/* Check DB Statistics
The table had more than 500 rows when the statistics were gathered, 
and the colmodctr of the leading column of the
statistics object hAS changed by more than 500 + 20% of the number of rows in the table 
when the statistics were gathered .
查詢最佳化工具由於使用 AUTO_CREATE_STATISTICS 選項而產生統計資料時，統計資料名稱就會以 _WA 為開頭。
判斷查詢最佳化工具是否已經針對查詢述詞資料行建立了統計資料 */
-- update STATISTICS
USE DBName
GO
EXEC sp_updatestats;
GO

-- 物件, 欄位, 統計名稱
SELECT object_name(s.object_id) AS [object_name],
COL_NAME(sc.object_id, sc.column_id) AS [column_name],
s.name AS statistics_name
FROM sys.stats AS s
JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name LIKE '_WA%'
ORDER BY 1;

-- 資料表統計資料
SELECT so.name AS [table_name],
ss.object_id AS [object_id],
so.type_desc AS [object_type],
so.modify_date AS [object_modify],
ss.name AS [stats_name],
stats_date(ss.object_id, ss.stats_id) AS [statistics_update_date]
FROM sys.stats ss
JOIN sys.objects so ON ss.object_id = so.object_id
WHERE ss.object_id in ( 
SELECT object_id FROM sys.objects WHERE type_desc <> 'SYSTEM_TABLE' )
AND ss.name LIKE '_WA%'
ORDER BY 6

-- 索引統計資料
SELECT so.name AS object_name,
ss.object_id,
so.type_desc AS object_type,
ss.name AS stats_name,
stats_date(ss.object_id, ss.stats_id) AS statistics_update_date
FROM sys.stats ss
JOIN sys.indexes so ON ss.object_id = so.object_id
WHERE ss.object_id IN ( 
SELECT object_id FROM sys.objects WHERE type_desc <> 'SYSTEM_TABLE' )
AND ss.name LIKE '_WA%'
ORDER BY 5

-- 資料表統計資料欄位數量
SELECT object_name(sp.[object_id]) AS [Table],
sp.[stats_id] AS [Statistic ID],
s.[name] AS [Statistic],
sp.[lASt_updated] AS [LASt Updated],
sp.[rows],
sp.[rows_sampled],
sp.[unfiltered_rows],
sp.[modification_counter] AS [Modifications]
FROM [sys].[stats] AS s
OUTER APPLY sys.dm_db_stats_properties(s.[object_id],s.[stats_id]) AS sp
WHERE s.[name] like '_WA%'
ORDER BY 4 DESC

/*
rows bigint 上一次更新統計資料時位於資料表或索引檢視表中的資料列總數。
如果篩選了統計資料或是統計資料對應至篩選過的索引，
此資料列數可能會少於資料表中的資料列數。

rows_sampled bigint 針對統計資料計算進行取樣的資料列總數。

steps int 長條圖中的步驟數。如需詳細資訊，請參閱＜DBCC SHOW_STATISTICS (Transact-SQL)＞。

unfiltered_rows bigint 套用篩選運算式 (針對篩選的統計資料) 之前，資料表中的資料列總數。
如果統計資料未經過篩選，unfiltered_rows 就會等於 rows 資料行中傳回的值。

modification_counter bigint  自從上次更新統計資料以來，前端統計資料資料行 
(用以建置長條圖的資料行) 的總修改次數。此資料行沒有包含記憶體最佳化資料表的資訊。 
*/
-- 參考
-- 我忘了參考那邊