title: Why a scalar function call for string splitting can lead to session block?
body: <p>Recently, we monitored a very strange issue about SQL Server session block, there was a scalar function for string splitting process which used in a loop code block within the stored procedure, we've checked the code, there was no any operations on database tables, why other sessions were blocked by the session that invoking the function?</p>
  
  <p>here is the function definitions:</p>
  
  <pre><code>CREATE function [dbo].[splits](@SourceSql varchar(max), @StrSeprate varchar(10), @y int)
   returns varchar(max) as
  begin
      declare @i int
      declare @idx int
      declare @s varchar(max)
      if(right(@SourceSql,1)!=@StrSeprate)begin
          set @SourceSql=@SourceSql+@StrSeprate
      end
      set @idx=0
      set @i=charindex(@StrSeprate,@SourceSql)
      while @i&gt;=1
      begin
          set @s=left(@SourceSql,@i-1)
          set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
          set @i=charindex(@StrSeprate,@SourceSql)
          set @idx=@idx+1
          if (@idx=@y) begin
              break
          end
          set @s=null
      end
      return @s
  end
  </code></pre>