While checking for long running queries I noticed a lot of entries where the query was "CREATE FUNCTION xyz ....".
After some investigation it appears that we have a function in databaseA that is taking a contactID and looking it up to get a staffNo in a table in a table in a different database. like this:
Is there something in the background that actually creates the function in DatabaseB for the query to run?
I'm going to work on getting the function(s) (i expect there isnt just one case of this!) moved to the databases where the data they work on is stored in order to speed things up a bit but am interested in what is actually going on.
[EDIT:] The script I'm using that shows this up is from here: http://scarydba.wordpress.com/2009/02/18/refining-the-missing-index-data/
You're calling the DMV's. Within them you can see individual statement calls. When you see them, it's usually based on CREATE PROCEDURE or CREATE FUNCTION. Confusing at first, but you'll get used to it. It's because the stuff in cache doesn't store the call itself, but stores the object called.
answered Feb 01, 2010 at 10:08 PM
Grant Fritchey ♦♦
There is no function created in DataBase B , nor do i see how creating the functions within database B will improve performance. Scalar UDF's are notorious in their poor performance, as a matter of best practice you should remove all Scalar UDF's and replace with inline UDF's or Views. There is quite a large overhead in simply calling the UDF plus you have also stopped the optimizer providing a best query plan for the statement as a while.
answered Feb 01, 2010 at 08:54 AM