调优方法

set statistics io on

查看读写，sql读写的单位是一页8k,实际存储8060byte
reads:逻辑读，缓存中取数据
physical reads:物理读，磁盘读到缓存
read-ahead reads:预读,生成执行计划时估计的去读数据

DMV(sqlserver重启后,DMV上的数据会丢失，应该先运行一段时间再查看)

• 查看索引的使用情况
  SELECT ind.name,* FROM sys.dm_db_index_usage_stats dm inner join sys.indexes ind
  on dm.object_id=ind.object_id and dm.index_id=ind.index_id
  WHERE database_id = DB_ID()
  AND dm.object_id=OBJECT_ID('[Referral]')

• 查看丢失的索引(SQLServer优化器认为该加索引的列)
  sys.dm_db_missing_index_details
  sys.dm_db_missing_index_columns(index_handle) //查看缺失索引的列信息
  sys.dm_db_missing_index_groups //缺失索引组
  sys.dm_db_missing_index_group_stats //预计可以提升的性能,avg_user_impact 提升的百分比

SELECT migs.group_handle, mid.* //根据索引组id 找到缺失索引
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
ON (mig.index_handle = mid.index_handle)
WHERE migs.group_handle = 7;