数据库优化及找出最消耗资源的查询

转载

wx63914693c5388 2022-12-08 10:20:48

数据库优化是为了提高数据库的存取及访问速度，这其中包括了表结构的优化、索引的优化、查询字符串的优化等。

这里，我将介绍的是，在规范的按照第三范式建表的基础上进行的索引的优化，这里主要介绍两种方法：

1、通过SQL管理查询分析器的“工具”的“SQL Server Profiler。”保存下一段时间内，Sql的执行日志，然后通过“工具”里的“数据库引擎优化顾问”来分析出需要优化的索引，然后将索引新增到相应的表中

2、通过以下的查询语句，查询出最消耗资源的查询语句，并根据索引优化建议对索引进行优化

SQL Server 2008中有很多用来记录查询统计信息的DMV，然而单个的DMV给出的信息并不直观，需要一定的加工来提高可读性。每个DBA都会编写符合自己使用习惯的脚本，下面这个脚本是我用来找出消耗资源（逻辑读）最多的查询，配合where子句中的条件，可以筛选包含指定字符串、指定对象类型的查询，还可以忽略那些消耗资源不够多、执行次数不多、最近没有执行的查询。脚本返回的结果集，包含可读性更高的统计信息，和执行计划：

select 
    highest_cpu_queries.plan_handle,    
    highest_cpu_queries.total_worker_time,   
    q.dbid,   
    q.objectid,   
    q.number,   
    q.encrypted,   
    q.[text]   
from 
    (select top 50    
        qs.plan_handle,    
        qs.total_worker_time   
    from 
        sys.dm_exec_query_stats qs   
    order by qs.total_worker_time desc) as highest_cpu_queries   
    cross apply sys.dm_exec_sql_text(plan_handle) as q   
order by highest_cpu_queries.total_worker_time desc

或者

/*
根据下面产生的数据，观察最消耗资源的操作，并通过query_plan的提示来优化数据库
*/

with QS as(
  select cp.objtype as object_type
      ,db_name(st.dbid)as [database]
      ,object_schema_name(st.objectid,st.dbid)as [schema]
      ,object_name(st.objectid,st.dbid)as [object]
      ,convert(char(16),qs.creation_time,120)as plan_creation
      ,convert(char(16),qs.last_execution_time,120)as last_execution
      ,qs.plan_generation_num
      ,qs.execution_count
      ,qs.total_worker_time
      ,qs.total_physical_reads
      ,qs.total_logical_writes
      ,qs.total_logical_reads
      ,qs.total_elapsed_time/(1000000*qs.execution_count)as avg_elapesd_seconds
      ,qs.total_worker_time/qs.execution_count as avg_cpu_cost
      ,qs.total_logical_reads/qs.execution_count as avg_logical_reads
      ,qs.total_logical_writes/qs.execution_count as avg_logical_writes
      ,qs.total_physical_reads/qs.execution_count as avg_physical_reads
      ,st.text
      ,qp.query_plan
  from sys.dm_exec_query_stats qs
     join sys.dm_exec_cached_plans cp on cp.plan_handle=qs.plan_handle
     cross apply sys.dm_exec_sql_text(sql_handle)as st
     cross apply sys.dm_exec_query_plan(qs.plan_handle)as qp
  where 1=1
  --and cp.objtype='Proc'         --对象类型
  --and db_name(st.dbid)='GprsRun'    --数据库
  --and st.text not like '%时间%' and st.text not like '%@queryStr%'    --查询字符串
  --and qs.execution_count>100     --执行次数
  --and qs.total_worker_time>100     --cpu总时间  
  --and qs.total_physical_reads>100    --物理读次数
  --and qs.total_logical_writes>100    --逻辑写次数
  --and qs.total_logical_reads>100   --逻辑读次数
)

select *, '执行次数最多的' type from (select top 5 * from QS order by execution_count desc)a       --执行次数最多的
union all
select *, '执行时间最长的' type from (select top 5 * from QS order by total_worker_time desc)a       --执行时间最长的
union all
select *, '物理读次数最多的' type from (select top 5 * from QS order by total_physical_reads desc)a     --物理读次数最多的
union all
select *, '逻辑写次数最多的' type from (select top 5 * from QS order by total_logical_writes desc)a     --逻辑写次数最多的
union all
select *, '逻辑读次数最多的' type from (select top 5 * from QS order by total_logical_reads desc)a      --逻辑读次数最多的
union all
select *, '平均cpu时间最长的' type from (select top 5 * from QS order by avg_cpu_cost desc)a         --平均cpu时间最长的
union all
select *, '平均逻辑读最多的' type from (select top 5 * from QS order by avg_logical_reads desc)a        --平均逻辑读最多的
union all
select *, '平均逻辑写最多的' type from (select top 5 * from QS order by avg_logical_writes desc)a     --平均逻辑写最多的
union all
select *, '平均物理写最多的' type from (select top 5 * from QS order by avg_physical_reads desc)a     --平均物理写最多的
order by text