--返回最经常运行的100条语句
SELECT TOP 100
    cp.cacheobjtype
    ,cp.usecounts 
    ,cp.size_in_bytes 
    ,qs.statement_start_offset 
    ,qs.statement_end_offset
    ,qt.dbid ,qt.objectid 
    ,SUBSTRING(qt.text,qs.statement_start_offset/2+1
    ,(case when qs.statement_end_offset = -1
    then len(convert(nvarchar(max),qt.text)) * 2
    else qs.statement_end_offset end - qs.statement_start_offset) / 2) AS statement 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
INNER JOIN sys.dm_exec_cached_plans as cp ON qs.plan_handle = cp.plan_handle
WHERE cp.plan_handle = qs.plan_handle
AND cp.usecounts>4
ORDER BY [dbid],[Usecounts] DESC

--返回最经常被修改的100个索引，通过它们的DataBase_id、object_id、index_id和partition_number 可以找到他们是哪个数据库上的哪个索引
SELECT TOP 100 db_name(database_id) as [DB Name],
       object_name(a.object_id) as [Table Name],
       b.type,
      -- b.type_desc,
       i.name as [Index Name],
       leaf_insert_count,
       leaf_delete_count,
       leaf_update_count
FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) a
INNER JOIN sys.objects b on a.object_id = b.object_id
INNER JOIN sys.indexes i on i.object_id = a.object_id and i.index_id  = a.index_id
WHERE B.type = 'U'
ORDER BY leaf_insert_count + leaf_delete_count + leaf_update_count DESC

建议查询1.3 返回IO代价最大的语句与执行计划 

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_phys_reads,
     Execution_count, 
     a.last_execution_time,
     a.last_elapsed_time/1000/1000,
    --statement_start_offset as stmt_start_offset,
    --statement_end_offset as stmt_end_offset,
    substring(sql_text.text, (statement_start_offset/2), 
    case 
    when (statement_end_offset -statement_start_offset)/2 <=0 then 64000
    else (statement_end_offset -statement_start_offset)/2 end) as exec_statement,
    sql_text.text,
    db_name(sql_text.dbid),
    plan_text.*
from sys.dm_exec_query_stats  a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
where a.last_execution_time > getdate()-0.5
order by 
--(total_logical_reads + total_logical_writes) /Execution_count Desc
 (total_physical_reads)  Desc

--查看数据库中没有用到的索引
select db_name(database_id) as N'数据库名称',
       object_name(a.object_id,a.database_id) as N'表名',
       b.name N'索引名称',
       user_seeks N'用户索引查找次数',
       user_scans N'用户索引扫描次数',
       last_user_seek N'最后查找时间',
       last_user_scan N'最后扫描时间',
       c.[rows] as N'表中的行数'
from sys.dm_db_index_usage_stats a 
  inner join sys.indexes b  on a.object_id = b.object_id and a.index_id = b.index_id 
  inner join sysindexes  c  on c.id = b.object_id
where (user_seeks+user_scans)=0
  and c.rows > 100000
order by user_seeks,user_scans,object_name(a.object_id)

-- 计算signal wait占整wait时间的百分比
-- 指令等待 CPU 资源的时间占总时间的百分比。如果超过 25% ，说明 CPU 紧张
select convert(numeric(5,4),(sum(signal_wait_time_ms)*1.0)/sum(wait_time_ms)) *100 AS 'CPU Busy Ratio'
from Sys.dm_os_wait_stats 

-- 计算'Cxpacket'占整wait时间的百分比
-- Cxpacket:Sql Server 在处理一句代价很大的语句，要不有并行作业，要不就是没有合适的索引或筛选条件没能筛选足够的记录，使得语句要返回大量的结果，当 >5% 说明有问题
declare @Cxpacket bigint
declare @PageIOLATCH_EX bigint
declare @PageIOLATCH_SH bigint
declare @Sumwaits bigint
select @Cxpacket = wait_time_ms
from Sys.dm_os_wait_stats
where wait_type = 'Cxpacket';   
select @PageIOLATCH_EX = wait_time_ms 
  from sys.dm_os_wait_stats 
 where wait_type = 'PageIOLATCH_EX';
