Why Halloween slowed your queries

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:

3 q

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:

3 r

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:

4 q

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:

4 r

Note the “Table Spool” operator caused by the function?  Again, this is due to the lack of schema binding. Simply adding “WITH SCHEMABINDING” 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!

 

 

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