Troubleshoot SQL Server Function Performance with the sys.dm_exec_function_stats DMV
By: Daniel Farina | Updated: 2015-11-11 | Comments (1) | Related: More > SQL Server 2016
You need to collect execution statistics for a function. In previous versions of SQL Server the only way to obtain these statistics was by looking at individual statements within the function. In this tip I will introduce a new system view that will give us the execution statistics of the whole function.
Starting with version 2005, SQL Server added system views that collect statistics for query executions. This was improved on SQL Server 2008 with the addition of new Dynamic Management Views to keep track of stored procedures (sys.dm_exec_procedure_stats) and triggers (sys.dm_exec_trigger_stats). Now with SQL Server 2016 we have another improvement, a new Dynamic Management View that returns performance statistics of cached functions. The new system view I am referring is sys.dm_exec_function_stats.
SQL Server sys.dm_exec_function_stats Dynamic Management View
This sys.dm_exec_function_stats Dynamic Management View keeps execution statistics for scalar functions including both in-memory and CLR scalar functions. The view returns one row for every scalar function as long as it has a cached execution plan. Itís not surprising, because the same happens with the similar system views like sys.dm_exec_query_stats.
Considerations for In-Memory Functions
When you look at the execution statistics of In-Memory functions, you wonít be able to get information on the columns related to logical or physical IO. Instead those columns will be set to zero. On the contrary you will be able to query for worker statistics and the number of executions.
For the purposes of this test I will be using the Adventure Works 2014 sample database which you can download for free from Codeplex by following this link: https://msftdbprodsamples.codeplex.com/releases/view/125550.
I made two simple queries; the first one contains scalar function calls, and the other joins a table with a table valued function. This way we can see how the sys.dm_exec_function_stats displays its execution statistics.
Here is the query that calls two Scalar Functions: dbo.ufnGetProductListPrice and dbo.ufnGetStock, both from AdventureWorks2014 database.
USE AdventureWorks2014 GO SELECT OH.PurchaseOrderNumber , dbo.ufnGetProductListPrice(OD.ProductID, OH.OrderDate) ListPrice , OD.UnitPRice , OD.OrderQty , OD.LineTotal , dbo.ufnGetStock(OD.ProductID) RemainingStock FROM Sales.SalesOrderHeader OH INNER JOIN Sales.SalesOrderDetail OD ON OH.SalesOrderID = OD.SalesOrderID GO
The following query execution will create an entry in cache for the dbo.ufnGetContactInformation Table Valued Function of the AdventureWorks 2014 database, but as I previously explained, it wonít show a record in the sys.dm_exec_function_stats system view.
USE AdventureWorks2014 GO SELECT * FROM Sales.SalesOrderHeader OH CROSS APPLY dbo.ufnGetContactInformation(OH.SalesPersonID) GO
After executing the previous scripts we are now ready to query the sys.dm_exec_function_stats Dynamic Management View. The next query will return the fully qualified function name and its maximum times as well as the function code.
USE MASTER GO SELECT DB_NAME(database_id) + '.' + OBJECT_SCHEMA_NAME(OBJECT_ID, database_id) + '.' + OBJECT_NAME(OBJECT_ID, database_id) AS Function_Name, QS.last_execution_time , QS.max_worker_time , QS.max_physical_reads , QS.max_logical_reads , QS.max_logical_writes , T.Text FROM sys.dm_exec_function_stats QS CROSS APPLY sys.dm_exec_sql_text(sql_handle) T
As you can see on the next two images querying sys.dm_exec_function_stats only shows the statistics of the two scalar functions from the first query.
But you may be asking, what if I need execution statistics of Table Valued Functions. In such case, since sys.dm_exec_function_stats is very similar to sys.dm_exec_query_stats, we can adapt the previous query to use the sys.dm_exec_query_stats system view.
USE MASTER GO SELECT DB_NAME(T.dbid) + '.' + OBJECT_SCHEMA_NAME(T.objectid, T.dbid) + '.' + OBJECT_NAME(T.objectid, T.dbid) AS Function_Name, QS.last_execution_time , QS.max_worker_time , QS.max_physical_reads , QS.max_logical_reads , QS.max_logical_writes , T.Text FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(sql_handle) T WHERE T.dbid = DB_ID('AdventureWorks2014')
After executing the previous query you should see something similar to the next images. Notice that the result shows details about the execution statistics for the dbo.ufnGetContactInformation Table Valued Function as well as the two scalar functions.
- You can download an evaluation copy of SQL Server 2016 from this link: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016.
- Also you can download the AdventureWorks2014 database as well as other test databases from this link: https://msftdbprodsamples.codeplex.com/releases/view/125550.
- Since sys.dm_exec_function_stats is very similar to sys.dm_exec_procedure_stats and sys.dm_exec_query_stats you can reuse the queries of those system views with little effort. Take this tip as an example: Several Methods to collect SQL Server Stored Procedure Execution History
- If you need more information about Dynamic Management Views check out Dynamic Management Views and Functions Tips Category.
- You can find more tips about Performance Tuning on Performance Tuning Tips Category.
- Stay tuned on SQL Server 2016 Tips Category for more information about this release of SQL Server.
Last Updated: 2015-11-11
About the author
View all my tips