{"id":843,"date":"2024-10-25T08:00:00","date_gmt":"2024-10-25T13:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=843"},"modified":"2024-10-24T23:25:28","modified_gmt":"2024-10-25T04:25:28","slug":"avoiding-recompiles-dynamic-sql-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=843","title":{"rendered":"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server"},"content":{"rendered":"<h2>Introduction<\/h2>\n<p>Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It&#8217;s especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I&#8217;ll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.<\/p>\n<h2>Why Dynamic SQL Causes Recompiles<\/h2>\n<p>When SQL Server runs a query, it creates an execution plan\u2014a roadmap for how to execute the query. These plans are cached so that if the same query runs again, SQL Server can reuse the plan without having to recreate it, saving time and resources.<\/p>\n<p>With dynamic SQL, especially when building queries by concatenating strings, the actual text of the query can change with each execution. Even small changes in the query text make SQL Server think it&#8217;s a new query, so it creates a new execution plan each time. This can lead to:<\/p>\n<ul>\n<li><strong>Increased CPU Usage<\/strong>: Compiling new plans consumes more CPU resources.<\/li>\n<li><strong>Plan Cache Bloat<\/strong>: The cache fills up with many single-use plans, consuming memory.<\/li>\n<li><strong>Slower Performance<\/strong>: Recompiling plans adds overhead, slowing down query execution.<\/li>\n<\/ul>\n<h2>Best Practices to Avoid Recompiles<\/h2>\n<h3>1. Use <code>sp_executesql<\/code> with Parameters<\/h3>\n<p>Instead of injecting parameters directly into your dynamic SQL string, use <code>sp_executesql<\/code> to parameterize your queries. This keeps the query text consistent across executions, allowing SQL Server to reuse the execution plan.<\/p>\n<p><strong>Example Without Parameterization (Leads to Recompiles):<\/strong><\/p>\n<pre><code>DECLARE @sql NVARCHAR(MAX);\nDECLARE @param1 NVARCHAR(50) = 'ValueA';\n\nSET @sql = N'SELECT * FROM dbo.MyTable WHERE MyColumn = ''' + @param1 + ''';';\nEXEC(@sql);<\/code><\/pre>\n<p><strong>Example With <code>sp_executesql<\/code> (Avoids Recompiles):<\/strong><\/p>\n<pre><code>DECLARE @sql NVARCHAR(MAX);\nDECLARE @param1 NVARCHAR(50) = 'ValueA';\n\nSET @sql = N'SELECT * FROM dbo.MyTable WHERE MyColumn = @param1;';\nEXEC sp_executesql @sql, N'@param1 NVARCHAR(50)', @param1 = @param1;<\/code><\/pre>\n<p>By using parameters, the query text remains the same every time it runs, so SQL Server can cache and reuse the execution plan.<\/p>\n<h3>2. Avoid Dynamic SQL When It&#8217;s Not Necessary<\/h3>\n<p>Before using dynamic SQL, consider whether you really need it. If your query structure is fixed and only the parameter values change, you can use a regular parameterized query or a stored procedure.<\/p>\n<p><strong>Example Using a Stored Procedure:<\/strong><\/p>\n<pre><code>CREATE PROCEDURE dbo.GetMyData\n    @param1 NVARCHAR(50)\nAS\nBEGIN\n    SELECT * FROM dbo.MyTable WHERE MyColumn = @param1;\nEND;<\/code><\/pre>\n<p>This method avoids the complexities of dynamic SQL and ensures efficient plan reuse.<\/p>\n<h3>3. Keep Your Dynamic SQL Statements Consistent<\/h3>\n<p>If you must use dynamic SQL, make sure the query text doesn&#8217;t change unnecessarily between executions. Avoid incorporating variable elements directly into the SQL string that could alter its text.<\/p>\n<p><strong>Inefficient Approach (Query Text Changes Each Time):<\/strong><\/p>\n<pre><code>DECLARE @sql NVARCHAR(MAX);\nDECLARE @param1 NVARCHAR(50) = 'ValueA';\n\nSET @sql = N'SELECT * FROM dbo.MyTable WHERE MyColumn = ''' + @param1 + ''';';<\/code><\/pre>\n<p><strong>Efficient Approach (Consistent Query Text):<\/strong><\/p>\n<pre><code>DECLARE @sql NVARCHAR(MAX);\nDECLARE @param1 NVARCHAR(50) = 'ValueA';\n\nSET @sql = N'SELECT * FROM dbo.MyTable WHERE MyColumn = @param1;';<\/code><\/pre>\n<p>Using parameters ensures the query text stays the same, helping SQL Server recognize it and reuse the plan.<\/p>\n<h3>4. Be Mindful of Parameter Sniffing<\/h3>\n<p>Parameter sniffing occurs when SQL Server uses the parameter values from the first execution to generate the execution plan. If subsequent executions use different parameter values that don&#8217;t perform well with the cached plan, performance can suffer.<\/p>\n<p>To handle this, you can:<\/p>\n<ul>\n<li><strong>Use <code>OPTION (RECOMPILE)<\/code><\/strong>: Forces SQL Server to recompile the query plan for each execution, optimizing it for the current parameter values.<\/li>\n<\/ul>\n<pre><code>DECLARE @sql NVARCHAR(MAX);\nDECLARE @param1 NVARCHAR(50) = 'ValueA';\n\nSET @sql = N'SELECT * FROM dbo.MyTable WHERE MyColumn = @param1 OPTION (RECOMPILE);';\nEXEC sp_executesql @sql, N'@param1 NVARCHAR(50)', @param1 = @param1;<\/code><\/pre>\n<p>Use this sparingly, as it increases CPU usage due to recompilation.<\/p>\n<ul>\n<li><strong>Use <code>OPTIMIZE FOR UNKNOWN<\/code><\/strong>: Instructs SQL Server to optimize the query without using the initial parameter values, which can result in a more general execution plan.<\/li>\n<\/ul>\n<pre><code>DECLARE @sql NVARCHAR(MAX);\nDECLARE @param1 NVARCHAR(50) = 'ValueA';\n\nSET @sql = N'SELECT * FROM dbo.MyTable WHERE MyColumn = @param1 OPTION (OPTIMIZE FOR UNKNOWN);';\nEXEC sp_executesql @sql, N'@param1 NVARCHAR(50)', @param1 = @param1;<\/code><\/pre>\n<h3>5. Monitor and Analyze Recompiles<\/h3>\n<p>Keep an eye on your SQL Server to identify queries that are causing recompiles. You can use dynamic management views (DMVs) or Extended Events to monitor recompilation events.<\/p>\n<p><strong>Example Query to Identify Ad-Hoc Plans:<\/strong><\/p>\n<pre><code>SELECT\n    cp.plan_handle,\n    cp.objtype,\n    st.text,\n    cp.usecounts\nFROM sys.dm_exec_cached_plans AS cp\nCROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st\nWHERE cp.objtype = 'Adhoc'\nORDER BY cp.usecounts DESC;<\/code><\/pre>\n<p>This query helps you find ad-hoc queries with low reuse counts, indicating they might be causing recompiles.<\/p>\n<h3>6. Ensure Data Types Match<\/h3>\n<p>When declaring parameters in <code>sp_executesql<\/code>, make sure the data types and lengths match those in the database. Mismatches can lead to implicit conversions, which can prevent plan reuse.<\/p>\n<p><strong>Example:<\/strong><\/p>\n<pre><code>DECLARE @sql NVARCHAR(MAX);\nDECLARE @param1 NVARCHAR(50) = 'ValueA';\n\nSET @sql = N'SELECT * FROM dbo.MyTable WHERE MyColumn = @param1;';\nEXEC sp_executesql @sql, N'@param1 NVARCHAR(50)', @param1 = @param1;<\/code><\/pre>\n<h3>7. Avoid Changing SET Options in Dynamic SQL<\/h3>\n<p>Altering session-level SET options within your dynamic SQL can cause SQL Server to treat each execution as a different query. Keep SET options consistent to enable plan reuse.<\/p>\n<p><strong>Inefficient Approach:<\/strong><\/p>\n<pre><code>DECLARE @sql NVARCHAR(MAX);\nDECLARE @param1 NVARCHAR(50) = 'ValueA';\n\nSET @sql = N'SET ARITHABORT ON; SELECT * FROM dbo.MyTable WHERE MyColumn = @param1;';\nEXEC sp_executesql @sql, N'@param1 NVARCHAR(50)', @param1 = @param1;<\/code><\/pre>\n<p><strong>Better Approach:<\/strong><\/p>\n<p>Set all necessary SET options at the session level before executing the dynamic SQL.<\/p>\n<h3>8. Be Consistent with Formatting<\/h3>\n<p>Even minor differences like extra spaces or comments can change the query text and prevent plan reuse. Keep your SQL statements formatted consistently.<\/p>\n<h3>9. Secure Your Dynamic SQL<\/h3>\n<p>While not directly related to recompiles, it&#8217;s important to protect your dynamic SQL from SQL injection attacks. Use parameterization and functions like <code>QUOTENAME<\/code> for object names.<\/p>\n<p><strong>Example Using <code>QUOTENAME<\/code>:<\/strong><\/p>\n<pre><code>DECLARE @sql NVARCHAR(MAX);\nDECLARE @tableName SYSNAME = 'MyTable';\nDECLARE @param1 NVARCHAR(50) = 'ValueA';\n\nSET @sql = N'SELECT * FROM ' + QUOTENAME(@tableName) + N' WHERE MyColumn = @param1;';\nEXEC sp_executesql @sql, N'@param1 NVARCHAR(50)', @param1 = @param1;<\/code><\/pre>\n<h2>Testing the Queries: Setting Up a Sample Database<\/h2>\n<p>To try out these examples, you can create a test database with the following script:<\/p>\n<pre><code>-- Create a test database\nCREATE DATABASE DynamicSQLTestDB;\nGO\n\n-- Use the test database\nUSE DynamicSQLTestDB;\nGO\n\n-- Create a sample table 'MyTable'\nCREATE TABLE dbo.MyTable (\n    ID INT IDENTITY(1,1) PRIMARY KEY,\n    MyColumn NVARCHAR(50),\n    MyColumn1 NVARCHAR(50),\n    MyColumn2 INT\n);\nGO\n\n-- Insert sample data into 'MyTable'\nINSERT INTO dbo.MyTable (MyColumn, MyColumn1, MyColumn2)\nVALUES\n    ('ValueA', 'Alpha', 10),\n    ('ValueB', 'Beta', 20),\n    ('ValueC', 'Gamma', 30),\n    ('ValueD', 'Delta', 40),\n    ('ValueE', 'Epsilon', 50);\nGO\n\n-- Create an index on 'MyColumn'\nCREATE INDEX IDX_MyColumn ON dbo.MyTable(MyColumn);\nGO<\/code><\/pre>\n<h2>Instructions:<\/h2>\n<ul>\n<li>Run the script above to create the <code>DynamicSQLTestDB<\/code> database and the <code>dbo.MyTable<\/code> table.<\/li>\n<li>The table includes sample data that we&#8217;ll use in the examples.<\/li>\n<li>An index is created on <code>MyColumn<\/code> to simulate a more realistic scenario.<\/li>\n<\/ul>\n<h2>Conclusion<\/h2>\n<p>Dynamic SQL doesn&#8217;t have to be a performance headache. By following these best practices\u2014like parameterizing your queries with <code>sp_executesql<\/code>, keeping your SQL statements consistent, and monitoring for recompiles\u2014you can enjoy the flexibility of dynamic SQL without sacrificing performance.<\/p>\n<p>Remember, the goal is to keep the query text consistent so that SQL Server can cache and reuse execution plans. This boosts performance and helps maintain a healthier SQL Server environment. Keep an eye on your queries, and adjust your approach as needed to ensure your dynamic SQL is as efficient as possible.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It&#8217;s especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I&#8217;ll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[12,5,91,84],"tags":[333,92,269,334,336,335,337,131],"class_list":["post-843","post","type-post","status-publish","format-standard","hentry","category-internals","category-performance","category-query-optimization","category-sql-developer","tag-dynamic-sql","tag-execution-plans","tag-parameter-sniffing","tag-performance-optimization","tag-plan-reuse","tag-sp_executesql","tag-sql-best-practices","tag-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server - SQL Table Talk<\/title>\n<meta name=\"description\" content=\"Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It&#039;s especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I&#039;ll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.\" \/>\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.sqltabletalk.com\/?p=843\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server - SQL Table Talk\" \/>\n<meta property=\"og:description\" content=\"Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It&#039;s especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I&#039;ll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltabletalk.com\/?p=843\" \/>\n<meta property=\"og:site_name\" content=\"SQL Table Talk\" \/>\n<meta property=\"article:published_time\" content=\"2024-10-25T13:00:00+00:00\" \/>\n<meta name=\"author\" content=\"Stephen Planck\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Stephen Planck\" \/>\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.sqltabletalk.com\/?p=843#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=843\"},\"author\":{\"name\":\"Stephen Planck\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"headline\":\"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server\",\"datePublished\":\"2024-10-25T13:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=843\"},\"wordCount\":870,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"keywords\":[\"Dynamic SQL\",\"Execution Plans\",\"parameter sniffing\",\"Performance Optimization\",\"Plan Reuse\",\"sp_executesql\",\"SQL Best Practices\",\"SQL Server\"],\"articleSection\":[\"Internals\",\"Performance\",\"Query Optimization\",\"SQL Developer\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=843#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=843\",\"url\":\"https:\/\/www.sqltabletalk.com\/?p=843\",\"name\":\"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server - SQL Table Talk\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\"},\"datePublished\":\"2024-10-25T13:00:00+00:00\",\"description\":\"Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It's especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I'll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=843#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltabletalk.com\/?p=843\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltabletalk.com\/?p=843#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltabletalk.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#website\",\"url\":\"https:\/\/www.sqltabletalk.com\/\",\"name\":\"SQL Table Talk\",\"description\":\"Breaking Down SQL Server, One Post at a Time.\",\"publisher\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltabletalk.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0\",\"name\":\"Stephen Planck\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g\",\"caption\":\"Stephen Planck\"},\"logo\":{\"@id\":\"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/dexterwiki.com\",\"https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI\",\"https:\/\/www.youtube.com\/linuxmate\"],\"url\":\"https:\/\/www.sqltabletalk.com\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server - SQL Table Talk","description":"Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It's especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I'll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.","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.sqltabletalk.com\/?p=843","og_locale":"en_US","og_type":"article","og_title":"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server - SQL Table Talk","og_description":"Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It's especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I'll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.","og_url":"https:\/\/www.sqltabletalk.com\/?p=843","og_site_name":"SQL Table Talk","article_published_time":"2024-10-25T13:00:00+00:00","author":"Stephen Planck","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Stephen Planck","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqltabletalk.com\/?p=843#article","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/?p=843"},"author":{"name":"Stephen Planck","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"headline":"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server","datePublished":"2024-10-25T13:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqltabletalk.com\/?p=843"},"wordCount":870,"commentCount":0,"publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"keywords":["Dynamic SQL","Execution Plans","parameter sniffing","Performance Optimization","Plan Reuse","sp_executesql","SQL Best Practices","SQL Server"],"articleSection":["Internals","Performance","Query Optimization","SQL Developer"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqltabletalk.com\/?p=843#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqltabletalk.com\/?p=843","url":"https:\/\/www.sqltabletalk.com\/?p=843","name":"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server - SQL Table Talk","isPartOf":{"@id":"https:\/\/www.sqltabletalk.com\/#website"},"datePublished":"2024-10-25T13:00:00+00:00","description":"Dynamic SQL is a powerful feature in SQL Server that allows you to build and execute SQL statements on the fly. It's especially useful when you need flexibility in your queries based on varying conditions. However, a common issue with dynamic SQL is that it can lead to frequent recompilations of execution plans, which can hurt performance. In this post, I'll explain why this happens, how you can avoid unnecessary recompiles, and provide scripts you can use to test these concepts yourself.","breadcrumb":{"@id":"https:\/\/www.sqltabletalk.com\/?p=843#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltabletalk.com\/?p=843"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltabletalk.com\/?p=843#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltabletalk.com\/"},{"@type":"ListItem","position":2,"name":"Avoiding Recompiles in Dynamic SQL: Best Practices for SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltabletalk.com\/#website","url":"https:\/\/www.sqltabletalk.com\/","name":"SQL Table Talk","description":"Breaking Down SQL Server, One Post at a Time.","publisher":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltabletalk.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/1947e42a9438bccd91691d8b791888e0","name":"Stephen Planck","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/64181114edc3de3d99072c049bcec024f025c9536dc89fc8ff1bac58976ca81e?s=96&d=mm&r=g","caption":"Stephen Planck"},"logo":{"@id":"https:\/\/www.sqltabletalk.com\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/dexterwiki.com","https:\/\/www.linkedin.com\/in\/stephen-planck-4611b692?trk=people-guest_people_search-card&challengeId=AQErf8gbBmcVMwAAAYsyIsxO-0UvU8z7cHrBpZoo_n3xt9qEKpRN5B_jd_LmAMu-OfeArkQ7GDjobJ2uRoQQV35EQdh_rR6kxA&submissionId=09de7067-c335-8e17-40b8-8dc32b60ed6c&challengeSource=AgEcUCw35zpPmAAAAYsyI4vAWhJTV7Nt4vZYKc3V1qiDBpCkKgUvtlOBgYXcE84&challegeType=AgE_wZiTT09IAQAAAYsyI4vDmNvbZIYe6XHju5V2bXVvM3IVxnJslgY&memberId=AgESFTkUShzs_gAAAYsyI4vGYk0Gic1uc5kB6cKOABA26Gw&recognizeDevice=AgHdSZyUSI5CEwAAAYsyI4vKd_koF9JgpsCJShT8QfbK1QMiv8SI","https:\/\/www.youtube.com\/linuxmate"],"url":"https:\/\/www.sqltabletalk.com\/?author=1"}]}},"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/843","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=843"}],"version-history":[{"count":1,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/843\/revisions"}],"predecessor-version":[{"id":844,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=\/wp\/v2\/posts\/843\/revisions\/844"}],"wp:attachment":[{"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=843"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=843"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqltabletalk.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=843"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}