{"id":9701,"date":"2022-10-18T16:45:58","date_gmt":"2022-10-18T11:15:58","guid":{"rendered":"https:\/\/www.skynats.com\/?p=9701"},"modified":"2024-12-17T12:23:16","modified_gmt":"2024-12-17T06:53:16","slug":"recompile-a-stored-procedure-sql-server","status":"publish","type":"post","link":"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/","title":{"rendered":"Recompile a Stored Procedure SQL server"},"content":{"rendered":"\n<p>Let&#8217;s take a look at how our <a href=\"https:\/\/www.skynats.com\/blog\/\" target=\"_blank\" rel=\"noreferrer noopener\">Server Management Services<\/a> at Skynats can help you recompile a stored procedure SQL server method.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-overview-recompile-a-stored-procedure-sql-server\">Overview: Recompile a Stored Procedure SQL Server<\/h2>\n\n\n\n<p><strong>SQL stored procedure:<\/strong><\/p>\n\n\n\n<p>The following are the key advantages of using test scripts, which are ready T-SQL code that can be reused repeatedly:<\/p>\n\n\n\n<p><strong>Performance<\/strong>:<\/p>\n\n\n\n<p>After the initial execution of a stored procedure, the query optimizer generates an execution plan that is cached in the query plan cache. Therefore, this cached stored procedure will be used for all subsequent tasks of the same SQL Server stored procedure. With this technique, unwanted query compilation processes are avoided.<\/p>\n\n\n\n<p><strong>Code reuse<\/strong>:<\/p>\n\n\n\n<p>This makes it possible to avoid continually writing the same codes.<\/p>\n\n\n\n<p><strong>Maintainability<\/strong>:<\/p>\n\n\n\n<p>It is easier to maintain code when stored procedures are used, and many different applications use them. However, changing a single stored procedure will easily affect all applications.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-two-simple-methods-for-recompiling-stored-procedures\">Two Simple Methods for Recompiling Stored Procedures:<\/h3>\n\n\n\n<p>You don&#8217;t have to create the stored procedure from scratch to recompile. Here are two quick techniques:<\/p>\n\n\n\n<p><strong>Method 1: WITH RECOMPILE<\/strong><\/p>\n\n\n\n<p>While your stored procedure is running, you can simply recompile it. This is the script.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXECStoredProcedureName @parameters WITHRECOMPILE<\/code><\/pre>\n\n\n\n<p>As soon as it completes the task, this will recompile the stored procedure.<\/p>\n\n\n\n<p><strong>Method 2: sp_recompile for Recompiling Stored Procedures<\/strong><\/p>\n\n\n\n<p>This is where you can create your stored procedure for recompilation.<\/p>\n\n\n\n<p>When a stored procedure is run for the first time using this technique, the cache is cleared from the procedure cache.<\/p>\n\n\n\n<p>When you run a stored procedure, you use the first method, and when you run it for the first time, it uses a different method to prepare it for recompilation.<\/p>\n\n\n\n<p><strong>Discover the Procedure Cache<\/strong><\/p>\n\n\n\n<p>Here is a short script you can use to find out which stored procedure your database has cached along with its various associated properties.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECTSCHEMA_NAME(SCHEMA_ID) SchemaName, nameProcedureName,last_execution_time LastExecuted,last_elapsed_time LastElapsedTime,execution_count ExecutionCount,cached_time CachedTimeFROMsys.dm_exec_procedure_stats ps JOINsys.objects o ONps.object_id = o.object_idWHEREps.database_id = DB_ID();<\/code><\/pre>\n\n\n\n<p>Are you looking for an answer to another query?\u00a0<a href=\"https:\/\/www.skynats.com\/contact-us\/\" target=\"_blank\" rel=\"noreferrer noopener\"><em>Contact<\/em><\/a>\u00a0our technical support team.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Let&#8217;s take a look at how our Server Management Services at Skynats can help you recompile a stored procedure SQL server method. Overview: Recompile a Stored Procedure SQL Server SQL stored procedure: The following are the key advantages of using test scripts, which are ready T-SQL code that can be reused repeatedly: Performance: After the [&hellip;]<\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[744],"class_list":["post-9701","post","type-post","status-publish","format-standard","hentry","category-blog","tag-recompile"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v25.9 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Recompile a Stored Procedure SQL server | Skynats<\/title>\n<meta name=\"description\" content=\"Let&#039;s take a look at how our Server Management Services at Skynats can help you recompile a stored procedure SQL server method.\" \/>\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.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Recompile a Stored Procedure SQL server\" \/>\n<meta property=\"og:description\" content=\"Let&#039;s take a look at how our Server Management Services at Skynats can help you recompile a stored procedure SQL server method.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Server Management Services | Cloud Management | Skynats\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/skynats\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-18T11:15:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-12-17T06:53:16+00:00\" \/>\n<meta name=\"author\" content=\"Tony\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@skynatstech\" \/>\n<meta name=\"twitter:site\" content=\"@skynatstech\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tony\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/recompile-a-stored-procedure-sql-server\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/recompile-a-stored-procedure-sql-server\\\/\"},\"author\":{\"name\":\"Tony\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#\\\/schema\\\/person\\\/1398d4641035daa52ed8b827e0aafdeb\"},\"headline\":\"Recompile a Stored Procedure SQL server\",\"datePublished\":\"2022-10-18T11:15:58+00:00\",\"dateModified\":\"2024-12-17T06:53:16+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/recompile-a-stored-procedure-sql-server\\\/\"},\"wordCount\":320,\"publisher\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#organization\"},\"keywords\":[\"Recompile\"],\"articleSection\":[\"Blog\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/recompile-a-stored-procedure-sql-server\\\/\",\"url\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/recompile-a-stored-procedure-sql-server\\\/\",\"name\":\"Recompile a Stored Procedure SQL server | Skynats\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-10-18T11:15:58+00:00\",\"dateModified\":\"2024-12-17T06:53:16+00:00\",\"description\":\"Let's take a look at how our Server Management Services at Skynats can help you recompile a stored procedure SQL server method.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/recompile-a-stored-procedure-sql-server\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.skynats.com\\\/blog\\\/recompile-a-stored-procedure-sql-server\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/recompile-a-stored-procedure-sql-server\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Recompile a Stored Procedure SQL server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/\",\"name\":\"Server Management Services | Cloud Management | Skynats\",\"description\":\"Server Management and Cloud Management\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#organization\",\"name\":\"Skynats Technologies\",\"url\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/wp-content\\\/uploads\\\/2021\\\/08\\\/Sknats-Logo-New-whole.png\",\"contentUrl\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/wp-content\\\/uploads\\\/2021\\\/08\\\/Sknats-Logo-New-whole.png\",\"width\":989,\"height\":367,\"caption\":\"Skynats Technologies\"},\"image\":{\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/skynats\",\"https:\\\/\\\/x.com\\\/skynatstech\",\"https:\\\/\\\/www.instagram.com\\\/skynatstech\\\/\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/skynats-technologies\",\"https:\\\/\\\/www.youtube.com\\\/channel\\\/UCvTAjrFJ4_E2MJKwlDHomlg\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.skynats.com\\\/blog\\\/#\\\/schema\\\/person\\\/1398d4641035daa52ed8b827e0aafdeb\",\"name\":\"Tony\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f90e801961478d690f50507d9e19eeecebabab8ab7dfdab7d2d2431114c6acd9?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f90e801961478d690f50507d9e19eeecebabab8ab7dfdab7d2d2431114c6acd9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f90e801961478d690f50507d9e19eeecebabab8ab7dfdab7d2d2431114c6acd9?s=96&d=mm&r=g\",\"caption\":\"Tony\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Recompile a Stored Procedure SQL server | Skynats","description":"Let's take a look at how our Server Management Services at Skynats can help you recompile a stored procedure SQL server method.","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.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Recompile a Stored Procedure SQL server","og_description":"Let's take a look at how our Server Management Services at Skynats can help you recompile a stored procedure SQL server method.","og_url":"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/","og_site_name":"Server Management Services | Cloud Management | Skynats","article_publisher":"https:\/\/www.facebook.com\/skynats","article_published_time":"2022-10-18T11:15:58+00:00","article_modified_time":"2024-12-17T06:53:16+00:00","author":"Tony","twitter_card":"summary_large_image","twitter_creator":"@skynatstech","twitter_site":"@skynatstech","twitter_misc":{"Written by":"Tony","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/"},"author":{"name":"Tony","@id":"https:\/\/www.skynats.com\/blog\/#\/schema\/person\/1398d4641035daa52ed8b827e0aafdeb"},"headline":"Recompile a Stored Procedure SQL server","datePublished":"2022-10-18T11:15:58+00:00","dateModified":"2024-12-17T06:53:16+00:00","mainEntityOfPage":{"@id":"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/"},"wordCount":320,"publisher":{"@id":"https:\/\/www.skynats.com\/blog\/#organization"},"keywords":["Recompile"],"articleSection":["Blog"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/","url":"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/","name":"Recompile a Stored Procedure SQL server | Skynats","isPartOf":{"@id":"https:\/\/www.skynats.com\/blog\/#website"},"datePublished":"2022-10-18T11:15:58+00:00","dateModified":"2024-12-17T06:53:16+00:00","description":"Let's take a look at how our Server Management Services at Skynats can help you recompile a stored procedure SQL server method.","breadcrumb":{"@id":"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.skynats.com\/blog\/recompile-a-stored-procedure-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.skynats.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Recompile a Stored Procedure SQL server"}]},{"@type":"WebSite","@id":"https:\/\/www.skynats.com\/blog\/#website","url":"https:\/\/www.skynats.com\/blog\/","name":"Server Management Services | Cloud Management | Skynats","description":"Server Management and Cloud Management","publisher":{"@id":"https:\/\/www.skynats.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.skynats.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.skynats.com\/blog\/#organization","name":"Skynats Technologies","url":"https:\/\/www.skynats.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.skynats.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.skynats.com\/blog\/wp-content\/uploads\/2021\/08\/Sknats-Logo-New-whole.png","contentUrl":"https:\/\/www.skynats.com\/blog\/wp-content\/uploads\/2021\/08\/Sknats-Logo-New-whole.png","width":989,"height":367,"caption":"Skynats Technologies"},"image":{"@id":"https:\/\/www.skynats.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/skynats","https:\/\/x.com\/skynatstech","https:\/\/www.instagram.com\/skynatstech\/","https:\/\/www.linkedin.com\/company\/skynats-technologies","https:\/\/www.youtube.com\/channel\/UCvTAjrFJ4_E2MJKwlDHomlg"]},{"@type":"Person","@id":"https:\/\/www.skynats.com\/blog\/#\/schema\/person\/1398d4641035daa52ed8b827e0aafdeb","name":"Tony","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f90e801961478d690f50507d9e19eeecebabab8ab7dfdab7d2d2431114c6acd9?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f90e801961478d690f50507d9e19eeecebabab8ab7dfdab7d2d2431114c6acd9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f90e801961478d690f50507d9e19eeecebabab8ab7dfdab7d2d2431114c6acd9?s=96&d=mm&r=g","caption":"Tony"}}]}},"_links":{"self":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts\/9701","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/comments?post=9701"}],"version-history":[{"count":0,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/posts\/9701\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/media?parent=9701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/categories?post=9701"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skynats.com\/blog\/wp-json\/wp\/v2\/tags?post=9701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}