SQL优化：基本概念（查询调整、资源调控）

查询调整

 

create table txt(id numeric(10,0) primary key,
                 v varchar(20),
                 vv int )

create index txt_v on txt(v)

insert into txt
select object_id,
       type_desc,
       schema_id
from sys.objects
where LEN(type_desc) < 20


/*=================================================
1.1动态sql语句很容易导致SQL注入，与存储过程不同的是，
动态sql、常规即席查询在每次执行时都会生成新的执行计划，
所以查询的性能不稳定.
===================================================*/
exec ('select * from txt');


/*===============================================
1.2创建可重复使用的、只有查询参数不同的查询计划,
来处理动态sql的性能问题.

参数是类型安全的,不能以非指定的数据类型使用,
也就是参数类型与字段类型必须一致,否则会报错.
=================================================*/
exec sp_executesql 
	@statement = N'select * 
	               from txt 
	               where id > @id and
	                     vv > @vv',
	                    
	@params    = N'@id numeric(10,0),  
	               @vv int',    --定义为@id int时会报错
	@id = 10000,
	@vv = 2



--2.强制SQL Server使用特定的查询计划
set statistics xml on

select * 
from txt 
where id > 1000 and
	  vv > 2

set statistics xml off

select *
from txt
option(
use plan
'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" 
Version="1.1" Build="10.50.1600.1">
<BatchSequence><Batch><Statements>
<StmtSimple StatementText="SELECT * FROM [txt] WHERE [id]>@1 AND [vv]>@2" 
StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.00429444" 
StatementEstRows="42.2945" StatementOptmLevel="TRIVIAL" QueryHash="0xA4E0AA4B0A87F88B" 
QueryPlanHash="0x3325250D8A42F500">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" 
ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/>
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="1" CompileCPU="1" 
CompileMemory="136"><RelOp NodeId="0" 
PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" 
EstimateRows="42.2945" EstimateIO="0.00386574" EstimateCPU="0.0004287" 
AvgRowSize="34" EstimatedTotalSubtreeCost="0.00429444" 
TableCardinality="292" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList><ColumnReference Database="[test2]" 
Schema="[dbo]" Table="[txt]" Column="id"/><ColumnReference 
Database="[test2]" Schema="[dbo]" Table="[txt]" Column="v"/>
<ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" Column="vv"/>
</OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" 
ActualRows="5" ActualEndOfScans="1" ActualExecutions="1"/>
</RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" 
ForcedIndex="0" ForceSeek="0" NoExpandHint="0"><DefinedValues>
<DefinedValue><ColumnReference Database="[test2]" 
Schema="[dbo]" Table="[txt]" Column="id"/>
</DefinedValue><DefinedValue><ColumnReference 
Database="[test2]" Schema="[dbo]" Table="[txt]" 
Column="v"/></DefinedValue><DefinedValue>
<ColumnReference Database="[test2]" Schema="[dbo]" 
Table="[txt]" Column="vv"/></DefinedValue></DefinedValues>
<Object Database="[test2]" Schema="[dbo]" Table="[txt]" 
Index="[PK__txt__3213E83F4D1564AE]" IndexKind="Clustered"/>
<SeekPredicates><SeekPredicateNew><SeekKeys>
<StartRange ScanType="GT"><RangeColumns>
<ColumnReference Database="[test2]" Schema="[dbo]" Table="[txt]" 
Column="id"/></RangeColumns><RangeExpressions><ScalarOperator 
ScalarString="CONVERT_IMPLICIT(numeric(10,0),[@1],0)"><Identifier>
<ColumnReference Column="ConstExpr1003"><ScalarOperator>
<Convert DataType="numeric" Precision="10" Scale="0" Style="0" Implicit="1">
<ScalarOperator><Identifier><ColumnReference Column="@1"/>
</Identifier></ScalarOperator></Convert></ScalarOperator>
</ColumnReference></Identifier></ScalarOperator>
</RangeExpressions></StartRange></SeekKeys></SeekPredicateNew>
</SeekPredicates><Predicate><ScalarOperator 
ScalarString="[test2].[dbo].[txt].[vv]>CONVERT_IMPLICIT(int,[@2],0)"><Compare CompareOp="GT">
<ScalarOperator><Identifier><ColumnReference Database="[test2]" Schema="[dbo]" 
Table="[txt]" Column="vv"/></Identifier></ScalarOperator><ScalarOperator>
<Identifier><ColumnReference Column="ConstExpr1004"><ScalarOperator>
<Convert DataType="int" Style="0" Implicit="1"><ScalarOperator>
<Identifier><ColumnReference Column="@2"/></Identifier>
</ScalarOperator></Convert></ScalarOperator></ColumnReference>
</Identifier></ScalarOperator></Compare></ScalarOperator>
</Predicate></IndexScan></RelOp><ParameterList>
<ColumnReference Column="@2" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)"/>
<ColumnReference Column="@1" ParameterCompiledValue="(1000)" 
ParameterRuntimeValue="(1000)"/></ParameterList></QueryPlan>
</StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>')



