question

Tatyana avatar image
Tatyana 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?
lockingfunctioncreate
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I type too slow, and my wife is having a crisis on her computer (Libraries aren't displaying properly, end of the world).
0 Likes 0 ·
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!
0 Likes 0 ·
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.
0 Likes 0 ·
I just bought this book! Maybe it's time to read it now :) Thank you!
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.