![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
SELECT DB_NAME(database_id) as [データベース名],
count(*) as [期待しているインデックスが存在しない数]
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
USE [データベース名]
SELECT gs.avg_user_impact AS [予測されるクエリパフォーマンス改善率],
gs.last_user_seek AS [最後にシークした時間],
id.statement AS [テーブル名] ,
id.equality_columns AS [等値述語に使用できる列],
id.inequality_columns AS [不等値述語に使用できる列] ,
id.included_columns AS [包括列として必要な列],
gs.unique_compiles AS [コンパイルおよび再コンパイルの数],
gs.user_seeks AS [クエリによって発生したシーク数]
FROM sys.dm_db_missing_index_group_stats AS gs
INNER JOIN sys.dm_db_missing_index_groups AS ig
ON gs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS id
ON ig.index_handle = id.index_handle
WHERE id.[database_id] =DB_ID() Order By gs.last_user_seek ASC
SELECT [テーブル名] = t.name,
[統計名] = s.name,
[統計の自動作成] = s.auto_created,
[NORECOMPUTEオプション] = s.no_recompute,
[フィルター定義] = s.has_filter,
[フィルター定義] = s.filter_definition,
[行数] = sp.rows,
[フィルター化されていない行数] = sp.unfiltered_rows,
[ステップ数] = sp.steps,
[サンプリング行数] = sp.rows_sampled,
[統計情報最終更新日] = sp.last_updated
FROM sys.tables AS t
INNER JOIN sys.stats AS s
ON s.object_id = t.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
ORDER BY t.name OPTION (RECOMPILE);
SELECT [平均実行時間(msec)] = total_elapsed_time / execution_count / 1000.00,
[平均CPU時間(msec)] = total_worker_time / execution_count / 1000.00,
[平均物理I/O数] = total_physical_reads / execution_count,
[平均論理I/O数] = (total_logical_reads + total_logical_writes)/ execution_count,
[SQL文] = SUBSTRING(est.text, (statement_start_offset / 2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(est.text)
ELSE statement_end_offset
END - statement_start_offset) / 2) + 1)
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS est
UPDATE STATISTICS テーブル名;
EXEC sp_updatestats;
DBCC FREEPROCCACHE;
ALTER INDEX <インデックス名> ON <テーブル名> REBUILD;
ALTER INDEX ALL ON <テーブル名> REBUILD;
大変勉強になります。
