October 9, 2014 at 4:52 pm
Hello community.
I've read that adding SCHEMABINDING to a scaler UDF that does not reference any external schema (tables, etc) can have a performance improvement, possibly removing the need for Eager Spooling, etc. Sure enough, some quick tests show improved query plans.
I added SCHEMABINDING to several heavily used scaler UDFs that have no external schema references (ex: simple formatting or validation).
Later I dropped an unused table from the same database. This table had been flagged for deletion and was not used by any processes.
The drop took nearly a minute during which time scores of deadlock errors came streaming in from our application.
My question is:
When the schema is changed in the database (such as during a DROP TABLE) is a schema lock held on objects that have SCHEMABINDING even if the schema bound objects (such as my UDFs) have no external schema references?
Any input is welcome. Thank you.
October 10, 2014 at 11:29 am
I'm beginning to think this has nothing to do with SCHEMABINDING. Perhaps a schema lock on the PFS? Still, I've never had deadlocks like this show up from dropping an unused table.
October 10, 2014 at 12:21 pm
While not addressing your question directly I have a question. If you are concerned about improving performance for scalar functions, have you considered getting rid of them and converting your scalar functions to inline table valued functions? Scalar functions are notoriously bad for performance. Make sure that if you do switch to itvf that they are NOT multi statement table valued functions. Those are generally even worse than scalar functions. Your itvf needs to be a single statement.
There is an article on this site about converting scalar functions to inline table valued functions but for the life of me I can't find it right now.
You might also take a look at this article which explains squeezing some performance from scalar functions. http://www.sqlservercentral.com/articles/T-SQL/91724/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2014 at 2:09 am
Paul White did a very nice blog post about Halloween Protection and schemabinding on UDFs. That is a significant performance improvement.
I don't know the answer to your question about schema-type locking when dropping an object. It would be VERY easy to set up a test though to see what locks are taken by doing an explicit transaction and a WAITFOR DELAY after the drop then check locks taken.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply