The Use and Abuse of RECOMPILE in SQL Server (Video)

What are the costs when you ask SQL Server to recompile a statement or a stored procedure each time it runs? In this 30 minute talk Kendra Little explains the impact on query execution– and on your SQL Server instance– of forcing recompilation. This talk is appropriate for DBAs and Developers who have a working knowledge of execution plans in SQL Server.

Want to use the scripts from the video? Scroll on down to the bottom of the page.

Learn more about Recompilation in SQL Server

Scripts used today:

Previous Post
Why Your SQL Server’s Network Connection Matters
Next Post
Five Things That Fix Bad SQL Server Performance

5 Comments. Leave new

  • Nice.I used a proc in Sybase. This proc was fired almost every second and it was running in around 2 seconds but this was also high and thus I looked what was going on and there was a statement which was cauisng the proc to recompile (in sybase there is no statement level recompile but if populate temp table and then create the index on table then all the code after that create index statement is recompiled) and the proc was very big and referencing lots of tables. After putting the create index statement before populating the data it reduced the run time from 2 s to 350ms on average.

    Reply
  • Abhiram. Chitipirlla (Raam )
    July 27, 2013 8:07 pm

    Hi Kendra

    First thing , I really appreciate the way your team bringing SQL awareness.

    In this video, you mentioned ; Plan cache will get truncated each time we restart SQL Server. What is the case in clustering environment where maintenance(SAN , Windows patching, SQL Patching) takes place every 10 -15 days ? At this point, SQL Server services will move from one node to another each time we do a fail over.

    Does SQL plan cache get truncate each time we fail-over?

    Reply
  • You have a typo in the youtube embed link, so the video does not show up correctly.
    Also the audio quality of the video is a bit low, hardly understandable.
    However, keep up the good work!

    Reply
    • Kendra Little
      March 17, 2015 12:27 pm

      Thanks Stefan– the link looks OK to me, so I think someone may have fixed this already.

      Thanks for the note on the sound. This is back from when we recorded live webcasts that we simultaneously broadcasted, and the sound quality was pretty poor for the recordings with that system. I’ll put this on my list of topics that would be good to re-record with better quality.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}