数据库动态管理视图DMV(3)

--查询引发io最多的前10位查询或批处理
SELECT TOP 10
 (total_logical_reads/execution_count) AS AVG_LOGICAL_READS,
 (total_logical_writes/execution_count) AS AVG_LOGICAL_WRITES,
 (total_physical_reads/execution_count) AS AVG_PHYSICAL_READS,
 execution_count,
 (
  SELECT SUBSTRING([TEXT],statement_end_offset/2+1,
  (CASE WHEN statement_end_offset =-1
     THEN LEN(CONVERT(NVARCHAR(MAX),[TEXT]))*2
     ELSE statement_end_offset END -statement_end_offset)/2)
  FROM SYS.dm_exec_sql_text(sql_handle)) AS QUERY_TEXT,
  plan_handle
FROM SYS.dm_exec_query_stats
ORDER BY total_logical_reads+total_logical_writes desC

--查看索引丢失情况
--先将对TestTable表的查询执行100遍，条件分别为number和Createtime字段
declare @i int
set @i=0
while @i<100
begin
select * from dbo.TestTable where number between 1000 and 10000
set @i=@i+1
end
go

declare @i int
set @i=0
while @i<100
begin
select * from dbo.TestTable where Createtime between '2013-05-02 10:13:07.553' and '2013-05-02 10:13:11.280'
set @i=@i+1
end
go
SELECT T1.[object_id] ,T2.user_seeks,T2.user_scans,T1.equality_columns,T1.inequality_columns FROM SYS.dm_db_missing_index_details AS T1
JOIN SYS.dm_db_missing_index_groups AS T3 ON T1.index_handle=T3.index_handle
JOIN SYS.dm_db_missing_index_group_stats AS T2 ON T2.group_handle=T3.index_group_handle
WHERE database_id=DB_ID() AND [OBJECT_ID]=OBJECT_ID('TestTable')

SELECT * INTO #TEMP FROM dbo.TestTable