Let me start by saying that this isn’t your typical post about the Halloween problem. This is intended to describe a couple different performance problems you should be keenly aware of.

If you’ve never heard of the Halloween problem then I suggest you read about it from Paul White’s blog or here: http://www.sqlservercentral.com/blogs/sqlstudies/2016/10/31/the-halloween-problem/

Halloween protection & user defined functions

So today is Halloween and there’s this really scary sounding problem in databases called “The Halloween problem.” If you didn’t read the above links you should but no need to do so right this moment. For now, let’s take a look at two issues you may not know have been affecting you.

Examine the following:

Above you can see three user defined functions (UDFs). These functions are used in an IN. Would you expect the query plan between the two statements above to be the same? I’m guessing you just thought “No” because I’m blogging about them. LOL.

Results:

Notice the “Filter” operator? Note that this has occurred because the function is not “schema bound” and the engine doesn’t know what tables are accessed so the Filter is put in place.

Now let’s look at an update:

Note that this update uses the same function that lacks the “With schema_binding” text and you should expect something ugly on the other end.

Let’s see the difference:

Note the “Table Spool” operator caused by the function?  Again, this is due to the lack of schema binding. Simply adding “WITH SCHEMA_BINDING” will eliminate these extra operators and dramatically improve the performance. “Halloween protection” caused them both; so, I’d recommend reading about the “Halloween problem” now if you’ve still not read about it.

If you’d like to read more of my posts, you can find them here:

http://www.sqlservercentral.com/blogs/confessions-of-a-microsoft-addict/

and here: http://www.sqltechblog.com

Also be sure to follow me on LinkedIn and Twitter!