德瑞克：SQL Server 學習筆記: 2018/07

2018-07-30

TSQL: Look at parameter from execution plan, 查看執行計畫所使用的參數

延續先前文章 SSMS: Look at parameter from execution plan, 查看執行計畫所使用的參數

在對 Stored Procedure 執行 Performance Optimization (效能優化)時，若能知道編譯時期的 Parameter Compiled Value，將能夠精準有效率的執行優化任務。

可使用以下方式查詢：Parameter Compiled Value

使用 SSMS
使用 DMV: sys.dm_exec_query_plan

使用 SSMS 觀察 Execution Plan，可以看到

Column
Parameter Compiled Value
Parameter Data Type
Parameter Runtime Value

使用 DMV: sys.dm_exec_query_plan 的資料行: query_plan，可以觀察 ParameterList Element(元素)：

Column: 傳入的 Parameter 名稱。
ParameterDataType: 使用的資料類型。
ParameterCompiledValue: 編譯 Execution Plan 的值。

TSQL: Look at parameter from execution plan, 查
看執行計畫所使用的參數

01. 使用以下範例，其中：

[dbo].[uspGetWhereUsedProductID] 使用的參數是：

@StartProductID=819
@CheckDate='2010-12-23'

-- figure 01_TSQL_Sample

02. 使用以下語法查詢取得 last performance statistics for cached query:

-- Get last performance statistics for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, etc.
SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)],
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)],
 qs.last_logical_reads LastLogicalReads,
 qs.last_physical_reads LastPhysicalRead,
 qs.last_logical_writes LastlogicalWrites,
 qs.last_rows LastRows,
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;
GO

-- figure 11_Get last performance statistics for cached query

03. 檢視查詢結果，可以看到：[ParameterList]。

考量會傳入多個參數值，採用 XML 格式呈現。

-- figure 12_last performance statistics_ParameterList

04. 檢查 ParameterList 的 XML 格式，可以觀察'：

Column: 傳入的 Parameter 名稱。
ParameterDataType: 使用的資料類型。
ParameterCompiledValue: 編譯 Execution Plan 的值。

Column="@CheckDate" ParameterDataType="datetime" ParameterCompiledValue="'2010-12-23 00:00:00.000'"
Column="@StartProductID" ParameterDataType="int" ParameterCompiledValue="(819)"

-- figure 13_Review_ParameterCompiledValue

05. Get performance statistics for cached query - Full

-- Get performance statistics for cached query - Full
/*
sys.dm_exec_query_stats - Returns aggregate performance statistics
An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. 
More accurate results may be determined by rerunning the query.

total_elapsed_time: Total elapsed time, reported in microseconds (but only accurate to milliseconds), 
for completed executions of this plan.
*/

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], cp.usecounts [ExecutionCount],
 qs.total_elapsed_time/cp.usecounts/1000000.0 [AvgElapsedTime(sec)], 
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)], qs.max_elapsed_time/1000000.0 [MaxElapsedTime(sec)], 
 qs.min_elapsed_time/1000000.0 [MinElapsedTime(sec)], qs.total_elapsed_time/1000000.0 [TotalElapsedTime(sec)],
 qs.total_worker_time/cp.usecounts/1000000.0 [AvgCPUTime(sec)], 
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)], qs.max_worker_time/1000000.0 [MaxCPUTime(sec)], 
 qs.min_worker_time/1000000.0 [MinCPUTime(sec)], qs.total_worker_time/1000000.0 [TotalCPUTime(sec)], 
 qs.total_logical_reads/cp.usecounts [AvgLogicalRead], 
 qs.last_logical_reads, qs.max_logical_reads, qs.min_logical_reads, qs.total_logical_reads, 
 qs.last_physical_reads [LastPhysicalRead], qs.max_physical_reads, qs.min_physical_reads, qs.total_physical_reads,  
 qs.total_logical_writes/cp.usecounts [AvgLogicalWrite], qs.last_logical_writes,
 qs.max_logical_writes, qs.min_logical_writes, qs.total_logical_writes,
 qs.last_rows [LastRows], qs.max_rows, qs.min_rows, qs.total_rows,
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList], 
 qp.query_plan [QueryPlan], cp.plan_handle, cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 st.objectid, qs.last_execution_time, qs.creation_time --qs.*
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;
GO

/*
CPU bound - ORDER BY qs.total_worker_time , [AvgElapsedTime(sec)]
I/O bound - ORDER BY qs.total_logical_reads , [AvgLogicalRead]
*/

-- figure 21_Get performance statistics for cached query

06. 檢視回傳的結果：

-- figure 22_Get performance statistics for cached query

07. Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, etc.

-- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, etc.
SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList], 
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY [ExecutionCount] DESC;
GO

-- figure 31_Get execution plan for cached query

08. 檢視回傳的結果：

-- figure 32_Get execution plan for cached query_ParameterList

09. -- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.

-- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 TRY_CAST(qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') 
 AS decimal(38,7)) [StatementSubTreeCost],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@RetrievedFromCache)[1]', 'nvarchar(50)') [RetrievedFromCache],
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014') 
-- AND cp.objtype = 'Proc' CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY StatementSubTreeCost DESC;
GO

-- figure 41_Get execution plan for cached query

-- figure 42_Get execution plan for cached query_ParameterList

Sample Code

20180730_Look at parameter_TSQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

Reference

[SQL Server] SSMS: Look at parameter from execution plan, 查看執行計畫所使用的參數
http://sharedderrick.blogspot.com/2017/12/sql-server-look-at-parameter-from.html

2018-07-24

[Performance Tuning] Parallel INSERT … SELECT with TABLOCK hint

