Freigeben über

Improving query plans with the SCHEMABINDING option on T-SQL UDFs

  • Artikel

This blog describes how the SCHEMABINDING option specified during creation of T-SQL UDFs may affect query plans involving these UDFs in SQL Server 2005. Armed with this knowledge, you may find that you can dramatically improvement your query performance for free.

Before I jump into the details, let me briefly summarize the take-away: If you are using simple T-SQL UDFs that do not touch any tables (i.e. do not access data), make sure you specify the SCHEMABINDING option during creation of the UDFs. This will make the UDFs schema-bound and ensure that the query optimizer does not generate any unnecessary spool operators for query plans involving these UDFs.

The BizTalk folks ran into this (https://blogs.msdn.com/BizTalk_Core_Engine/) recently, so I thought I’d take a deeper look. Let’s dig into the details.

Take a look at the following example for creating a simple T-SQL UDF:

CREATE FUNCTION dbo.ComputeNum(@i int)

RETURNS int

BEGIN

  RETURN @i * 2 + 50

END

When creating the UDF, we have the option to bind the UDF to the schema of the underlying objects to which it refers. This is done using the SCHEMABINDING option. For UDFs that are schema-bound, any attempt to change the underlying objects’ schema will raise an error. Using this option ensures that the UDF will not inadvertently break due to changes of an underlying object’s schema.

In SQL Server 2005, there are a couple of derived properties associated with whether the UDF is schema-bound: SystemDataAccess indicates whether the function accesses system data (system catalogs or virtual system tables), and UserDataAccess indicates whether the function accesses user data.

These properties can be seen via:

SELECT OBJECTPROPERTYEX(OBJECT_id('<MyFunction>'), 'SYSTEMDATAACCESS')

And

SELECT OBJECTPROPERTYEX(OBJECT_id('<MyFunction>'), 'USERDATAACCESS')

In our example above, because we did not specify the SCHEMABINDING option, both of these properties get set to 1, even though the function itself does not do any data access. Why? Since the UDF is not schema-bound, there’s no way to ensure that the underlying schema (including the schema of any underlying UDFs or views that this UDF may call) did not change since its creation. This means that we would have to derive these properties at runtime during every execution of the UDF. To avoid this performance penalty, we simply mark the UDF as accessing data to be on the safe side and do not attempt to derive these properties at runtime.

So, why are these properties interesting? Because they affect query plans involving the UDF. In the above example, because these properties are set to 1, the query optimizer assumes that the function may access data and will add a spool operator in order to protect itself from any data changes (also known as “Halloween protection”). Consider following UPDATE statement that uses our UDF:

CREATE TABLE t(col int);

CREATE INDEX i_t ON t (col ASC);

UPDATE t SET col = col + 5 WHERE dbo.ComputeNum(col) > 50;

If you look at the query plan generated for the above UPDATE statement, you’ll see that we’re spooling the results, even though the UDF itself does not do any data access.

Making the UDF schema-bound, as in the following example, forces SQL Engine to analyze the body of the UDF and properly set these derived properties. You’ll avoid the unnecessary spooling and may see a very significant performance gain:

CREATE FUNCTION dbo.ComputeNum(@i int)

RETURNS int

WITH SCHEMABINDING

BEGIN

  RETURN @i * 2 + 50

END

In summary, specify the SCHEMABINDING option for T-SQL UDFs to ensure that plans using these UDFs do not do unnecessary spools.

Comments

  • Anonymous
    June 12, 2006
    SQL Server 2005 has new optimization logic to use the SCHEMABINDING option to derive certain properties...

  • Anonymous
    September 21, 2006
    Hi Tomerv
    Thanks for sharing tip.
    i've quick question ,
    you are right ,when i run above UPDATE stmt it does add spooler operator in query plan and just wondering does it add spooler operator "SELECT" stmts also ?
    bcoz i've function which does not do any data access and only some string manipulations and what i noticed is there is no spooler operator whether there is schemabinding or not !
    Thanks.

  • Anonymous
    August 22, 2007
    Table spool la apelul unui UDF

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2155852-using-functions-on-sql-server

  • Anonymous
    July 25, 2013
    I just happened upon this article and gave it a try on SQL Server 2012 (11.0.3128.0), and sure enough, this article is still relevant. Is this a big deal? YES!!! This amounts to a 22% performance improvement for the update in this article's example when adding the schemabinding(!).