{"id":17208,"date":"2022-06-01T16:35:33","date_gmt":"2022-06-01T14:35:33","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=17208"},"modified":"2025-10-01T12:03:02","modified_gmt":"2025-10-01T10:03:02","slug":"sql-server-2022-parameter-sensitive-plan-optimization","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/","title":{"rendered":"SQL Server 2022 Parameter Sensitive Plan optimization"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"h-introduction\">Introduction<\/h2>\n\n\n\n<p>The Intelligent Query Processing (IQP) feature family is extended with SQL Server 2022.<br>One of the most anticipated features is the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/performance\/parameter-sensitivity-plan-optimization?view=sql-server-ver16&amp;viewFallbackFrom=sql-server-ver15\">Parameter Sensitive Plan optimization<\/a>.<\/p>\n\n\n\n<p>I started to test this new feature. In this post, you will find some information to understand how it works and make your first tests too.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-the-issue-with-parameter-sensitive-plan\">The issue with Parameter Sensitive Plan<\/h2>\n\n\n\n<p>Parameter Sensitive Plan, also known as &#8220;Parameter Sniffing&#8221; is a scenario caused by non-uniform data distribution where a single cached execution plan for a parameterized query performs poorly for some parameter values.<\/p>\n\n\n\n<p>A few options are available to deal with a Parameter Sensitive Plan query:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use the RECOMPILE query hint to force a new plan compilation for all executions<\/li>\n\n\n\n<li>Use the OPTIMIZE FOR hint to generate an execution plan for a specific parameter value<\/li>\n\n\n\n<li>Force the last known good plan with <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/performance\/monitoring-performance-by-using-the-query-store?view=sql-server-ver16\">Query Store<\/a><\/li>\n<\/ul>\n\n\n\n<p>All the methods mentioned below require manual intervention, either at the level of the SQL code to add a query hint or by a DBA to force a particular execution plan.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-new-feature-psp-optimization\">New feature: PSP Optimization<\/h2>\n\n\n\n<p>This new PSP optimization feature will be introduced with SQL Server 2022 and enabled by default with Compatibility Level 160.<\/p>\n\n\n\n<p>Even if the Query Store will be enabled by default with 2022, PSP optimization does not require to have Query Store enabled, unlike some other IQP features.<\/p>\n\n\n\n<p>This feature introduces 2 new concepts. To quote the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/performance\/parameter-sensitivity-plan-optimization?view=sql-server-ver16#psp-optimization-implementation\">documentation<\/a>:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>For eligible plans, the initial compilation produces a\u00a0<strong>dispatcher plan<\/strong>\u00a0that contains the PSP optimization logic called a dispatcher expression. A dispatcher plan maps to\u00a0<strong>query variants<\/strong>\u00a0based on the cardinality range boundary values predicates.<\/p>\n<\/blockquote>\n\n\n\n<p>The idea is as follows; an eligible query will get a dispatcher plan containing the dispatcher expression. Each significant set of parameters has its query variant, an execution plan optimized for these parameters<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1780\" height=\"555\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg\" alt=\"\" class=\"wp-image-17240\" \/><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-psp-optimization-demo\">PSP Optimization Demo<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-prerequisites\"><span style=\"text-decoration: underline\">Prerequisites<\/span><\/h3>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"h-sql-server-2022-and-compatibility-level-160\"><span style=\"text-decoration: underline\">SQL Server 2022 and Compatibility Level 160<\/span><\/h5>\n\n\n\n<p><\/p>\n\n\n\n<p>For this demo, you obviously need <a href=\"https:\/\/info.microsoft.com\/ww-landing-sql-server-2022.html\">SQL Server 2022<\/a>. I&#8217;m now using the first public preview, CTP 2.0.<br>As just mentioned above the prerequisite for this feature is the Compatibility Level 160.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"h-enable-the-feature\"><span style=\"text-decoration: underline\">Enable the feature<\/span><\/h5>\n\n\n\n<p><\/p>\n\n\n\n<p>The PSP Optimization feature is enabled by default. You can enable\/disable it with the following command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON<\/code><\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"h-reason-for-psp-optimization-being-skipped\"><span style=\"text-decoration: underline\">Reason for PSP optimization being skipped<\/span><\/h5>\n\n\n\n<p><\/p>\n\n\n\n<p>I had difficulty producing a scenario that triggers PSP optimization.<br>Using the documented XE events I found some reasons for PSP skipping my queries: <strong>SkewnessThresholdNotMet<\/strong>, <strong>UnsupportedComparisonType <\/strong>or <strong>ConjunctThresholdNotMet<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-style-default\"><img loading=\"lazy\" decoding=\"async\" width=\"787\" height=\"55\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-2.png\" alt=\"\" class=\"wp-image-17223\" \/><\/figure>\n\n\n\n<p>I do not know what these thresholds are. I just used a larger table and a simpler query for the demo.<br>There are currently 32 reasons listed in the XE &#8220;psp_skipped_reason_enum&#8221; that you can get with this query:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name, map_value\nFROM sys.dm_xe_map_values \nWHERE name ='psp_skipped_reason_enum' \nORDER BY map_key<\/code><\/pre>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"370\" height=\"482\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image.png\" alt=\"\" class=\"wp-image-17220\" \/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"h-demo\"><span style=\"text-decoration: underline\">Demo<\/span><\/h3>\n\n\n\n<p>I used the <a href=\"http:\/\/dataeducation.com\/thinking-big-adventure\/\">bigTransactionHistory<\/a> table that I slightly modified to get the following data distribution producing a parameter sniffing scenario.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"275\" height=\"93\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-9.png\" alt=\"\" class=\"wp-image-17232\" style=\"width:275px;height:93px\" \/><\/figure>\n<\/div>\n\n\n<p>I run the following query twice with different parameters and PSP optimization enabled.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXEC sp_executesql \n\tN'\n\tselect TransactionId, Quantity, ActualCost, TransactionDate\n\tfrom dbo.bigTransactionHistory\n\twhere TransactionDate = @date'\n\t, N'@date datetime'\n<mark style=\"background-color:#fcb900\" class=\"has-inline-color\">\t, '2004-06-01 00:00:00';\n<\/mark>GO\nEXEC sp_executesql \n\tN'\n\tselect TransactionId, Quantity, ActualCost, TransactionDate\n\tfrom dbo.bigTransactionHistory\n\twhere TransactionDate = @date'\n\t, N'@date datetime'\n<mark style=\"background-color:#fcb900\" class=\"has-inline-color\">\t, '2022-06-01 00:00:00';\n<\/mark>GO<\/code><\/pre>\n\n\n\n<p>I get 2 different execution plans, without forcing a Recompile or forcing a plan myself.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"alignright size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"865\" height=\"597\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-3.png\" alt=\"\" class=\"wp-image-17225\" \/><\/figure>\n<\/div>\n\n\n<p><\/p>\n\n\n\n<p>The paramter_sensitive_plan_optimization Extended Event was fired during both executions of the query. We can notice the variant_id information.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1413\" height=\"477\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-12.png\" alt=\"\" class=\"wp-image-17246\" \/><\/figure>\n<\/div>\n\n\n<p>The execution plan shows a new hint option &#8220;PLAN PER VALUE&#8221; added to the query text:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select TransactionId, Quantity, ActualCost, TransactionDate\n\tfrom dbo.bigTransactionHistory\n\twhere TransactionDate = @date <mark style=\"background-color:#fcb900\" class=\"has-inline-color\">option (PLAN PER VALUE(QueryVariantID = 1, predicate_range(&#091;AdventureWorks].&#091;dbo].&#091;bigTransactionHistory].&#091;TransactionDate] = @date, 100.0, 10000.0)))<\/mark>\nselect TransactionId, Quantity, ActualCost, TransactionDate\n\tfrom dbo.bigTransactionHistory\n\twhere TransactionDate = @date <mark style=\"background-color:#fcb900\" class=\"has-inline-color\">option (PLAN PER VALUE(QueryVariantID = 3, predicate_range(&#091;AdventureWorks].&#091;dbo].&#091;bigTransactionHistory].&#091;TransactionDate] = @date, 100.0, 10000.0)))<\/mark>\n<\/code><\/pre>\n\n\n\n<p>Based on the parameter value provided when running the query SQL Server will choose the plan to be used at runtime.<\/p>\n\n\n\n<p>There&#8217;s a new &#8220;Dispatcher&#8221; section in the XML execution plan containing the dispatcher &#8220;expression&#8221;.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1488\" height=\"722\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-5.png\" alt=\"\" class=\"wp-image-17228\" \/><\/figure>\n<\/div>\n\n\n<p>Even though the Query Store is not required for PSP to be working it is useful to have it enabled because you will get information about your query variants in a new DMV: sys.query_store_query_variant<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"412\" height=\"66\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-6.png\" alt=\"\" class=\"wp-image-17229\" \/><\/figure>\n<\/div>\n\n\n<p>The Query Store report doesn&#8217;t show an aggregated view of all variants at once. Looking for query_id 4 doesn&#8217;t show anything. That&#8217;s something that could be useful in the next versions of SSMS.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"630\" height=\"353\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-8.png\" alt=\"\" class=\"wp-image-17231\" \/><\/figure>\n<\/div>\n\n\n<p>The query_hash in DMV sys.dm_exec_query_stats is common to all variants, so it&#8217;s possible to determine aggregate resource usage for queries that differ only by input parameter values.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"1409\" height=\"101\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-7.png\" alt=\"\" class=\"wp-image-17230\" \/><\/figure>\n<\/div>\n\n\n<p>The plan cache shows the plan for each variant and the dispatcher.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n\tp.usecounts, p.cacheobjtype\n\t, p.objtype, p.size_in_bytes\n\t, t.&#091;text]\n\t, qp.query_plan\nFROM sys.dm_exec_cached_plans p\n\tCROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t\n\tCROSS APPLY sys.dm_exec_query_plan(p.plan_handle) AS qp \nWHERE t.&#091;text] like '%TransactionDate%'\n  AND p.objtype = 'Prepared'\nORDER BY p.objtype DESC<\/code><\/pre>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1103\" height=\"222\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-10.png\" alt=\"\" class=\"wp-image-17234\" style=\"width:899px;height:181px\" \/><\/figure>\n<\/div>\n\n\n<p>Although the Dispatcher plan is the largest, it only contains the XML dispatcher section mentioned above.<\/p>\n\n\n<div class=\"wp-block-image is-style-default\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"653\" height=\"218\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/image-11.png\" alt=\"\" class=\"wp-image-17235\" style=\"width:563px;height:188px\" \/><\/figure>\n<\/div>\n\n\n<h2 class=\"wp-block-heading\" id=\"h-conclusion\">Conclusion<\/h2>\n\n\n\n<p>The Parameter Sensitive Plan optimization is working as described in SQL Server 2022 CTP2.0. There&#8217;s a lot to learn about this feature.  <br>We do not yet know precisely what are the conditions for a request to be eligible for this feature. We don&#8217;t know yet what the side effects are if there are any. <br>This is a very promising feature that could help stabilize and make database performance more predictable in some cases.<\/p>\n\n\n\n<p>Written by <a href=\"https:\/\/www.linkedin.com\/in\/steven-naudet-aa540158\/\">Steven Naudet<\/a><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction The Intelligent Query Processing (IQP) feature family is extended with SQL Server 2022.One of the most anticipated features is the Parameter Sensitive Plan optimization. I started to test this new feature. In this post, you will find some information to understand how it works and make your first tests too. The issue with Parameter [&hellip;]<\/p>\n","protected":false},"author":26,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,99],"tags":[2557,1192,2554,2556,2555,2550,2553],"type_dbi":[],"class_list":["post-17208","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-sql-server","tag-iqp","tag-optimization","tag-parameter-sensitive-plan","tag-performance-2","tag-psp","tag-sql-server-2","tag-sql-server-2022"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server 2022 Parameter Sensitive Plan optimization - 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-2022-parameter-sensitive-plan-optimization\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2022 Parameter Sensitive Plan optimization\" \/>\n<meta property=\"og:description\" content=\"Introduction The Intelligent Query Processing (IQP) feature family is extended with SQL Server 2022.One of the most anticipated features is the Parameter Sensitive Plan optimization. I started to test this new feature. In this post, you will find some information to understand how it works and make your first tests too. The issue with Parameter [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-06-01T14:35:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-10-01T10:03:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1780\" \/>\n\t<meta property=\"og:image:height\" content=\"555\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\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=\"6 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-2022-parameter-sensitive-plan-optimization\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"SQL Server 2022 Parameter Sensitive Plan optimization\",\"datePublished\":\"2022-06-01T14:35:33+00:00\",\"dateModified\":\"2025-10-01T10:03:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/\"},\"wordCount\":771,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg\",\"keywords\":[\"IQP\",\"Optimization\",\"Parameter Sensitive Plan\",\"performance\",\"PSP\",\"SQL Server\",\"SQL Server 2022\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/\",\"name\":\"SQL Server 2022 Parameter Sensitive Plan optimization - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg\",\"datePublished\":\"2022-06-01T14:35:33+00:00\",\"dateModified\":\"2025-10-01T10:03:02+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg\",\"width\":1780,\"height\":555},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2022 Parameter Sensitive Plan optimization\"}]},{\"@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 2022 Parameter Sensitive Plan optimization - 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-2022-parameter-sensitive-plan-optimization\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2022 Parameter Sensitive Plan optimization","og_description":"Introduction The Intelligent Query Processing (IQP) feature family is extended with SQL Server 2022.One of the most anticipated features is the Parameter Sensitive Plan optimization. I started to test this new feature. In this post, you will find some information to understand how it works and make your first tests too. The issue with Parameter [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/","og_site_name":"dbi Blog","article_published_time":"2022-06-01T14:35:33+00:00","article_modified_time":"2025-10-01T10:03:02+00:00","og_image":[{"width":1780,"height":555,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"SQL Server 2022 Parameter Sensitive Plan optimization","datePublished":"2022-06-01T14:35:33+00:00","dateModified":"2025-10-01T10:03:02+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/"},"wordCount":771,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg","keywords":["IQP","Optimization","Parameter Sensitive Plan","performance","PSP","SQL Server","SQL Server 2022"],"articleSection":["Database Administration &amp; Monitoring","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/","name":"SQL Server 2022 Parameter Sensitive Plan optimization - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg","datePublished":"2022-06-01T14:35:33+00:00","dateModified":"2025-10-01T10:03:02+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/06\/psp-boundaries.jpg","width":1780,"height":555},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2022-parameter-sensitive-plan-optimization\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2022 Parameter Sensitive Plan optimization"}]},{"@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\/17208","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=17208"}],"version-history":[{"count":29,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17208\/revisions"}],"predecessor-version":[{"id":40620,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17208\/revisions\/40620"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17208"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17208"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17208"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17208"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}