SQL Server 2016 新增加 INSERT … SELECT with TABLOCK hint，支援 Parallel 平行處理來提升效能。

先前版本，雖然 TABLOCK，採取減少 Log 量與 Lock 方式來提升效能。但在 SQL Server 2016 再強化為支援 Parallel 平行處理來優化效能。

基本規則是

資料表沒有 Clustered Index，也就是 Heap(堆積)。
資料庫的 COMPATIBILITY_LEVEL (相容性) = 130 含以上。
INSERT ... SELECT 使用 TABLCOK。

Parallel INSERT … SELECT with TABLOCK hint

Table Without Clustered Index.
The database compatibility level must be 130 and above.
Must use the TABLOCK hint with the INSERT … SELECT statement.

使用 TABLOCK，效能提升：71.5% 。

(0.061514000-0.105527000)/0.061514000 = 71.5%

TSQL	LastElapsedTime(sec)
#th1：with TABLOCK	0.061514000
#th2：without TABLOCK	0.105527000
#th3_ClusteredIndex：with TABLOCK, and with Clustered Index	0.123723000

[Performance Tuning] Parallel INSERT … SELECT with TABLOCK hint

01. 建立 temporary table (暫存資料表)。

#th1 : 沒有 Clustered Index，也就是 Heap (堆積)。
#th2 : 沒有 Clustered Index，也就是 Heap (堆積)。

-- figure 01_Create Table_without_Clustered Index

02. 建立 temporary table (暫存資料表)。

#th3_ClusteredIndex : 有 Clustered Index。
TransactionID 資料行，設定為 Clustered Index，也是 Primary Key。

-- figure 02_Create Table With Clustered Index

03. 新增資料列，使用 INSERT ... SELECT

#th1 : INSERT ... SELECT ，並加上使用 TABLOCK 。

Parallel Insert，平行處理。

#th2 : 僅使用 INSERT ... SELECT 。

無法 Parallel Inert，無法平行處理。

-- figure 11_WITH (TABLOCK)

04. 觀察 Execution Plan，兩相比較下

有使用 TABLOCK hint，僅占 36% 成本資源。
觀察到有 Parallelism 平行處理的圖示。

-- figure 12_Execution_Plan

05. 使用 Plan Explorer 來做觀察：

有使用 TABLOCK，Cost 僅使用 36%，Parallel Operations 是 4 。
沒用 TABLOCK，Cost 多耗用至 63.8%，Parallel Operations 是 1 。

-- figure 13_Execution_Plan_Plan_Explorer

06. 進一步觀察 Execution Plan:

有使用 TABLOCK，Subtree Cost 是: 3.34141。
沒用 TABLOCK，Subtree Cost 高達: 5.89468。

-- figure 14

-- figure 15

07. 觀察 Table Insert operator:

有使用 TABLOCK，可以 Parallel 平行處理。

留意：Number of Executions 是：4。

沒有 TABLOCK，不支援 Parallel 平行處理。

留意：Number of Executions 是：1。

-- figure 16_Table_Insert

-- figure 17_Table_Insert

08. 觀察 Performance Data：

使用 TABLOCK，效能提升：71.5% 。
Improvement = (Original_Response_Time - Improved_Response_Time) / Original_Response_Time * 100%
(0.061514000-0.105527000)/0.061514000 = 71.5%

TSQL	LastElapsedTime(sec)
#th1：with TABLOCK	0.061514000
#th2：without TABLOCK	0.105527000
#th3_ClusteredIndex：with TABLOCK, and with Clustered Index	0.123723000

-- figure 18_Performance_Data

09. 比較以下 INSERT ... SELECT ：

#th1：with TABLOCK

可平行處理。Parallel Insert

#th2：without TABLOCK

無法平行處理。Not Parallel

#th3_ClusteredIndex：with TABLOCK, and with Clustered Index

無法平行處理。Not Parallel

-- figure 19_Different type of INSERT ... SELECT

10. 觀察 Execution Plan

-- figure 20_Compare_3

The database compatibility level must be 130 and above.

01. 調降 database compatibility level 為 120。

-- figure 21_database compatibility level

02. 確認該資料庫是降級使用 COMPATIBILITY_LEVEL = 120

-- figure 22_COMPATIBILITY_LEVEL = 120

03. 再度執行 INSERT ... SELECT

-- figure 23_INSERT_SELECT

04. 觀察 Execution Plan:

都不支援平行處理。
Non-Parallel execution in all script.

這是由於 COMPATIBILITY_LEVEL 必須要在 130 以上才支援平行處理。

-- figure 24_Execution_Plan

大量匯入資料的最佳做法

使用 INSERT INTO…SELECT 搭配最低限度記錄來大量匯入資料
您可以搭配使用 INSERT INTO SELECT FROM 最低限度記錄，

有效率地將大量資料列從某份資料表 (例如暫存表格) 傳送至另一份資料表。

最低限度記錄可以改善此陳述式的效能並且降低交易期間作業填滿可用交易記錄空間的可能性。

此陳述式的最低限度記錄具有下列需求：

資料庫的復原模式設為 simple (簡單) 或 bulk-logged(大量記錄)。
目標資料表是空白或非空白的 heap(堆積)。
目標資料表未用於複寫。
針對目標資料表指定了 TABLOCK 提示。

由於 MERGE 陳述式中的插入動作而插入堆積的資料列也可以採用最低限度記錄。

與 BULK INSERT 陳述式 (持有較不嚴格的大量更新鎖定) 不同之處在於，
具 TABLOCK 提示的 INSERT INTO…SELECT 對資料表持有獨佔 (X) 鎖定。
這代表您無法使用平行插入作業插入資料列。

Sample Code

20180724_INSERT_SELECT_TABLOCK
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing