查询调整
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