Sql-server – Self blocking session

sql serversql-server-2008-r2

we are experiencing unresponsive Sql Server 2008 R2 Web Edition. The only way how to connect to the server is via DAC.

The dm_os_tasks return 539 tasks almost all SUSPENDED or PENDING. There is one task with blocking_session_id same as session_id. It's waiting for LCK_M_SCH_M, the others tasks are waiting for TEMPOBJ.

Both LCK_M_SCH_M and TEMPOBJ are weird. There is no scripts for changing/creating/droping schema. We are not using any temporary tables, only table variables and table-UDT as input parameters for several functions and stored procedures.

The task with LCK_M_SCH_M is inline-table-function with table-UDT (TblGuid).
The UDT TblGuid is table with single column:

CREATE TYPE [dbo].[TblGuid] AS TABLE(
    [Guid] [uniqueidentifier] NULL
)

The table-valued-function is unchanged for months and is called a lot. This behaviour became "out of nowhere".

If we restart the sql server service, all became ok. This state occures +- 10x in day.

I suspect the same session_id and blocking_session_id is invalid state and could not appears. Am I right?

EDIT:
The problematic function is:

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel] 
(   
    @IDsBattleTargets [dbo].[TblGuid] READONLY,
    @IDCurrentPlayer uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        DISTINCT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM @IDsBattleTargets AS IDS
    INNER JOIN [dbo].[tblBattleTarget] AS BT
        ON BT.BattleTargetID = IDS.Guid
    INNER JOIN [dbo].[tblUnit] AS U
        ON U.IDBattleTarget = IDS.Guid
    LEFT JOIN [cache].[tblHeroSharingWithPlayerClan] AS HSWPC
        ON HSWPC.IDHero = IDS.Guid
            AND HSWPC.IDPlayer = @IDCurrentPlayer
    LEFT JOIN [cache].[tblHeroSharingWithAlliance] AS HSWPA
        ON HSWPA.IDHero = IDS.Guid
            AND HSWPA.IDPlayer = @IDCurrentPlayer
    WHERE U.Amount > 0
        AND (BT.IDBattleTargetOwner = @IDCurrentPlayer
            OR HSWPA.IDPlayer = @IDCurrentPlayer
            OR HSWPC.IDPlayer = @IDCurrentPlayer)
)

The function is called from another function or from C# (.NET 4.0) via SqlConnection,SqlCommand and SqlParameter.

SOLUTION:
After advices I decided for change TVP to something another. I choosed the varbinary(max) where one Guid/uniqueidentifier value has 16 bytes. I created sql-clr function with varbinary(max) input and table output Guid uniqueidentifier. I won't use spliting text because it's not much efficient for my purpose (this function is called very often). After the 30 hours in production (the problem occured only on one server in production, and we was not able reproduce them in testing environment) there was no problem. Usualy the problem occures several times a day. This will be propably solution.

In the final the function looks:

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel] 
(   
    @IDsBattleTargets varbinay(max),
    @IDCurrentPlayer uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        DISTINCT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM [dbo].[GetGuidsFromBinary](@IDsBattleTargets) AS IDS
    INNER JOIN [dbo].[tblBattleTarget] AS BT
        ON BT.BattleTargetID = IDS.Guid
    INNER JOIN [dbo].[tblUnit] AS U
        ON U.IDBattleTarget = IDS.Guid
    LEFT JOIN [cache].[tblHeroSharingWithPlayerClan] AS HSWPC
        ON HSWPC.IDHero = IDS.Guid
            AND HSWPC.IDPlayer = @IDCurrentPlayer
    LEFT JOIN [cache].[tblHeroSharingWithAlliance] AS HSWPA
        ON HSWPA.IDHero = IDS.Guid
            AND HSWPA.IDPlayer = @IDCurrentPlayer
    WHERE U.Amount > 0
        AND (BT.IDBattleTargetOwner = @IDCurrentPlayer
            OR HSWPA.IDPlayer = @IDCurrentPlayer
            OR HSWPC.IDPlayer = @IDCurrentPlayer)
)

If someone will have similar issue, please contact me. I would like to more investigate this behaviour.

Best Answer

It's not blocking itself, this is just an odd artifact of how the waits are being reported. (And no, this isn't an invalid state, it happens all the time and is not indicative of any bug or deeper problem.) This has been reported a few times with table variables, and I think TVPs is a relatively newer one where there might still be some unresolved issues.

Some minor thoughts on optimization. First of all, why is the Guid column in the table type nullable? Why isn't it a primary key? The optimizer will be able to do a lot more with this if it understands the cardinality better, and your C# code is certainly capable of ensuring that it doesn't populate the parameter with duplicates or null values.

CREATE TYPE [dbo].[TblGuid] AS TABLE(
    [Guid] [uniqueidentifier] PRIMARY KEY
);

Now, the function has a lot of joins that I think are unnecessary, and if you can perform the grouping outside the function that may be better. I don't have sample data to verify that this returns the same results as your existing function, but if it's off it shouldn't be off by much. When you get it returning the same results, please do compare the actual execution plans and I am confident you will find this version is better.

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel] 
(   
    @IDsBattleTargets [dbo].[TblGuid] READONLY,
    @IDCurrentPlayer  UNIQUEIDENTIFIER
)
RETURNS TABLE
WITH SCHEMABINDING -- added this 
AS
RETURN 
(
    SELECT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM dbo.tblUnit AS U
    WHERE U.Amount > 0 
    AND EXISTS 
    (
      SELECT 1 FROM dbo.tblBattleTarget AS BT 
        WHERE BT.BattleTargetID IN (SELECT Guid FROM @IDsBattleTargets
          WHERE Guid = U.IDBattleTarget))
    )
    AND EXISTS 
    ( 
      SELECT 1 FROM cache.[tblHeroSharingWithPlayerClan]
        WHERE IDHero IN (SELECT Guid FROM @IDsBattleTargets)
        AND IDPlayer = @IDCurrentPlayer
      UNION ALL 
      SELECT 1 FROM cache.[tblHeroSharingWithAlliance]
        WHERE IDHero IN (SELECT Guid FROM @IDsBattleTargets)
        AND IDPlayer = @IDCurrentPlayer
    )
    -- only if necessary:
    -- GROUP BY U.IDUnitCass, U.IDBattleTarget, U.Amount
);
Related Question