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?
asked Jun 21 '11 at 06:30 AM in Default
When you see the
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.
answered Jun 21 '11 at 06:42 AM
Kev Riley ♦♦
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.
answered Jun 21 '11 at 06:44 AM
Grant Fritchey ♦♦