SQL Tips by Namwar Rizvi

June 27, 2007

Statement Level Recompilation–Excellent new feature of SQL Server 2005

Recompilation of the Query plan sometimes harms your server performance more than you expect. Prior to SQL Server 2005, SQL Server had the algorithm which may decide to recompile the full stored procedure because of just one statement. This strategy of full recompilation makes DBA life difficult because he/she needs to investigate the full stored procedure and sometimes may break it into separate small stored procedures to reduce recompilations.

SQL Server 2005 has a great feature of statement level recompilation. This feature automatically detects the statements needs to be recompiled and only these statements will be recompiled resulting less CPU cycles and no need of breaking the stored procedure into sub-stored procedures.

An excellent article about recompilations is available at Microsoft here

Advertisements

1 Comment »

  1. You absolutely have a point there, I have never thought about it like it like that before.
    You make it sound so enthralling. I am going to have to investigate
    this more!

    Comment by Sourish Pau — February 17, 2013 @ 9:11 AM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: