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

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

 

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

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