SQL Server blocking on 'create function...', what does this imply?

Question

Hi folks,

I've used the sql from this article http://blogs.techrepublic.com.com/datacenter/?p=275 to try and track down the cause of a lot of blocking which has been going on recently within my sql server 2005 database. A number of times, all the processes returned are calling 'create function...', the functions vary but a number of them will be creating the same function. From the details included in the article, and from looking at what is in the tables the result set is built from it doesn't look like these create statements are usually being called. Does this imply recompilation of the functions in question? Or something else?

Thanks for any help,

Robin

Answer 1

You need to use the statement_start_offset and statement_end_offsets from sys.dm_exec_requests (don't use sysprocesses as shown in the article) to SUBSTRING out the offending section of code. See the example in the BOL topic for sys.dm_exec_sql_text().

EDIT:

Here is how to do what I said above:

SELECT 
    spid = r.session_id, 
    BlockingSPID = r.blocking_session_id, 
    DatabaseName = DB_NAME(r.database_id),
    s.program_name,
    s.login_name,
    ObjectName = OBJECT_NAME(st.objectid, st.dbid),
    Definition = SUBSTRING(st.text, (r.statement_start_offset/2)+1, 
        ((CASE r.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE r.statement_end_offset
         END - r.statement_start_offset)/2) + 1)
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text (sql_handle) st
WHERE r.session_id > 50

The output is going to be different because only currently executing requests are in the dm_exec_requests DMV, where sysprocesses shows every spid, executing or not. You can find your blocking statements with the above code though.