{"id":24962,"date":"2023-05-17T14:14:34","date_gmt":"2023-05-17T12:14:34","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=24962"},"modified":"2025-10-01T12:11:46","modified_gmt":"2025-10-01T10:11:46","slug":"sql-server-is-this-ad-hoc-workload-an-issue","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/","title":{"rendered":"SQL Server: Is this ad hoc workload an issue?"},"content":{"rendered":"\n<p>I ran into an interesting scenario with a customer that I want to share in this blog post.<br>This customer is running a data-loading activity on a monthly basis from his business application.<br>He did not complain about the performance, the time of this activity is quite correct.<br>But, I was asked to monitor the activity from an SQL Server point of view. The goal was to ensure that there were no obvious performance issues caused by a misconfiguration or an SQL query requiring tuning.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-some-performance-metrics\">Some performance metrics<\/h3>\n\n\n\n<p>While the workload was running I observed different things, SQL activity with <a href=\"https:\/\/github.com\/amachanic\/sp_whoisactive\" target=\"_blank\" rel=\"noreferrer noopener\">sp_whoIsActive<\/a>, memory usage by observing <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/system-dynamic-management-views\/sys-dm-os-memory-clerks-transact-sql?view=sql-server-ver16\" target=\"_blank\" rel=\"noreferrer noopener\">Memory Clerks<\/a> size, <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/performance-monitor\/sql-server-plan-cache-object?view=sql-server-ver16\" target=\"_blank\" rel=\"noreferrer noopener\">plan cache<\/a> usage, <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wait-statistics-or-please-tell-me-where-it-hurts\/\" target=\"_blank\" rel=\"noreferrer noopener\">wait statistics<\/a>, etc\u2026<\/p>\n\n\n\n<p>During the SQL activity, I did not notice any obvious performance problems.<br>Here is a view of the CPU activity. The CPU load increase is visible during data loading, as expected. Most of the time this server is idling. Yet, nothing alarming with only 15% CPU usage on average.<\/p>\n\n\n\n<p>The query used below can be found in <a href=\"https:\/\/glennsqlperformance.com\/resources\/\" target=\"_blank\" rel=\"noreferrer noopener\">Glenn Berry&#8217;s diagnostic queries<\/a>; titled &#8220;CPU Utilization History&#8221;<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"775\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history-1024x775.png\" alt=\"\" class=\"wp-image-25183\" style=\"width:712px;height:538px\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history-1024x775.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history-300x227.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history-768x581.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history.png 1058w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<p>In terms of SQL Server wait statistics, there was nothing special to report either.<br>This is one of many small samples I took. You can also do this using <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-wait-statistics-period-time\/\" target=\"_blank\" rel=\"noreferrer noopener\">Paul Randal&#8217;s script<\/a>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"206\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_wait_stats-1024x206.png\" alt=\"\" class=\"wp-image-25185\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_wait_stats-1024x206.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_wait_stats-300x60.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_wait_stats-768x154.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_wait_stats.png 1398w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<p>What comes out first is a WRITELOG wait type that corresponds to INSERT queries. This is normal. The duration of these waits is quite negligible.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-plan-cache\">Plan cache<\/h3>\n\n\n\n<p>Looking at the execution plan cache we can see the limit of the number of cached entries has been reached. It is <a href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/sql-server-plan-cache-limits\/#:~:text=the%20plan%20cache%20is%20limited%20to%20160%2C036%20total%20entries\" target=\"_blank\" rel=\"noreferrer noopener\">by default around 160,000<\/a>.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT \n\t&#x5B;objtype] AS CacheType,\n\tCOUNT_BIG(*) AS TotalPlans,\n\tAVG(CAST(&#x5B;usecounts] AS BIGINT)) AS AvgUseCount,\n\tMAX(CAST(&#x5B;usecounts] AS BIGINT)) AS MaxUseCount,\n\tSUM(CAST(&#x5B;size_in_bytes] AS BIGINT))\/1024\/1024 AS TotalSizeMB,\n\tSUM((CASE WHEN CAST(&#x5B;usecounts] AS BIGINT)=1 \n\t\tTHEN CAST(&#x5B;size_in_bytes] AS BIGINT) ELSE 0 END))\/1024\/1024 AS TotalMBforUSECount1,\n\tSUM(CASE WHEN CAST(&#x5B;usecounts] AS BIGINT)=1 THEN 1 ELSE 0 END) AS TotalPlansUSECount1\nFROM sys.dm_exec_cached_plans\nGROUP BY objtype\nORDER BY TotalMBforUSECount1 DESC;\n<\/pre><\/div>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"989\" height=\"272\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_plan_cache.jpg\" alt=\"\" class=\"wp-image-25184\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_plan_cache.jpg 989w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_plan_cache-300x83.jpg 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_plan_cache-768x211.jpg 768w\" sizes=\"auto, (max-width: 989px) 100vw, 989px\" \/><\/figure>\n<\/div>\n\n\n<p>These are almost all execution plans of Ad hoc queries executed only once.<\/p>\n\n\n\n<p>The memory size consumed by these plans is reduced (166MB) because the option &#8220;<a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/database-engine\/configure-windows\/optimize-for-ad-hoc-workloads-server-configuration-option?view=sql-server-ver16\" target=\"_blank\" rel=\"noreferrer noopener\">Optimize for ad-hoc workloads<\/a>&#8221; is enabled at the instance level.<br>So at least there is no issue with significant memory pressure on the data cache (Buffer Pool).<\/p>\n\n\n\n<p>In scenarios like this, the query_hash is useful. A query_hash is a calculated value that identifies queries that differ only in literal values. We can count and identify the queries bloating the cache.<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT query_hash, COUNT(query_hash) AS PlanCount\nFROM sys.dm_exec_query_stats\nGROUP BY query_hash\nORDER BY PlanCount DESC\n<\/pre><\/div>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"343\" height=\"360\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_adhoc_query_hash.jpg\" alt=\"\" class=\"wp-image-25182\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_adhoc_query_hash.jpg 343w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_adhoc_query_hash-286x300.jpg 286w\" sizes=\"auto, (max-width: 343px) 100vw, 343px\" \/><\/figure>\n<\/div>\n\n\n<p>We can see that about ten unparameterized queries are filling the execution plan cache.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-compilation-time\">Compilation time<\/h3>\n\n\n\n<p>Memory considerations aside, the disadvantage of Ad hoc plans polluting the cache is the compilation cost. There is a CPU cost associated with generating the execution plans. It can also take time, up to several hundred milliseconds for very complex queries. <\/p>\n\n\n\n<p>There SQL queries polluting the cache and running over and over by the data loading activity are very simple queries. I looked at the query text of each of them using the query_hash value. <\/p>\n\n\n\n<p>Their compilation time is very short, between 1 and 3 milliseconds.<br>You can get your query compilation time from various places, if you are interested; <a href=\"https:\/\/erikdarlingdata.com\/are-long-compile-times-bringing-you-down\/\" target=\"_blank\" rel=\"noreferrer noopener\">from the Query Store<\/a>, <a href=\"https:\/\/erikdarlingdata.com\/capturing-query-recompilations-with-sp_humanevents\/\" target=\"_blank\" rel=\"noreferrer noopener\">Extended Events<\/a>, and <a href=\"https:\/\/www.scarydba.com\/2021\/05\/10\/query-compile-time\/\" target=\"_blank\" rel=\"noreferrer noopener\">execution plans<\/a>.<\/p>\n\n\n\n<p>From what I observed with Perfmon while the activity was running, the number of SQL compilations was about 250 per second.<br>Considering an average of 1.5 milliseconds of compilation time, this gives <code>250*1.5ms\/4 CPU cores<\/code>: 93 milliseconds are spent each second by the CPUs to perform compilations.<br>We end up with 9% of CPU time for compilations (based on this high estimate of 1.5ms on average). This seems consistent with the average CPU time of the instance at 15% as seen previously.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-parametrization\">Parametrization<\/h3>\n\n\n\n<p>Pollution of the plan cache can be avoided by updating the application code to use a method that parametrizes queries instead of using literal values. This can be difficult, expensive and take a few months depending on whether your application is developed in-house or you depend on a vendor.<\/p>\n\n\n\n<p>Another option is to enable the <a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/relational-databases\/query-processing-architecture-guide?view=sql-server-ver16#forced-parameterization\" target=\"_blank\" rel=\"noreferrer noopener\">Forced parametrization<\/a> at the database level. The downside is that obviously it could introduce parameter-sensitivy problems. Forced parametrization is <a href=\"https:\/\/www.brentozar.com\/archive\/2018\/09\/can-forced-parameterization-go-wrong\/\" target=\"_blank\" rel=\"noreferrer noopener\">Forced Parameter Sniffing<\/a>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-conclusion\">Conclusion<\/h3>\n\n\n\n<p>In this particular case, I didn&#8217;t found that the plan cache saturation is causing a significant negative impact on performance.<br>The data loading activity being ocasional and the customer being satisfied with the loading time, I did not perform any actions on this environment.<br>For cases with a high number of query compilation and where lowering CPU utilization and response time is critical for your workload, updating the code to make queries parametrized or using force parameterization can help.<\/p>\n\n\n\n<p>Written by <a href=\"https:\/\/www.linkedin.com\/in\/steven-naudet-aa540158\/\">Steven Naudet<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I ran into an interesting scenario with a customer that I want to share in this blog post.This customer is running a data-loading activity on a monthly basis from his business application.He did not complain about the performance, the time of this activity is quite correct.But, I was asked to monitor the activity from an [&hellip;]<\/p>\n","protected":false},"author":26,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[99],"tags":[2958,2957,67,51],"type_dbi":[],"class_list":["post-24962","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-ad-hoc-workload","tag-parametrization","tag-performance","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server: Is this ad hoc workload an issue? - dbi Blog<\/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.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server: Is this ad hoc workload an issue?\" \/>\n<meta property=\"og:description\" content=\"I ran into an interesting scenario with a customer that I want to share in this blog post.This customer is running a data-loading activity on a monthly basis from his business application.He did not complain about the performance, the time of this activity is quite correct.But, I was asked to monitor the activity from an [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-05-17T12:14:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-10-01T10:11:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1058\" \/>\n\t<meta property=\"og:image:height\" content=\"801\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"SQL Server: Is this ad hoc workload an issue?\",\"datePublished\":\"2023-05-17T12:14:34+00:00\",\"dateModified\":\"2025-10-01T10:11:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/\"},\"wordCount\":723,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/05\\\/Blog36_CPU_history-1024x775.png\",\"keywords\":[\"ad-hoc workload\",\"parametrization\",\"Performance\",\"SQL Server\"],\"articleSection\":[\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/\",\"name\":\"SQL Server: Is this ad hoc workload an issue? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/05\\\/Blog36_CPU_history-1024x775.png\",\"datePublished\":\"2023-05-17T12:14:34+00:00\",\"dateModified\":\"2025-10-01T10:11:46+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/05\\\/Blog36_CPU_history-1024x775.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2023\\\/05\\\/Blog36_CPU_history-1024x775.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/sql-server-is-this-ad-hoc-workload-an-issue\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server: Is this ad hoc workload an issue?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/microsoft-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server: Is this ad hoc workload an issue? - dbi Blog","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.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server: Is this ad hoc workload an issue?","og_description":"I ran into an interesting scenario with a customer that I want to share in this blog post.This customer is running a data-loading activity on a monthly basis from his business application.He did not complain about the performance, the time of this activity is quite correct.But, I was asked to monitor the activity from an [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/","og_site_name":"dbi Blog","article_published_time":"2023-05-17T12:14:34+00:00","article_modified_time":"2025-10-01T10:11:46+00:00","og_image":[{"width":1058,"height":801,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history.png","type":"image\/png"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"SQL Server: Is this ad hoc workload an issue?","datePublished":"2023-05-17T12:14:34+00:00","dateModified":"2025-10-01T10:11:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/"},"wordCount":723,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history-1024x775.png","keywords":["ad-hoc workload","parametrization","Performance","SQL Server"],"articleSection":["SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/","name":"SQL Server: Is this ad hoc workload an issue? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history-1024x775.png","datePublished":"2023-05-17T12:14:34+00:00","dateModified":"2025-10-01T10:11:46+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history-1024x775.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2023\/05\/Blog36_CPU_history-1024x775.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-is-this-ad-hoc-workload-an-issue\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server: Is this ad hoc workload an issue?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/24962","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=24962"}],"version-history":[{"count":31,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/24962\/revisions"}],"predecessor-version":[{"id":40626,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/24962\/revisions\/40626"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=24962"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=24962"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=24962"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=24962"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}