select @PageIOLATCH_SH = wait_time_ms 
  from sys.dm_os_wait_stats 
 where wait_type = 'PageIOLATCH_SH'; --这个值大一般表示内存不够用
select @Sumwaits = sum(wait_time_ms)
from Sys.dm_os_wait_stats
select convert(numeric(5,4),@Cxpacket/(@Sumwaits * 1.0)) *100  As 'Cxpacket Ratio%',
       convert(numeric(5,4),@PageIOLATCH_EX/(@Sumwaits * 1.0)) *100  As 'PageIOLATCH_EX Ratio%',
       convert(numeric(5,4),@PageIOLATCH_SH/(@Sumwaits * 1.0)) *100  As 'PageIOLATCH_SH Ratio%'

--性能计数对象SQLServer:SQL Statistics 下面有几个计数器，可以计算出大致的执行计划重用率。计算方法是：
--Initial Compilations = SQL Compilations/sec – SQL Re-Compilations/sec
--执行计划重用率 = （Batch request/sec – Initial Compilations/sec）/Batch requests/sec
declare @batch_requests_sec bigint;
declare @sql_compilations_sec bigint;
declare @sql_recompilations_sec bigint;
select @batch_requests_sec = cntr_value     from sys.sysperfinfo b   where b.object_name = 'SQLServer:SQL Statistics'   and counter_name = 'Batch Requests/sec'
select @sql_compilations_sec = cntr_value   from sys.sysperfinfo b   where b.object_name = 'SQLServer:SQL Statistics'   and counter_name = 'SQL Compilations/sec'
select @sql_recompilations_sec = cntr_value from sys.sysperfinfo b   where b.object_name = 'SQLServer:SQL Statistics'   and counter_name = 'SQL Re-Compilations/sec'
select 100.0*(@batch_requests_sec - (@sql_compilations_sec-@sql_recompilations_sec))/@batch_requests_sec as [SQL PLAN Reuse Ratio%]
go

select  *    
 from sys.sysperfinfo b   
 where b.object_name = 'SQLServer:Buffer Manager' 
   and counter_name  =  'Page life expectancy'

select  *   
 from sys.sysperfinfo b   
 where b.object_name = 'SQLServer:Buffer Node' 
   and counter_name  =  'Page life expectancy'

--等待内存分配的用户数目，如果大于1，一定有内存压力
select  *   
 from sys.sysperfinfo b   
 where b.object_name = 'SQLServer:Memory Manager' 
   and counter_name  =  'Memory Grants Pending'

select  *
 from sys.sysperfinfo a 
where a.object_name = 'SQLServer:Plan Cache' 
  and a.counter_name  like  'Cache Hit Ratio%'

--查询阻塞发生频率与平均阻赛时间
DECLARE @dbid int
SELECT @dbid = db_id() 
SELECT 
    dbid=database_id
    ,objectname = object_name(s.object_id) 
    ,indexname = i.name ,i.index_id
    ,partition_number
    ,row_lock_count 
    ,row_lock_wait_count 
    ,[block %] = CAST(100.0 * row_lock_wait_count/(1+row_lock_count) AS numeric(15,2)) 
    ,row_lock_wait_in_ms 
    ,[avg row lock wait in ms] = CAST(1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) AS numeric(15,2))
FROM 
sys.dm_db_index_operational_stats(@dbid,NULL,NULL,NULL) s
, sys.indexes i 
WHERE
    objectproperty(s.object_id,'IsUserTable') = 1
AND i.object_id = s.object_id
AND i.index_id = s.index_id 
ORDER BY row_lock_wait_count DESC