asked
CREATE FUNCTION executes during the UPDATE statement?
I was executing a statement: UPDATE [TableName] SET Col_1 = dbo.FunctionName(Col_2, Col_3) It was taking long time to execute, so I ran a query to see the activity on the server: SELECT d1.session_id, d3.[text] FROM sys.dm_exec_sessions d1 JOIN sys.dm_exec_requests d2 ON d1.session_id=d2.session_id CROSS APPLY sys.dm_exec_sql_text(d2.sql_handle) d3 , and saw there CREATE FUNCTION dbo.FinctionName statement, and none other activity. And when I opened the Activity Monitor it was showing a zillion of "U" page locks on the [TableName]. There are several indexes on that table with ALLOW_PAGE_LOCKS = ON. Were they causing the problem? And why the CREATE FUNCTION statement was executing (and never able to finish) during the simple update statement?
When you see the `CREATE FUNCTION` in the activity, this is just showing you the definition of the function that is running, it doesn't mean it is creating the function (that's a one-time thing, that you've already done). However by having this as a function on your update, you are essentially calling the function as many number of times as there are rows in the table. This is not the best way to do this, and is known as row-by-row processing, rather than set-based. Try and take the processing that the function is doing and apply it to the table in one go rather than this one-at-a-time approach.
I type too slow, and my wife is having a crisis on her computer (Libraries aren't displaying properly, end of the world).
Thank you, Kev and Grant. I understand it about CREATE FUNCTION statement. And I rewrote the code to avoid updating a table using a function, and it did execute in a much more reasonable time. I wonder if the same rule would apply to calling a function in the WHERE clause. Maybe you could suggest a good resource to read more on this subject. Thank you!
Functions in WHERE clauses can cause issues... mostly preventing the use of an index. @Grant Fritchey covers it in his "SQL Server 2008 Query Performance Tuning Distilled" book. A good read and it should be on your bookshelf.
One of two things, check the code, someone might have accidently wrapped a CREATE statement inside some other code, usually be misplacing GO statements. Or, you're see the CREATE statement just as a marker for what is being executed. For example, if you run queries against the DMO objects and join to sys.dm_exec_sql_text and you don't use the offsets to identify the statement being executed, you'll see a CREATE statement for the procedure/view/function. It could just be that.
