Speed up compilation time by using OPTION (RECOMPILE)

· Uncategorized

Sounds like a contradiction but it works.

A client of mine uses lots of views that each hosts a terrible query. Plan cache used up to 2GB (many plans were around 50MB), which seems rather a lot to me (in total 14GB was reserved for buffer pool). More, plans were reused rarely. When running a simple select statement to the view (SELECT * FROM <viewname>) and activating SET STATISTICS TIME ON, huge values for compilationtime was generated. This sounded like not the recompile took that much time, but rather the interaction with the plan cache did. By using OPTION (RECOMPILE) these actions were skipped and compile time went down.

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

%d bloggers like this: