SQL Server Engine Tips

Guidelines, Best Practices, TSQL and SQL Programming Tips & Tricks.

This Blog

About
Email

Syndication

Search

News

These postings are provided
"AS IS" with no warranties,
and confers no rights.
Use of included script samples
are subject to the terms
specified at http://www.microsoft.com/ info/cpyright.htm.

SQL Server 2005

SQL Server Document Links

Use of SCHEMABINDING option for TSQL UDFs can improve performance in SQL Server 2005...

SQL Server 2005 has new optimization logic to use the SCHEMABINDING option to derive certain properties about the TSQL UDF. This can greatly improve performance of queries that use scalar UDFs in a SELECT statement for example. You can read all about it at the blog entry below from one of our development engineers.

http://blogs.msdn.com/sqlprogrammability/archive/2006/05/12/596424.aspx

Published Monday, June 12, 2006 3:04 PM by SQL Server Engine Team

Filed under: Best Practices, Performance - SQL/TSQL

Comments

# re: Use of SCHEMABINDING option for TSQL UDFs can improve performance in SQL Server 2005... @ Tuesday, June 20, 2006 9:57 PM

The use of Schemabinding improves performance with SQL 2005 UDF's...

I think there has been additional improvements in relation to the way in which UDF's are processed in SQL2005.

We have been testing an application that makes heavy use of transact SQL functions
and have noticed vast improvements. (14 hours SQL Server 2000 SP4) to (40 minutes SQL Server 2005 SP1).

All testing was done on exactly the same hardware etc etc...

The UDF's are performing string comparisons and manipulations against tables that have millions of rows.

Why has performance improved so much???

One thing we found when examining a profile trace of the UDF call is that in SQL 2000 the "SP:ExeContextHit" event
gets fired every time the function is called.

However in SQL 2005 the "SP:CacheHit" event only gets fired once.....

so....

select myfunction(someColumn)
from sometable --------------- ( assume sometable has 500 rows)

will generate 500 "SP:ExeContextHit" events in SQL2000 but only 1 "SP:CacheHit" event in SQL2005

Regards,

Andrew

Andrew

# re: Use of SCHEMABINDING option for TSQL UDFs can improve performance in SQL Server 2005... @ Thursday, July 06, 2006 5:11 PM

Well, that would explain why my IsNull almost-dynamic WHERE clauses would suddenly start working so much faster than they did in SQL Server 2000!

Thanks!

Anonymous comments are disabled