数据库性能调优查询

step1:===查看缺失的索引

SELECT t4.name,t1.[statement],t1.object_id, t2.user_seeks, t2.user_scans,

       t1.equality_columns, t1.inequality_columns,t1.included_columns,

       case 

          --when t1.equality_columns is null and charindex(',',t1.inequality_columns)=0 and t1.included_columns is null

          --    then   'create UNIQUE NONCLUSTERED INDEX IX_' + replace((replace((replace(t1.[statement],'[','_')),']','_')),'.','_') +'_'+ replace((replace((replace(isnull(t1.equality_columns,'1'),'[','_')),']','_')),'.','_') +'_' 

          --           +replace((replace((replace(isnull(t1.inequality_columns,'_2'),'[','_')),']','_')),'.','_') + ' ON '+ t1.[statement] + ' (' + t1.inequality_columns + ' ASC )'  

          when --t1.equality_columns is null and charindex(',',t1.inequality_columns)>0 and

              t1.included_columns is null

              then   'create  NONCLUSTERED INDEX IX_' + replace((replace((replace((replace(t1.[statement],'[','_')),']','_')),'.','_')),',','_') +'_'  

                     +replace(replace(replace(replace(replace(isnull(t1.equality_columns,'2'),' [',''),'[',''),'.',''),',',''),']','')

                     +replace((replace((replace((replace(isnull(t1.inequality_columns,'2'),'[','')),']','')),'.','')),',','_') + ' ON '+ t1.[statement] + ' (' + 

                       case  when t1.equality_columns is null then ' '

                             when charindex(',',t1.equality_columns)=0 then t1.equality_columns +' ASC '

                             when charindex(',',t1.equality_columns)>0 then replace(t1.equality_columns,',',' ASC,') + ' ASC ' 

                        end

                          +   

                       case  when charindex(',',t1.inequality_columns)=0 then  ' ,'+t1.inequality_columns + ' ASC )'

                             when  t1.inequality_columns is null then ' )'

                             when charindex(',',t1.inequality_columns)>0 then ' ,'+ replace(t1.inequality_columns,',',' ASC,') + ' ASC )' 

                        end

          when t1.included_columns is not null

               then   'create NONCLUSTERED INDEX IX_' + replace((replace((replace((replace(t1.[statement],'[','_')),']','_')),'.','_')),',','_') +'_'  

                     +replace(replace(replace(replace(replace(isnull(t1.equality_columns,'2'),' [',''),'[',''),'.',''),',',''),']','')

                     +replace((replace((replace((replace(replace(isnull(t1.inequality_columns,'2'),' [',''),'[','')),']','')),'.','')),',','_') + ' ON '+ t1.[statement] + ' (' + 

                       case  when t1.equality_columns is null then ' '

                             when charindex(',',t1.equality_columns)=0 then t1.equality_columns +' ASC '

                             when charindex(',',t1.equality_columns)>0 then replace(t1.equality_columns,',',' ASC,') + ' ASC ' 

                        end

                          +   

                       case  when t1.equality_columns is null then ''+t1.inequality_columns + ' ASC )'

                             when charindex(',',t1.inequality_columns)=0 then ' ,'+t1.inequality_columns + ' ASC )'

                             when  t1.inequality_columns is null then ' )'

                             when charindex(',',t1.inequality_columns)>0 then ' ,'+ replace(t1.inequality_columns,',',' ASC,') + ' ASC )' 

                        end

                     + ' INCLUDE ( ' + t1.included_columns + ' )'

 

       end  as  '建立索引的语句'

 

 

      FROM sys.dm_db_missing_index_groups AS t3

      join sys.dm_db_missing_index_details AS t1

       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

              join sys.databases AS t4 

                on t1.database_id = t4.database_id

      WHERE t1.database_id = DB_ID() --AND object_id = OBJECT_ID('dealer.升级码_经销商添加升级码')

      order by t2.user_seeks desc 

 

 

step2：===查看锁的语句

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

with C as

(

SELECT DB_NAME(resource_database_id) AS DatabaseName 

, request_session_id 

, resource_type 

, CASE 

WHEN resource_type = 'OBJECT' 

THEN OBJECT_NAME(resource_associated_entity_id) 

WHEN resource_type IN ('KEY', 'PAGE', 'RID') 

THEN (SELECT OBJECT_NAME(OBJECT_ID) 

FROM sys.partitions p 

WHERE p.hobt_id = l.resource_associated_entity_id) 

END AS resource_type_name 

, request_status 

, request_mode 

FROM sys.dm_tran_locks l 

WHERE request_session_id !=@@spid 

)

 select C.DatabaseName,C.request_session_id,C.request_mode,C.request_status,C.resource_type,

       C.resource_type_name,b.loginame,b.program_name,m.plan_handle,f.text,gp.query_plan

 

 from C join sys.sysprocesses as b 

      on C.request_session_id = b.spid  

 

      join sys.dm_exec_query_stats as m 

 

      on b.sql_handle  = m.sql_handle

 

      CROSS APPLY sys.dm_exec_sql_text(b.sql_handle) as f

 

      CROSS APPLY sys.dm_exec_query_plan(m.plan_handle) as gp

 

 

where resource_type <> 'database'    ORDER BY request_session_id

 

step3:===最占I/O的20个语句和他们的执行计划

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT TOP 20 

  [Total IO] = (qs.total_logical_reads + qs.total_logical_writes) 

  , [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) / 

                                            qs.execution_count 

  , qs.execution_count 

  , 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) + 1) AS [Individual Query] 

  , qt.text AS [Parent Query] 

  , DB_NAME(qt.dbid) AS DatabaseName 

  , qp.query_plan 

FROM sys.dm_exec_query_stats qs 

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 

ORDER BY [Total IO] DESC    

 

step4===查看没关闭事务的空闲进程,当前未完成的事务

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

with C as

(

SELECT es.session_id, es.login_name, es.host_name, est.text 

  , cn.last_read, cn.last_write, es.program_name 

FROM sys.dm_exec_sessions es 

INNER JOIN sys.dm_tran_session_transactions st 

            ON es.session_id = st.session_id 

INNER JOIN sys.dm_exec_connections cn 

            ON es.session_id = cn.session_id 

CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est 

LEFT OUTER JOIN sys.dm_exec_requests er                     

            ON st.session_id = er.session_id 

                AND er.session_id IS NULL  

)

 

 select [session_id],[login_name],[host_name],[text],[last_read],[last_write],[program_name] from C

 

 

step5:===查看cpu占用

select * from sys.sysprocesses order by cpu desc

 

 

select * from sys.sysprocesses as s

cross apply sys.dm_exec_sql_text(s.sql_handle) as t order by cpu desc

 

 

查询CPU占用高的语句

 

 

 

SELECT TOP 10

   total_worker_time/execution_count AS avg_cpu_cost, plan_handle,

   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

FROM sys.dm_exec_query_stats

ORDER BY [avg_cpu_cost] DESC