{"id":39546,"date":"2025-07-21T08:08:08","date_gmt":"2025-07-21T06:08:08","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=39546"},"modified":"2025-07-24T11:26:54","modified_gmt":"2025-07-24T09:26:54","slug":"sql-server-2025-optimized_sp_executesql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/","title":{"rendered":"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL"},"content":{"rendered":"\n<p>Within the past few days I was investigating another new feature of SQL Server 2025: OPTIMIZED_SP_EXECUTESQL. I was curious about the capabilities and the behaviour.<\/p>\n\n\n\n<p>OPTIMIZED_SP_EXECUTESQL is a database scoped configuration which allows us to get rid of the overhead of compiling and producing execution plans when we deal with dynamic SQL. The first user-session which launches a given dynamic statement using the stored procedure &#8220;sp_executesql&#8221;, will have to wait until SQL Server goes through the compilation process and has produced an execution plan. Then, all the following user-sessions can benefit from the work which was done earlier in terms of no more compilation work is left to be done. <br>Without enabling this feature, SQL Server will produce an execution plan for each execution of the same dynamic query (means we lose time because SQL Server has to compile every time we execute a query) \u2013 even if the query text is exactly the same.<\/p>\n\n\n\n<p><br>Furthermore, we are facing plan cache pollution because we have an unnecessarily large number of execution plans in the cache \u2013 for nothing! I will dig into a demo to show you the behaviour with and without this feature enabled. First of all, I create a tiny database called \u201cOptimizedSpExecutesql\u201d:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE DATABASE &#091;OptimizedSpExecutesql]\nGO\n\nUSE &#091;OptimizedSpExecutesql]\nGO\n\nCREATE TABLE dbo.Orders \n(\nOrderID INT IDENTITY(1,1) PRIMARY KEY,\nOrderType Varchar(20),\nOrderDate DATETIME DEFAULT GETDATE()\n)\nGO\n\nINSERT INTO dbo.Orders (OrderType) VALUES\n('Bentley'), ('Aston Martin'), ('Bentley'), ('Bugatti'),\n('Lamborghini'), ('Bentley'), ('Aston Martin'), ('Chevrolet'),\n('Bentley'), ('Bugatti'), ('Aston Martin'), ('Lamborghini');\nGO\n<\/code><\/pre>\n\n\n\n<p>The result set looks as follows:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"485\" height=\"405\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png\" alt=\"\" class=\"wp-image-39547\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png 485w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28-300x251.png 300w\" sizes=\"auto, (max-width: 485px) 100vw, 485px\" \/><\/figure>\n\n\n\n<p>Now I check, if the feature is disable to simulate the \u201cold\u201d world (i.e. the old behaviour):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Turn off the features - just to be sure\nALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_SP_EXECUTESQL = OFF;   \nGO\nALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = OFF;\nGO\n\nSELECT name, value, is_value_default FROM sys.database_scoped_configurations\nWHERE name IN ('OPTIMIZED_SP_EXECUTESQL', 'ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY')\nGO\n<\/code><\/pre>\n\n\n\n<p>As you can see, this is the default value for both of the database scoped settings:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"707\" height=\"137\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-29.png\" alt=\"\" class=\"wp-image-39548\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-29.png 707w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-29-300x58.png 300w\" sizes=\"auto, (max-width: 707px) 100vw, 707px\" \/><\/figure>\n\n\n\n<p>Let\u2019s cleanup the cache first and then execute the TSQL-Statement shown below. I will execute the TSQL-Statement with \u201costress.exe\u201d to simulate 100 sessions executing the statement once per each session:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Clean the plan cache first\nDBCC FREEPROCCACHE\nGO\n\n-- Define a parameterized query which will be executed via ostress.exe afterwards\n-- This code below is just to mention the TSQL which will be executed\nDECLARE @SQL NVarchar(MAX)\nSET @SQL = N'SELECT COUNT(*) FROM dbo.Orders WHERE OrderType = @Type';\n\nDECLARE @Params NVARCHAR(MAX) = N'@Type Varchar(20)';\nEXEC sys.sp_executesql @SQL, @Params, @Type = N'Bentley';  \nGO\n<\/code><\/pre>\n\n\n\n<p>Here is the execution of the statement with \u201costress.exe\u201d:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"135\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-30-1024x135.png\" alt=\"\" class=\"wp-image-39550\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-30-1024x135.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-30-300x39.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-30-768x101.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-30.png 1065w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>And the output of the plan cache shows us, that SQL Server has produced several execution plans for the same sql_handle (i.e. the same statement):<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"415\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-31-1024x415.png\" alt=\"\" class=\"wp-image-39551\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-31-1024x415.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-31-300x122.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-31-768x312.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-31.png 1065w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>The result here is a little bit unpredictable \u2013 sometimes you get 10 different plans, sometimes 85 and so on and so forth.<br>But all executions have one thing in common: we end up with an enormous amount of execution plans cached within the plan cache for exactly the same \u2013 means one! \u2013 query&#8230;<\/p>\n\n\n\n<p><strong>The feature turned on<\/strong> <\/p>\n\n\n\n<p>Now I want to show you the behaviour of SQL Server with the new feature turned on. Therefore, I will enable the database scoped setting(s) to on.<br>By-the-way: As you can see in the code section below, I turned on 2 switches at the same time. The first one is the feature itself which helps us to reduce compiling to a minimum while the second one just helps us to refresh the statistics asynchronously at low priority (instead of doing blockings through a synchronous SCH-M lock) \u2013 means that Statistic Updates stalls are avoided because SQL Server will use current statistics and updates those later if he isn\u2019t able to get a SCH-M lock placed at the moment.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Change the behaviour having the feature turned on\nUSE &#091;OptimizedSpExecutesql]\nGO\n\nALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_SP_EXECUTESQL = ON;   \nGO\nALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON;\nGO\n\nSELECT name, value, is_value_default FROM sys.database_scoped_configurations\nWHERE name IN ('OPTIMIZED_SP_EXECUTESQL', 'ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY')\nGO\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"698\" height=\"137\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-32.png\" alt=\"\" class=\"wp-image-39553\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-32.png 698w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-32-300x59.png 300w\" sizes=\"auto, (max-width: 698px) 100vw, 698px\" \/><\/figure>\n\n\n\n<p>After clearing the cache and executing the TSQL-Statement via \u201costress.exe\u201d again, we see within the plan cache following content:<br><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"64\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-33-1024x64.png\" alt=\"\" class=\"wp-image-39554\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-33-1024x64.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-33-300x19.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-33-768x48.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-33.png 1062w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>One plan with an execution count of 100 \u2013 as expected and also as desired!<br><\/p>\n\n\n\n<p><strong>Get a deeper insight into what is going on behind the scenes<\/strong> <\/p>\n\n\n\n<p>I want to show you what is captured within an Extended Events Session when we execute the same query as above \u2013 once without the new feature and once with it.<br>The Extended Events Session looks as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE EVENT SESSION &#091;QueryPerformanceCapture] ON SERVER \nADD EVENT sqlserver.sql_batch_completed (\nACTION (sqlos.task_time, sqlserver.client_app_name, sqlserver.database_id, sqlserver.server_principal_name, sqlserver.session_id, sqlserver.sql_text)\nWHERE (&#091;sqlserver].&#091;sql_text] LIKE N'%WHERE OrderType = @Type%')),\nADD EVENT sqlserver.sql_statement_recompile (\nACTION (sqlserver.client_app_name, sqlserver.database_id, sqlserver.session_id, sqlserver.sql_text)\nWHERE (&#091;sqlserver].&#091;sql_text] LIKE N'%WHERE OrderType = @Type%')),\nADD EVENT sqlserver.sp_statement_completed (\nACTION (sqlserver.session_id, sqlserver.sql_text, sqlserver.client_app_name, sqlserver.database_id)\nWHERE (&#091;sqlserver].&#091;sql_text] LIKE N'%WHERE OrderType = @Type%')),\nADD EVENT sqlserver.query_post_compilation_showplan(\nACTION(sqlserver.sql_text, sqlserver.session_id, sqlserver.client_app_name, sqlserver.database_id)\nWHERE (&#091;sqlserver].&#091;sql_text] LIKE N'%WHERE OrderType = @Type%'))\nADD TARGET package0.event_file (\nSET filename = N'C:\\DATEN\\SQL\\SQL_SERVER_HOME\\MSSQL17.MSSQLSERVER\\ANALYSIS\\QueryPerformanceCapture.xel', max_file_size = (10), max_rollover_files = (5))\nWITH (\n    MAX_MEMORY = 4096 KB,\n    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,\n    MAX_DISPATCH_LATENCY = 30 SECONDS,\n    MAX_EVENT_SIZE = 0 KB,\n    MEMORY_PARTITION_MODE = NONE,\n    TRACK_CAUSALITY = OFF,\n    STARTUP_STATE = ON\n);\nGO\n\n-- Start the event session\nALTER EVENT SESSION &#091;QueryPerformanceCapture] ON SERVER  \nSTATE = START;\nGO\n<\/code><\/pre>\n\n\n\n<p>As you can see, during the first execution of the TSQL-Statement via \u201costress.exe\u201d, 100 events were captured in terms of completion (because we launched 100 executions of the code) and 47 events related to compilations:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"685\" height=\"126\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-34.png\" alt=\"\" class=\"wp-image-39555\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-34.png 685w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-34-300x55.png 300w\" sizes=\"auto, (max-width: 685px) 100vw, 685px\" \/><\/figure>\n\n\n\n<p>Regarding the time spent on those 47 compile events, we land in this example on 16813998 nanoseconds which is 16.8 seconds:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"912\" height=\"128\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-35.png\" alt=\"\" class=\"wp-image-39556\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-35.png 912w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-35-300x42.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-35-768x108.png 768w\" sizes=\"auto, (max-width: 912px) 100vw, 912px\" \/><\/figure>\n\n\n\n<p>As soon as I turned on the feature on database level, we are facing only 1 event related to compilation:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"137\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-36-1024x137.png\" alt=\"\" class=\"wp-image-39558\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-36-1024x137.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-36-300x40.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-36-768x103.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-36.png 1066w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Regarding the time spent here on compiling now, we observe that this is 91142 nanoseconds (i.e. only 0.09 seconds) because SQL Server had to do the work only once:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"94\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-37-1024x94.png\" alt=\"\" class=\"wp-image-39559\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-37-1024x94.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-37-300x28.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-37-768x71.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-37.png 1065w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><br><strong>Conclusion<\/strong><\/p>\n\n\n\n<p>From my point of view, \u201cOPTIMIZED_SP_EXECUTESQL\u201d is as well as \u201cOptimized Locking\u201d a real game changer. Or should I say: could be?<br>Well, it depends as always\u2026 It depends on the workload you are dealing with on your SQL Server or database. If you see a lot of dynamic SQL, enabling this feature is really valuable otherwise it won\u2019t have an effect. Therefore, I recommend to enable this feature not instantaneous on all your databases, but to monitor your workload first and then make your decision after evaluating.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Within the past few days I was investigating another new feature of SQL Server 2025: OPTIMIZED_SP_EXECUTESQL. I was curious about the capabilities and the behaviour. OPTIMIZED_SP_EXECUTESQL is a database scoped configuration which allows us to get rid of the overhead of compiling and producing execution plans when we deal with dynamic SQL. The first user-session [&hellip;]<\/p>\n","protected":false},"author":138,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,368,99],"tags":[644,51],"type_dbi":[],"class_list":["post-39546","post","type-post","status-publish","format-standard","hentry","category-database-management","category-development-performance","category-sql-server","tag-performance-tuning","tag-sql-server"],"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 2025 \u2013 OPTIMIZED_SP_EXECUTESQL - 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-2025-optimized_sp_executesql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL\" \/>\n<meta property=\"og:description\" content=\"Within the past few days I was investigating another new feature of SQL Server 2025: OPTIMIZED_SP_EXECUTESQL. I was curious about the capabilities and the behaviour. OPTIMIZED_SP_EXECUTESQL is a database scoped configuration which allows us to get rid of the overhead of compiling and producing execution plans when we deal with dynamic SQL. The first user-session [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-07-21T06:08:08+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-07-24T09:26:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png\" \/>\n\t<meta property=\"og:image:width\" content=\"485\" \/>\n\t<meta property=\"og:image:height\" content=\"405\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Roger Sch\u00f6nmann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Roger Sch\u00f6nmann\" \/>\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-2025-optimized_sp_executesql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/\"},\"author\":{\"name\":\"Roger Sch\u00f6nmann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6\"},\"headline\":\"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL\",\"datePublished\":\"2025-07-21T06:08:08+00:00\",\"dateModified\":\"2025-07-24T09:26:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/\"},\"wordCount\":764,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png\",\"keywords\":[\"Performance Tuning\",\"SQL Server\"],\"articleSection\":[\"Database management\",\"Development &amp; Performance\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/\",\"name\":\"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png\",\"datePublished\":\"2025-07-21T06:08:08+00:00\",\"dateModified\":\"2025-07-24T09:26:54+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png\",\"width\":485,\"height\":405},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL\"}]},{\"@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\/fd2a9801ad83d36521b3af1428abc2c6\",\"name\":\"Roger Sch\u00f6nmann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g\",\"caption\":\"Roger Sch\u00f6nmann\"},\"honorificPrefix\":\"Mr\",\"gender\":\"male\",\"knowsLanguage\":[\"German\",\"English\",\"French\",\"Italian\"],\"jobTitle\":\"Senior SQL Server Consultant\",\"worksFor\":\"Roger Sch\u00f6nmann\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/rogerschonmann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL - 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-2025-optimized_sp_executesql\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL","og_description":"Within the past few days I was investigating another new feature of SQL Server 2025: OPTIMIZED_SP_EXECUTESQL. I was curious about the capabilities and the behaviour. OPTIMIZED_SP_EXECUTESQL is a database scoped configuration which allows us to get rid of the overhead of compiling and producing execution plans when we deal with dynamic SQL. The first user-session [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/","og_site_name":"dbi Blog","article_published_time":"2025-07-21T06:08:08+00:00","article_modified_time":"2025-07-24T09:26:54+00:00","og_image":[{"width":485,"height":405,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png","type":"image\/png"}],"author":"Roger Sch\u00f6nmann","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Roger Sch\u00f6nmann","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/"},"author":{"name":"Roger Sch\u00f6nmann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6"},"headline":"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL","datePublished":"2025-07-21T06:08:08+00:00","dateModified":"2025-07-24T09:26:54+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/"},"wordCount":764,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png","keywords":["Performance Tuning","SQL Server"],"articleSection":["Database management","Development &amp; Performance","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/","name":"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png","datePublished":"2025-07-21T06:08:08+00:00","dateModified":"2025-07-24T09:26:54+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-28.png","width":485,"height":405},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized_sp_executesql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2025 \u2013 OPTIMIZED_SP_EXECUTESQL"}]},{"@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\/fd2a9801ad83d36521b3af1428abc2c6","name":"Roger Sch\u00f6nmann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g","caption":"Roger Sch\u00f6nmann"},"honorificPrefix":"Mr","gender":"male","knowsLanguage":["German","English","French","Italian"],"jobTitle":"Senior SQL Server Consultant","worksFor":"Roger Sch\u00f6nmann","url":"https:\/\/www.dbi-services.com\/blog\/author\/rogerschonmann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/39546","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\/138"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=39546"}],"version-history":[{"count":13,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/39546\/revisions"}],"predecessor-version":[{"id":39651,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/39546\/revisions\/39651"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=39546"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=39546"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=39546"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=39546"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}