SQL Server优化常用SQL语句

--所有没有主键的表
select name from sysobjects where xtype=‘U‘ and id not in
(
select i.parent_obj from sysobjects i
where i.xtype=‘PK‘ 
) order by name

--总计耗费CPU时间最长的查询计划
SELECT TOP 5
        total_worker_time
       ,last_worker_time
       ,max_worker_time
       ,min_worker_time
       ,SUBSTRING(st.text,(qs.statement_start_offset / 2) + 1,
                  ((CASE statement_end_offset
                      WHEN -1 THEN DATALENGTH(st.text)
                      ELSE qs.statement_end_offset
                    END - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM    sys.dm_exec_query_stats AS qs
CROSS   APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time DESC 

--逻辑读最多的语句
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
       ,(SELECT SUBSTRING(text,statement_start_offset / 2 + 1,
                          (CASE WHEN statement_end_offset=-1
                                THEN LEN(CONVERT(NVARCHAR(MAX),text)) * 2
                                ELSE statement_end_offset
                           END - statement_start_offset) / 2)
         FROM   sys.dm_exec_sql_text(sql_handle)) AS query_text
       --,plan_handle
       ,db_name(qp.dbid)
       ,qp.query_plan
FROM    sys.dm_exec_query_stats
cross apply sys.dm_exec_query_plan(plan_handle) qp
ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC

--未被使用过的索引
SELECT  o.name Object_Name     --表名
      , i.name Index_name     --索引名
      , i.Type_Desc             --是否聚集索引
      , s.user_seeks         --索引seek的次数
      , s.user_scans         --索引scan的次数
      , s.user_updates         --索引update的次数
      , s.user_lookups         --索引lookup的次数
FROM    sys.objects AS o
JOIN    sys.indexes AS i
ON      o.object_id = i.object_id
LEFT OUTER   JOIN sys.dm_db_index_usage_stats AS s
ON      i.object_id = s.object_id
        AND i.index_id = s.index_id
WHERE   o.type = ‘u‘ 
and i.type_desc <> ‘heap‘
and user_seeks=0 
and user_scans=0
ORDER BY OBJECT_NAME;