SqlServer性能优化 即席查询（十三）
生活随笔
收集整理的這篇文章主要介紹了
SqlServer性能优化 即席查询（十三）
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
執行計劃，查詢類別：
1.即席查詢 2.預定義查詢
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size from 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 c cross apply sys.dm_exec_sql_text(c.plan_handle) as t dbcc 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.Size from Product as p inner join ProductCategory as c on p.aProductSubcategoryKey= c.ProductCategoryKey where p.Size>'2'
語句一樣時即席查詢才會重用執行計劃。
優化：打開開關
exec sp_configure 'show advanced options',1 reconfigure with override
為ad hoc的查詢優化：
exec sp_configure 'Optimize for ad hoc workloads',1 reconfigure with override
--使用參數化 alter database HRDB set Parameterization forced
set Parameterization forced 強制參數化（like無法識別 ）
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size from 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.Size from 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) as select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey where p.Size>@size
做跟蹤(以前有對應得截圖)：
執行存儲過程：
create procedure p_querycp @size varchar(500) as select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey where p.Size>@size --清空執行計劃 dbcc freeproccache --執行 exec p_querycp '1'
執行重復的語句：
dbcc freeproccache exec 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 c cross 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.size from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey where p.Size>@size ' declare @params nvarchar(500) set @params=N'@size varchar(500)' exec sp_executesql @sqltext,@params,@size='1'
把size 的大小換成 2
在.net中調用：（兩種寫法）
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();
}
頁面：
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="顯示產品" />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
點擊后的事件：
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.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>'+''''+@size+'''')
dbcc freeproccache --執行計劃 緩存 select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as c cross apply sys.dm_exec_sql_text(c.plan_handle) as t
形成兩個緩存計劃：
創建存儲過程：
create procedure p_querye @vacationhours int as select 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_physicaltb as CREATE 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 OrderDetail select * from PhysicalTB set statistics time on exec p_physicaltb --cpu:391 total:1762 set statistics time off
跟蹤的情況：
刪除之后再次創建執行。
物理表每次執行都會有重編譯的過程（不建議使用物理表來存儲臨時結果集）
第二種方式：
臨時表存儲臨時結果集
create procedure p_temptb as CREATE 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 OrderDetail select * from #PhysicalTB drop table #PhysicalTB set statistics time on exec p_temptb --cpu:110 total:1494 set statistics time off sp_helpdb 'tempdb'--16064,768
第一次執行時重新編譯，第二次就不會重新編譯了。
用到了tempdb臨時表：
第三種方式：表變量存儲臨時結果集
--表變量存儲臨時結果集 create procedure p_tabletb as --申明表變量 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 OrderDetail select * from @PhysicalTB set statistics time on exec p_tabletb --cpu:110 total:1494 set statistics time off sp_helpdb 'tempdb'--17064,768
執行時不會重新編譯
第四種方式：
--CTE(通用表表達式)存儲臨時結果集 完全放在內存中 不會操作任何數據庫中的東西 create procedure p_ctetb as begin --會自動推斷數據類型 ;with PhysicalTB( [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty] , [ProductID], [SpecialOfferID], [UnitPrice] , [UnitPriceDiscount] , [LineTotal] , [rowguid] , [ModifiedDate] ) as (select * from OrderDetail) --訪問通用表表達式 select * from PhysicalTB end
跟蹤的結果：
沒有重新編譯的過程，純粹操作內存。tempdb數據庫不會有任何的變化。
set statistics time on exec p_ctetb --cpu:100 total:1300 set statistics time off sp_helpdb 'tempdb'--17064,768
高版本的通用表達式可以進行多次的使用：
create procedure p_ctetb1 as begin ;with PhysicalTB( [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty] , [ProductID], [SpecialOfferID], [UnitPrice] , [UnitPriceDiscount] , [LineTotal] , [rowguid] , [ModifiedDate] ) as (select * from OrderDetail) select * from PhysicalTB select * from PhysicalTB end exec p_ctetb1
08之前的數據庫，只要把表達式在創建一次就可以了
臨時數據集的優化處理：
優化查詢：編譯指南。
--清空執行計劃
dbcc freeproccache
select * from EmployeeOp where VacationHours>1 option(use plan N'
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="12.0.2000.8">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select * from EmployeeOp where VacationHours>99" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00657038" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0x7E06C77E90EB9FBB" QueryPlanHash="0x64478FC6152D2A83" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120" ParameterizedText="(@1 tinyint)SELECT * FROM [EmployeeOp] WHERE [VacationHours]>@1">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="40" CompileTime="6" CompileCPU="6" CompileMemory="232">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="206857" EstimatedPagesCached="51714" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="830" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BusinessEntityID" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="NationalIDNumber" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="LoginID" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationNode" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationLevel" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="JobTitle" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BirthDate" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="MaritalStatus" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="Gender" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="HireDate" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SalariedFlag" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SickLeaveHours" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="CurrentFlag" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="rowguid" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="ModifiedDate" />
</OutputList>
<NestedLoops Optimized="0">
<OuterReferences>
<ColumnReference Column="Bmk1000" />
</OuterReferences>
<RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SickLeaveHours" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SickLeaveHours" />
</DefinedValue>
</DefinedValues>
<Object Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Index="[nc_Employee_vacationsickleave]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(99)">
<Const ConstValue="(99)" />
</ScalarOperator>
</RangeExpressions>
</StartRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<RelOp NodeId="3" PhysicalOp="RID Lookup" LogicalOp="RID Lookup" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="826" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
<OutputList>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BusinessEntityID" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="NationalIDNumber" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="LoginID" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationNode" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationLevel" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="JobTitle" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BirthDate" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="MaritalStatus" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="Gender" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="HireDate" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SalariedFlag" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="CurrentFlag" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="rowguid" />
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="ModifiedDate" />
</OutputList>
<IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BusinessEntityID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="NationalIDNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="LoginID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationNode" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationLevel" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="JobTitle" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BirthDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="MaritalStatus" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="Gender" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="HireDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SalariedFlag" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="CurrentFlag" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="rowguid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="ModifiedDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" TableReferenceId="-1" IndexKind="Heap" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Column="Bmk1000" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Bmk1000]">
<Identifier>
<ColumnReference Column="Bmk1000" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<ParameterList>
<ColumnReference Column="@1" ParameterCompiledValue="(99)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
') --表掃描
select * from sys.dm_exec_cached_plans
select * from sys.dm_exec_sql_text
select * from sys.dm_exec_query_plan
select * from EmployeeOp where VacationHours>99 -- 索引
select c.plan_handle,p.text from sys.dm_exec_cached_plans as c cross apply sys.dm_exec_sql_text(c.plan_handle) as p
select * from sys.dm_exec_query_plan(0x06000A00CD253E14207CA0290200000001000000000000000000000000000000000000000000000000000000)
總結
以上是生活随笔為你收集整理的SqlServer性能优化 即席查询（十三）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 【Linux基础】crontab定时命令
- 下一篇： SSM+mybatis单元测试