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

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

 

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

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_planfrom 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 qpwhere 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 allselect *, '执行时间最长的' type from (select top 5 * from QS order by total_worker_time desc)a--执行时间最长的union allselect *, '物理读次数最多的' type from (select top 5 * from QS order by total_physical_reads desc)a--物理读次数最多的union allselect *, '逻辑写次数最多的' type from (select top 5 * from QS order by total_logical_writes desc)a--逻辑写次数最多的union allselect *, '逻辑读次数最多的' type from (select top 5 * from QS order by total_logical_reads desc)a--逻辑读次数最多的union allselect *, '平均cpu时间最长的' type from (select top 5 * from QS order by avg_cpu_cost desc)a--平均cpu时间最长的union allselect *, '平均逻辑读最多的' type from (select top 5 * from QS order by avg_logical_reads desc)a--平均逻辑读最多的union allselect *, '平均逻辑写最多的' type from (select top 5 * from QS order by avg_logical_writes desc)a--平均逻辑写最多的union allselect *, '平均物理写最多的' type from (select top 5 * from QS order by avg_physical_reads desc)a--平均物理写最多的order by text