SQL Server Blog

Good use for WITH RECOMPILE

Haven’t looked into all of the details underneath but at the surface probably a good reminder of where WITH RECOMPILE can be helpful…

Using SQL Server Reporting Services, a workaround was used (this is the part I want to look into more) to basically get a blank report showing when a user first links to a report until they change parameters and run the report themselves. So this first execution gives an false year/month to search back until (190001). The procedure behind this report looks for any “invalid” (per the business rules that means less than 195001) date and issues a “RETURN”, returning control to the color.

The problem was picked up during an execution after a restart. When the Reporting Services query ran asking for 200809 it ran until it timed out, no results coming back. When the query was executed for 200809 from SQL Server Management Studio, it came back in about 30 seconds.

Two issues at play here 1.) The sessions are getting different query plans (topic for another post.. various reasons this can happen even for the same proc) and 2.) The query plan used by SSRS is being compiled first with the first time an SSRS session ever executes the query (190001).

This can be verified by looking at the xml showplan for the execution and seeing what the parameter is compiled for.

In this case, adding WITH RECOMPILE to the stored procedure before the AS, tells SQL to recalculate the plan at each execution instead of cache and reuse a plan for every execution. This resulted in the reporting services query coming back in 35 seconds as opposed to timing out.

Obviously longer term solutions include figuring out why this was designed this way and looking at other possible options. Normally WITH RECOMPILE is best when working with queries that are called with dramatically different parameter values each time that cause differences in the way the plan is figured.

For another post, we’ll talk about how you can have the same procedure use different plans even for the same server and how to minimize/avoid that.

Mike Walsh
Article by Mike Walsh
Mike loves mentoring clients on the right Systems or High Availability architectures because he enjoys those lightbulb moments and loves watching the right design and setup come together for a client. He started Straight Path in 2010 when he decided that after over a decade working with SQL Server in various roles, it was time to try and take his experience, passion, and knowledge to help clients of all shapes and sizes. Mike is a husband, father to four great children, and a Christian. He’s a volunteer Firefighter and EMT in his small town in New Hampshire, and when he isn’t playing with his family, solving SQL Server issues, or talking shop, it seems like he has plenty to do with his family running a small farm in NH raising Beef Cattle, Chickens, Pigs, Sheep, Goats, Honeybees and who knows what other animals have been added!

Subscribe for Updates

Name

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This