SqlServer性能优化 即席查询（十三）

SqlServer性能优化 即席查询（十三）

执行计划，查询类别：

1.即席查询     2.预定义查询

select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Sizefrom Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey where p.Size>'1'--查询执行计划是否被缓存 select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as ccross apply sys.dm_exec_sql_text(c.plan_handle) as tdbcc freeproccache--清空执行计划--没有join 的形式会生成简单参数化select EnglishProductName,Color,Size from Product where size>'1'--简单参数化select EnglishProductName,Color,Size from Product where size>'2'--简单参数化

select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Sizefrom Product as p inner join ProductCategory as c on p.aProductSubcategoryKey=c.ProductCategoryKey where p.Size>'2'

语句一样时即席查询才会重用执行计划。

优化：打开开关

exec sp_configure 'show advanced options',1reconfigure with override

为ad hoc的查询优化：

exec sp_configure 'Optimize for ad hoc workloads',1reconfigure with override

--使用参数化alter database HRDBset Parameterization forced

set Parameterization forced 强制参数化（like无法识别 ）

select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Sizefrom Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey where p.Size>'2'select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Sizefrom Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey where p.Size like '2%'

预定义查询：

预定义查询--参数化执行计划：

存储过程：

1.创建时延时检查

2.第一次执行时编译并生成执行计划

3.减少网络传输量

4.封装变化点

5.增强安全性，隔离访问控制

创建存储过程：

create procedure p_querycp @size varchar(500)asselect c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.sizefrom Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKeywhere p.Size>@size

做跟踪(以前有对应得截图)：

执行存储过程：

create procedure p_querycp @size varchar(500)asselect c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.sizefrom Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKeywhere p.Size>@size--清空执行计划dbcc freeproccache--执行exec p_querycp '1'

执行重复的语句：

dbcc freeproccacheexec p_querycp @size='1'exec p_querycp @size='2'

查看缓存计划：

select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as ccross apply sys.dm_exec_sql_text(c.plan_handle) as t

预定义查询---参数化执行计划：

SP_ExecuteSql

避免了自己维护存储过程管理成本

可重用执行计划

Unicode字符串作为参数值与类型

大小写敏感

把存储过程定义成传递参数的：

declare @sqltext nvarchar(500)set @sqltext=N'select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.sizefrom Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKeywhere p.Size>@size'declare @params nvarchar(500)set @params=N'@size varchar(500)' exec sp_executesql @sqltext,@params,@size='1'

把size 的大小换成 2

在-中调用：（两种写法）

public object getCp(string size) { HRUser dbcontext = new HRUser(); var cps = from p in dbcontext.Product join c in dbcontext.ProductCategory on p.ProductSubcategoryKey equals c.ProductCategoryKey where p.Size == size //返回匿名对象 select new { CName = c.EnglishProductCategoryName, PName = p.EnglishProductName, Color = p.Color, Size = p.Size }; return cps.ToList(); } public object getcp(string size) { HRUser dbcontext = new HRUser(); var cps = dbcontext.Product.Join(dbcontext.ProductCategory, a => a.ProductSubcategoryKey, ar => ar.ProductCategoryKey, (a, ar) => new { CName = ar.EnglishProductCategoryName, PName = a.EnglishProductName, Color = a.Color, Size = a.Size }).Where(p => p.Size == size); return cps.ToList(); }

页面：

protected void Button2_Click(object sender, EventArgs e) { Product p = new Product(); var cps = p.getCp(TextBox1.Text.Trim()); GridView1.DataSource = cps; GridView1.DataBind(); }

--动态构建语句（执行带参数的方法） declare @size varchar(500)set @size='2'execute('select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.sizefrom Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKeywhere p.Size>'+''''+@size+'''')

dbcc freeproccache--执行计划 缓存select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as ccross apply sys.dm_exec_sql_text(c.plan_handle) as t

形成两个缓存计划：

创建存储过程：

create procedure p_querye @vacationhours intasselect e.LoginID,e.JobTitle from EmployeeOp as e where VacationHours>@vacationhours

exec p_querye 2--实际执行计划 表扫描exec p_querye 99--实际执行计划 表扫描 应用用索引更好

--重新编译的执行计划exec p_querycp 99 with recompile

手工的指定执行几乎：

--手工的指定执行计划exec sp_create_plan_guide @name='执行任务计划指南之EmployeeOp Vacation',--转成Unicode编码格式@stmt=N'select e.LoginID,e.JobTitle from EmployeeOp as e where VacationHours>@vacationhours',@type=N'Object',--执行计划的名字@module_or_batch ='p_querye',@params =null,--提示@hints =N'OPTION(OPTIMIZE FOR(@vacationhours=''99''))'

清除执行计划：执行（会参考上面指定的执行计划）

exec p_querye 2

存储过程重编译：

临时结果集：

定义跟踪的模板：

一：使用物理表进行临时结果集

--1.做一张物理表create procedure p_physicaltbasCREATE TABLE PhysicalTB( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [LineTotal] [numeric](38, 6) NOT NULL, [rowguid] [uniqueidentifier] NOT NULL, [ModifiedDate] [datetime] NOT NULL) insert into PhysicalTB select * from OrderDetailselect * from PhysicalTBset statistics time onexec p_physicaltb --cpu:391 total:1762set statistics time off

跟踪的情况：

删除之后再次创建执行。

物理表每次执行都会有重编译的过程（不建议使用物理表来存储临时结果集）

第二种方式：

临时表存储临时结果集

create procedure p_temptbasCREATE TABLE #PhysicalTB( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [LineTotal] [numeric](38, 6) NOT NULL, [rowguid] [uniqueidentifier] NOT NULL, [ModifiedDate] [datetime] NOT NULL) insert into #PhysicalTB select * from OrderDetailselect * from #PhysicalTBdrop table #PhysicalTBset statistics time onexec p_temptb --cpu:110 total:1494set statistics time offsp_helpdb 'tempdb'--16064,768

第一次执行时重新编译，第二次就不会重新编译了。

用到了tempdb临时表：

第三种方式：表变量存储临时结果集

--表变量存储临时结果集create procedure p_tabletbas--申明表变量declare @PhysicalTB table( [SalesOrderID] [int] NOT NULL, [SalesOrderDetailID] [int] NOT NULL, [CarrierTrackingNumber] [nvarchar](25) NULL, [OrderQty] [smallint] NOT NULL, [ProductID] [int] NOT NULL, [SpecialOfferID] [int] NOT NULL, [UnitPrice] [money] NOT NULL, [UnitPriceDiscount] [money] NOT NULL, [LineTotal] [numeric](38, 6) NOT NULL, [rowguid] [uniqueidentifier] NOT NULL, [ModifiedDate] [datetime] NOT NULL) insert into @PhysicalTB select * from OrderDetailselect * from @PhysicalTBset statistics time onexec p_tabletb --cpu:110 total:1494set statistics time offsp_helpdb 'tempdb'--17064,768

执行时不会重新编译

第四种方式：

--CTE(通用表表达式)存储临时结果集 完全放在内存中 不会操作任何数据库中的东西create procedure p_ctetbasbegin--会自动推断数据类型;with PhysicalTB( [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty] , [ProductID], [SpecialOfferID], [UnitPrice] , [UnitPriceDiscount] , [LineTotal] , [rowguid] , [ModifiedDate] ) as (select * from OrderDetail)--访问通用表表达式select * from PhysicalTBend

跟踪的结果：

没有重新编译的过程，纯粹操作内存。tempdb数据库不会有任何的变化。

set statistics time onexec p_ctetb --cpu:100 total:1300set statistics time offsp_helpdb 'tempdb'--17064,768

高版本的通用表达式可以进行多次的使用：

create procedure p_ctetb1asbegin;with PhysicalTB( [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty] , [ProductID], [SpecialOfferID], [UnitPrice] , [UnitPriceDiscount] , [LineTotal] , [rowguid] , [ModifiedDate] ) as (select * from OrderDetail)select * from PhysicalTBselect * from PhysicalTBendexec p_ctetb1

08之前的数据库，只要把表达式在创建一次就可以了

临时数据集的优化处理：

优化查询：编译指南。

--清空执行计划dbcc freeproccacheselect * from EmployeeOp where VacationHours>1 option(use plan N'