--3.不修改应用程序，应用查询提示
exec sp_create_plan_guide
	@name = 'plan_guide_txt',
	@stmt = 'select * 
	         from txt 
	         inner join sys.objects o
	         on o.object_id = txt.id',
	@type = 'sql',
	@module_or_batch = null,
	@params = null,
	@hints  = 'option(merge join)'


--当下面的这段代码与上面的代码完全一样(包括空格,回车)时,系统才会应用查询提示	
select * 
	         from txt 
	         inner join sys.objects o
	         on o.object_id = txt.id
	         

	
--4.从缓存创建计划指南
begin
select * 
from txt 
inner join sys.objects o
on o.object_id = txt.id

select * 
from sys.objects
end
go

--找到计划句柄
select e.text,
       d.statement_start_offset,
       d.plan_handle   --计划句柄       
from sys.dm_exec_query_stats d
cross apply sys.dm_exec_sql_text(d.sql_handle)e
where e.text like '%from txt%' 


/*========================================================
从计划句柄建立计划指南

参数@statement_start_offset：在批处理中可能会有多条语句，
此参数指出批处理中创建计划指南的语句，在批处理中的开始偏移。
如果指定为NULL，那么会为批处理中的所有语句建立计划指南

==========================================================*/
exec sp_create_plan_guide_from_handle
	@name = 'plan_guide_txt_handle',
	@plan_handle = 0x060018007BA32F074021425A020000000000000000000000,
	@statement_start_offset = 14  
	


--5.使用计划指南把非参数化查询参数化
--5.1运行多条类似的查询
select * from txt where id = 8

select * from txt where id = 9

--5.2通过查询缓存的计划所对应的sql文本，发现很多都是相同的
--而且大部分的objtype都是proc,adhoc,prepared.
SELECT * 
FROM SYS.dm_exec_cached_plans E
CROSS APPLY SYS.dm_exec_sql_text(E.plan_handle)EE
WHERE EE.text LIKE '%select * from txt where id =%'


declare @sqltext nvarchar(max)
declare @parameter nvarchar(max)

--5.3获取查询的参数化形式以及查询的参数,放入变量中
exec sp_get_query_template 
	@querytext = N'select * from txt where id = 8',
	@templatetext= @sqltext output,
	@parameters = @parameter output


--5.4使用模板来创建计划指南	
exec sp_create_plan_guide
	@name = 'plan_guide_txt_template',
	@stmt = @sqltext,
	@type = 'template',  
	@module_or_batch = null,
	@params = @parameter,
	@hints = 'option(parameterization forced)' 
	

--5.5再次查询发现多条执行计划已经变为一条,usecounts计数增加
SELECT * 
FROM SYS.dm_exec_cached_plans E
CROSS APPLY SYS.dm_exec_sql_text(E.plan_handle)EE
WHERE EE.text LIKE '%select * from txt where id =%'



--6.显示计划指南的元数据
select p.plan_guide_id,
       p.name,
       p.create_date,
       p.modify_date,
       
       p.is_disabled,  --是否禁用计划指南
       p.query_text,   --创建计划指南所依据的查询文本
       
       p.scope_type,       --3个类型:object,sql,template
       p.scope_type_desc,
       
       p.scope_object_id , --如果类型为object,那么指出对象id
       
       scope_batch,        --如果scope_type为SQL，则为批处理文本。
                           --如果批处理类型不是SQL，则其值为NULL

       p.parameters,       --定义与计划指南关联的参数列表的字符串
       
       p.hints             --与计划指南关联的 OPTION 子句提示       
from sys.plan_guides p



/*=============================================================
7.验证指定计划指南的有效性。返回计划指南应用于其查询时遇到的第一条错误消息。

如果计划指南有效，则将返回一个空的行集。在更改数据库的物理设计后，计划指南可能会变为无效。
例如，如果计划指南指定了特定索引并且随后将该索引删除，则查询将不能再使用该计划指南。

通过验证计划指南，可确定优化器是否能够在不进行修改的情况下直接使用该指南。
例如，基于函数的结果，可决定删除该计划指南并重新调整查询或修改数据库设计,
例如，重新创建计划指南中指定的索引。
===============================================================*/
select pp.msgnum,     --错误消息的 ID
       pp.severity,   --消息的严重级别
       pp.state,      --错误的状态号，用于指示发生错误的代码位置
       pp.message     --错误的消息正文
from sys.plan_guides p
cross apply sys.fn_validate_plan_guide(p.plan_guide_id) pp



/*==============================================
8.删除或者禁用计划指南

disable,disable all:禁用(所有)计划指南
  enable,enable all:启用(所有)计划指南
      drop,drop all:删除(所有)计划指南
      
注意：当指定all时，不能指定计划指南名称。
================================================*/
exec sp_control_plan_guide
	@operation = 'disable',    --禁用计划指南
	@name = 'plan_guide_txt'
	

