#Sql server option recompile code#

Meaning the next time this script ran it used the bad query plan with different dates and puked. It was NOT always small after the query was executed again with a different set of parameters. The query plan for the amount of data within those parameter ranges was cached as if the amount of data in those plans was always small. This caused the performance go haywire if the query was run once on a set of dates that returned few records, then immediately after running on a different set of parameters that returned hundreds of thousands of records. The solution - Adding option recompile to the stored procedure of course! Parameter sniffing was the issue because the stored procedure was executing dynamic SQL and embedding parameter date values into the executed statement. I had stumbled across what is known as ' parameter sniffing'. The generated query plan differed greatly from what I saw previously in the quick runs. I accidentally ran the procedure once with incorrect parameters and then a second time after putting in the correct parameters was able to duplicate the issue. I had a feeling it had to do with a bad query plan but couldn't figure out why until I was able to luckily reproduce the issue in staging. All of these problems were ruled out as we made sure statistics were up to date, the SQL server load was low, and no maintenance scripts were running yet the problem still occurred. And it was not possible to reproduce in staging with the same data.Įxperimentation - Normally in this situation when the problem could not be reproduced on prod data I would think it's most likely load related, outdated statistics, or even a maintenance script running at the wrong time. In this case the query was only slow sometimes in production. Usually a slow running query could be easily reproduced in testing, which would lead to an eventual fix. The problem - We had a scheduled stored procedure taking a really long time in production. By adding the option recompile statement at the end of a stored procedure you are telling SQL to always recompile the query plan instead of fetching it from the cache.

But, this could be terrible for performance if the database objects changed drastically from the previous run.īy default the query plan is fetched from the cache and is only recompiled when the underlying tables change or SQL server is restarted. The efficient query plan can easily be fetched from the cache and won't have to be generated again which is generally good for performance. So, capturing the recompiles and getting grotty on the details means capturing statements.

This is really good if the relative sizes of the objects used in the stored procedure don't change very much from the last time the query plan was generated. Seriously though, we know that, since SQL Server 2005, all recompiles are at the statement level (unless you call for a recompile of a procedure or create that proc with a RECOMPILE hint). They're definitely worth it.When a stored procedure is compiled the generated query plan is optimized for the current state of the database. I converted it to an ITVF using cascading CTEs and achieved a 93.8% reduction in run time over a 1M row test table. There was a loop over the characters and several checks involved. In a recent redesign of an ETL system, the previous author had an MTVF to calculate a custom checksum for a given string of numbers. Piling on, ITVFs are definitely the way to go.

It's used differently that a Scalar Function but it's worth it for reduced CPU and increased performance. This issue can occur when multiple connections concurrently run this query by using different parameters. write it as an 'iSF' (Inline Scalar Function). Fixes a rare problem in which incorrect results are returned when you run a query that uses Option(Recompile) in SQL Server 2014 or SQL Server 2012. What do you do if you need a Scalar Function? Simple.

#Sql server option recompile code#

The iTVF works more like a parameterized view and (depending on how the code is written, of course) is as fast as if the code were written 'inline'. The first two are terrible for both performance and resource usage. Scalar Function, Multi-statement Table Valued Function (mTVF), and Inline Table Valued Functions (iTVFs). If you look in 'Books Online', you'll find that there are 3 types of functions.