{"id":1058,"date":"2016-01-29T15:34:21","date_gmt":"2016-01-29T23:34:21","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=1058"},"modified":"2020-01-12T11:03:39","modified_gmt":"2020-01-12T19:03:39","slug":"sql-server-diagnostic-information-queries-detailed-day-30","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/","title":{"rendered":"SQL Server Diagnostic Information Queries Detailed, Day 30"},"content":{"rendered":"

For Day 30 of this series, we start out with Query #67<\/strong>, which is UDF Statistics. This query retrieves information from the sys.dm_exec_function_stats<\/a> 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.<\/p>\n

\n
\n
   1:<\/span> -- Look at UDF execution statistics (Query 67) (UDF Statistics)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> OBJECT_NAME(object_id) AS<\/span> [Function<\/span> Name], execution_count,<\/pre>\n
<\/p>\n
   3:<\/span>    total_elapsed_time\/1000 AS<\/span> [time_milliseconds], fs.[type_desc]<\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> sys.dm_exec_function_stats AS<\/span> fs WITH<\/span> (NOLOCK) <\/pre>\n
<\/p>\n
   5:<\/span> WHERE<\/span> database_id = DB_ID()<\/pre>\n
<\/p>\n
   6:<\/span> ORDER<\/span> BY<\/span> OBJECT_NAME(object_id) OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   7:<\/span>  <\/pre>\n
<\/p>\n
   8:<\/span> -- New for SQL Server 2016<\/span><\/pre>\n
<\/p>\n
   9:<\/span> -- Helps you investigate UDF performance issues<\/pre>\n
<\/div>\n<\/div>\n
Figure 1: Query #67 UDF Statistics<\/strong><\/p>\n
One fairly well-known, long-running issue<\/a> 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. <\/p>\n
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.<\/p>\n
 <\/p>\n
Query #68<\/strong> is QueryStore Options. This query retrieves information from the sys.database_query_store_options<\/a> dynamic management view about the current QueryStore options for the current database. Query #68 is shown in Figure 2.<\/p>\n
\n
\n
   1:<\/span> -- Get QueryStore Options for this database (Query 68) (QueryStore Options)<\/span><\/pre>\n
<\/p>\n
   2:<\/span> SELECT<\/span> actual_state, actual_state_desc, readonly_reason, <\/pre>\n
<\/p>\n
   3:<\/span>        current_storage_size_mb, max_storage_size_mb<\/pre>\n
<\/p>\n
   4:<\/span> FROM<\/span> sys.database_query_store_options WITH<\/span> (NOLOCK)<\/pre>\n
<\/p>\n
   5:<\/span> OPTION<\/span> (RECOMPILE);<\/pre>\n
<\/p>\n
   6:<\/span>  <\/pre>\n
<\/p>\n
   7:<\/span> -- New for SQL Server 2016<\/span><\/pre>\n
<\/p>\n
   8:<\/span> -- Requires that QueryStore is<\/span> enabled for<\/span> this database<\/pre>\n
<\/div>\n<\/div>\n
Figure 2: Query #68 QueryStore Options<\/strong><\/p>\n
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 \u201cgood\u201d 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\u2013only feature or not. Before you can use QueryStore, you have to enable it for the database that you are concerned with.<\/p>\n
  <\/p>\n
These three Pluralsight Courses go into even more detail about how to run these queries and interpret the results. <\/p>\n
\n
SQL Server 2014 DMV Diagnostic Queries \u2013 Part 1<\/a><\/p>\n<\/blockquote>\n
\n
SQL Server 2014 DMV Diagnostic Queries \u2013 Part 2<\/a><\/p>\n<\/blockquote>\n
\n
SQL Server 2014 DMV Diagnostic Queries \u2013 Part 3<\/a><\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[272],"class_list":["post-1058","post","type-post","status-publish","format-standard","hentry","category-dmv-diagnostic-queries-detailed","tag-dmv-diagnostic-queries-detailed"],"yoast_head":"\nSQL Server Diagnostic Information Queries Detailed, Day 30 - Glenn Berry<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Diagnostic Information Queries Detailed, Day 30 - Glenn Berry\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/\" \/>\n<meta property=\"og:site_name\" content=\"Glenn Berry\" \/>\n<meta property=\"article:published_time\" content=\"2016-01-29T23:34:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-12T19:03:39+00:00\" \/>\n<meta name=\"author\" content=\"Glenn Berry\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Glenn Berry\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/\",\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 30 - Glenn Berry\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\"},\"datePublished\":\"2016-01-29T23:34:21+00:00\",\"dateModified\":\"2020-01-12T19:03:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Diagnostic Information Queries Detailed, Day 30\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\",\"name\":\"Glenn Berry\",\"description\":\"Semi-random musings about SQL Server performance\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7\",\"name\":\"Glenn Berry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g\",\"caption\":\"Glenn Berry\"},\"sameAs\":[\"https:\/\/www.sqlskills.com\/blogs\/glenn\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Diagnostic Information Queries Detailed, Day 30 - Glenn Berry","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Diagnostic Information Queries Detailed, Day 30 - Glenn Berry","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/","og_site_name":"Glenn Berry","article_published_time":"2016-01-29T23:34:21+00:00","article_modified_time":"2020-01-12T19:03:39+00:00","author":"Glenn Berry","twitter_misc":{"Written by":"Glenn Berry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/","name":"SQL Server Diagnostic Information Queries Detailed, Day 30 - Glenn Berry","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website"},"datePublished":"2016-01-29T23:34:21+00:00","dateModified":"2020-01-12T19:03:39+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/sql-server-diagnostic-information-queries-detailed-day-30\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/glenn\/"},{"@type":"ListItem","position":2,"name":"SQL Server Diagnostic Information Queries Detailed, Day 30"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/","name":"Glenn Berry","description":"Semi-random musings about SQL Server performance","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/glenn\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/57a8972435106bac7970692fcf5edfa7","name":"Glenn Berry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/glenn\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64bdac8830f25f2f8cc780f8a1286c66ff1182218009271e7a953639596f7e25?s=96&d=mm&r=g","caption":"Glenn Berry"},"sameAs":["https:\/\/www.sqlskills.com\/blogs\/glenn\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/glenn\/author\/glenn\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1058","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/comments?post=1058"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/posts\/1058\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/media?parent=1058"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/categories?post=1058"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/glenn\/wp-json\/wp\/v2\/tags?post=1058"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}