--删除所有计划指南
exec sp_control_plan_guide
	@operation = 'drop all'

 

资源调控

 在SQL Server 2008中引入了使用资源调控器来限制工作负荷的资源消耗。在SQL Server内部包含了两个资源池：默认和内部。内部资源池不能修改，并且使SQL Server的活动不受资源的限制。默认资源池可以把连接请求，连接、配置资源调控器，默认情况下没有限制，之后可以修改。

 

一个或多个工作组可以绑定到一个资源池，使用工作负荷组可以定义资源池中重要的请求、最大授予内存比、最大以秒为单位的cpu时间、最大授予内存超时时间、最大并行度、同时执行的请求的最大数量。工作负荷组也包含：内部和默认工作组。默认工作组用在没有被任何分类器用户定义函数覆盖的任何请求上。多个工作负荷组可以关联到一个资源池，但是一个工作负荷组不能关联到多个资源池。

在创建用户定义工作负荷组和绑定到资源池后，可以创建一个帮助确定：进入的SQL Server连接和请求属于哪个工作负荷组的分类器用户定义函数。

 

--1.创建资源池
--创建应用程序资源池
create resource pool app_query
with	
(
	MIN_CPU_PERCENT = 25,
	MAX_CPU_PERCENT = 75,
	
	MIN_MEMORY_PERCENT = 25,
	MAX_MEMORY_PERCENT = 75
)


--创建即席查询资源池
CREATE RESOURCE POOL adhoc_query
with
(
	min_cpu_percent = 5,
	max_cpu_percent = 25,
	
	min_memory_percent = 5,
	max_memory_percent = 25
)


--修改即席查询资源池
alter resource pool adhoc_query
with
(
	min_memory_percent = 10,
	max_memory_percent = 50
)


--2.创建工作负荷组
--创建应用程序a的工作负荷组
create workload group app_a
with
(
	importance = high,
		
	request_max_memory_grant_percent = 75,
	request_max_cpu_time_sec = 75,
	request_memory_grant_timeout_sec = 120,
	
	max_dop = 8,
	group_max_requests = 8
)
using app_query   --使用应用程序资源池


--创建应用程序b的工作负荷组
create workload group app_b
with
(
	importance = low,
	
	request_max_memory_grant_percent = 50,
	request_max_cpu_time_sec = 50,
	request_memory_grant_timeout_sec = 360,
	
	max_dop = 1,
	group_max_requests = 4
)
using app_query   --使用应用程序资源池


--创建即席查询工作负荷组
create workload group adhoc_user
with
(
	importance = low,
	
	request_max_memory_grant_percent = 100,
	request_max_cpu_time_sec = 120,
	request_memory_grant_timeout_sec = 360,
	
	max_dop = 1,
	group_max_requests = 5
)
using adhoc_query


--修改工作负荷组
alter workload group app_b
with 
(
	importance = medium
)



--3.分类器函数必须在master数据库中创建
use master
go

--返回工作负荷组名称，系统会根据工作组名称，将连接定位到工作组所对应的资源池
create function dbo.wc_classifier()
returns sysname
with schemabinding
as
begin
	declare @resource_group_name sysname
	
	if SUSER_SNAME() in ('sa')
		set @resource_group_name = 'app_a'
	
	if SUSER_SNAME() in ('sa')
		set @resource_group_name = 'app_b'
		
	if HOST_NAME() in ('abc')
		set @resource_group_name = 'adhoc_query'
	
	if @resource_group_name is null
		set @resource_group_name = 'default'
	
	return @resource_group_name
			
end
go


--设置资源调度器的分类器函数，并且重新配置
alter resource governor
with
(
	classifier_function = dbo.wc_classifier
)
go

--为了启用配置，需要执行reconfigure
alter resource governor reconfigure
go



--4.查看元数据
--资源池的元数据
select pool_id,

       min_cpu_percent,
       max_cpu_percent,
       
       min_memory_percent,
       max_memory_percent
from sys.resource_governor_resource_pools


--负荷工作组元数据
select group_id,
	   name,
	   
	   pool_id,
	   
	   importance,	   
	   request_max_memory_grant_percent,
	   request_max_cpu_time_sec,
	   request_memory_grant_timeout_sec,
	   
	   max_dop,
	   group_max_requests	          
from sys.resource_governor_workload_groups


--显示资源调控器的配置信息,包含了:分类器函数,是否启用
select classifier_function_id,

       OBJECT_NAME(classifier_function_id,
                   DB_ID('master')
                  ),         --函数名
       is_enabled            --是否启用
from sys.resource_governor_configuration



--5.删除资源池，工作负荷组，分类器函数
use master
go

drop workload group app_a
drop workload group app_b
drop workload group adhoc_user

drop resource pool adhoc_query
drop resource pool app_query


--禁用设置
alter resource governor disable

--设置不在使用分类器函数
alter resource governor 
with
(
	classifier_function = null
)

--删除分类器函数
drop function dbo.wc_classifier