RiCo技術農場

以前版本的SQL SERVER，對於stored procedures(sys.dm_exec_procedure_stats)

和trigger(sys.dm_exec_trigger_stats)都有相關system view收集執行的效能統計資料，

方便DBA追蹤並掌握效能，現在SQL2016又新增function效能統計資料(sys.dm_exec_function_stats)，

讓DBA可以有效進行function效能問題排除。

但要注意sys.dm_exec_function_stats只返回純量值函數統計資料(包含in-memory functions and CLR scalar functions)，

且如果function不存在快取區，該system view也會刪除相關資料，

這裡我簡單使用四種純量值函示執行不同查詢，

看看sys.dm_exec_function_stats是否能正確收集相關效能統計資料。

--有I/O
create function fun_GetMaxInoviceDate(@InvoiceID int)
returns date
as
begin
declare @result date;

select @result=max(InvoiceDate)
from dbo.Invoices
where InvoiceID=@InvoiceID

return @result
end

--沒有I/O
create function fun_onSales(@value int)
returns money
as
begin
 return @value*.1
end

--in-memory function 
create function fun_GetMaxInoviceDate_native(@InvoiceID int)
returns date
with NATIVE_COMPILATION ,SCHEMABINDING
as
begin atomic with (TRANSACTION ISOLATION LEVEL = SNAPSHOT,LANGUAGE = N'English')  
declare @result date;

select @result=max(InvoiceDate)
from dbo.Invoices_memory
where InvoiceID=@InvoiceID

return @result
end

--in-memory function
create function fun_onSales_native(@value int)
returns money
with NATIVE_COMPILATION ,SCHEMABINDING 
as   
begin atomic with (TRANSACTION ISOLATION LEVEL = SNAPSHOT,LANGUAGE = N'English')  
  
  return @value*.1
  
end

--query 1
select top 1000  InvoiceID,CustomerID
,dbo.fun_GetMaxInoviceDate(InvoiceID) as [MaxInoviceDate] 
,dbo.fun_GetMaxInoviceDate_native(InvoiceID) as [MaxInoviceDate_memory] 
,dbo.fun_onSales(20)
,dbo.fun_onSales_native(20)
from Invoices

--query 2
select top 1000   InvoiceID,CustomerID
,dbo.fun_GetMaxInoviceDate(InvoiceID) as [MaxInoviceDate] 
,dbo.fun_GetMaxInoviceDate_native(InvoiceID) as [MaxInoviceDate_memory] 
,dbo.fun_onSales(20)
,dbo.fun_onSales_native(20)
from Invoices t1 join myorders t2 on t1.InvoiceID=t2.OrderID

--查詢function效能統計資料
select  DB_NAME(database_id) + '.' + 
		OBJECT_SCHEMA_NAME(OBJECT_ID, database_id) +
        '.' + OBJECT_NAME(OBJECT_ID, database_id) 
		as [Function_Name],   
		sqltext.Text,  
		funstats.cached_time,
		funstats.total_elapsed_time, 
		funstats.total_elapsed_time/funstats.execution_count AS [avg_elapsed_time],
		funstats.last_elapsed_time, 
		funstats.execution_count,
        funstats.max_worker_time ,
        funstats.max_physical_reads ,
        funstats.max_logical_reads ,
        funstats.max_logical_writes ,
		funstats.last_execution_time        
from    master.sys.dm_exec_function_stats funstats
        cross apply sys.dm_exec_sql_text(sql_handle) sqltext
where  sqltext.dbid = DB_ID('WideWorldImporters') 

fun_onSales邏輯讀取0、fun_GetMaxInoviceDate邏輯讀取3，非in-memory function看上去滿正確的，

但卻沒顯示in-memory function相關資訊，為了這問題我詢問了國外SQL專家，下面是這兩位專家的回覆。

Bob:

Have you try using sp_xtp_control_query_exec_stats or

sp_xtp_control_proc_exec_stats?

See https://msdn.microsoft.com/en-us/library/dn435917.aspx for more info.

 

Jos:

Runtime stats collection is not enabled by default, since for native execution it introduces a significant overhead. 

To enable stats collection use the procs that Bob mentioned.

USE master
GO
-- This will enable statistics collection
EXEC sys.sp_xtp_control_proc_exec_stats @new_collection_value = 1
GO

啟用後再次執行查詢，sys.dm_exec_function_stats

果然正確收集in-memory function效能統計資料。

 

Enjoy SQL Server 2016

 

參考

sys.dm_exec_function_stats

sys.sp_xtp_control_query_exec_stats (Transact-SQL)

Debugging Natively-Compiled Stored Procedures Statements