For Day 30 of this series, we start out with Query #67, which is UDF Statistics. This query retrieves information from the sys.dm_exec_function_stats dynamic management view about aggregate runtime metrics for user-defined functions in the current database, ordered by object name. Query #67 is shown in Figure 1.
1: -- Look at UDF execution statistics (Query 67) (UDF Statistics)
2: SELECT OBJECT_NAME(object_id) AS [Function Name], execution_count,
3: total_elapsed_time/1000 AS [time_milliseconds], fs.[type_desc]
4: FROM sys.dm_exec_function_stats AS fs WITH (NOLOCK)
5: WHERE database_id = DB_ID()
6: ORDER BY OBJECT_NAME(object_id) OPTION (RECOMPILE);
7:
8: -- New for SQL Server 2016
9: -- Helps you investigate UDF performance issues
Figure 1: Query #67 UDF Statistics
One fairly well-known, long-running issue with SQL Server is how often you see performance problems with scalar, user-defined functions (UDFs). As a DBA, I always tried to avoid having my developers ever find out that scalar UDFs even existed, but they sometimes discovered them on their own, unfortunately. One big problem with scalar UDFs is that their actual cost does not show up when you look at the execution plan or statistics IO output for the query or stored procedure that called them.
In the past, you needed to use tools like SQL Profiler or Extended Events to see what was going on when you had scalar UDF usage. In SQL Server 2016, you will be able to use this new DMV and this query to have some visibility about what your UDFs are doing. As far as mitigation goes, doing things like converting a scalar UDF to a table UDF that just returns just one column and one row, or converting it to a stored procedure are often pretty effective and easy to do.
Query #68 is QueryStore Options. This query retrieves information from the sys.database_query_store_options dynamic management view about the current QueryStore options for the current database. Query #68 is shown in Figure 2.
1: -- Get QueryStore Options for this database (Query 68) (QueryStore Options)
2: SELECT actual_state, actual_state_desc, readonly_reason,
3: current_storage_size_mb, max_storage_size_mb
4: FROM sys.database_query_store_options WITH (NOLOCK)
5: OPTION (RECOMPILE);
6:
7: -- New for SQL Server 2016
8: -- Requires that QueryStore is enabled for this database
Figure 2: Query #68 QueryStore Options
QueryStore is one of the more exciting new features in SQL Server 2016. It gives you a lot of visibility about what is happening with your query plans in a particular database over time. You also get the ability to force the query optimizer to use a particular “good” query plan. This should make it much easier to troubleshoot and correct plan regression issues. So far, Microsoft has not announced whether this is an Enterprise Edition–only feature or not. Before you can use QueryStore, you have to enable it for the database that you are concerned with.
These three Pluralsight Courses go into even more detail about how to run these queries and interpret the results.